WordPress autocomplete search: how to connect query file of custom table?

The aim is to retrieve data from custom table and display it in auto-complete form on a web-site, running WordPress. Here is the table.

firm_id       firm_name
   1        Helsinki genetics
   2           23andMe

I use jQuery-Autocomplete library and advises from this topic. This is how do I fill functions.php of my WordPress theme. I guess, somewhere here could be mistake..

Read More
// add jQuery-Autocomplete library

function my_library_method() {
// register your script location, dependencies and version
   wp_register_script('library_script', get_template_directory_uri() . '/js/jquery.autocomplete.js');
 // enqueue the script
   wp_enqueue_script('library_script');
  }
add_action('wp_enqueue_scripts', 'my_library_method');    

// add javascript file with autocomplete

function my_scripts_method() {
  wp_register_script('custom_script', get_template_directory_uri() . '/js/js_auto.js');
  wp_enqueue_script('custom_script');
  }
add_action('wp_enqueue_scripts', 'my_scripts_method');

This is a code for input box.

<input type="text" name="mybox" id="mybox" />

This is a js_auto.js file, which makes all auto-complete magic.

jQuery(document).ready(function($) {

  $('#mybox').autocomplete({

// add the way to the file with database query

       serviceUrl: 'tags.php',

// what happens when user chooses autocomlete suggestion

       onSelect: function (suggestion) {
        alert('You selected: ' + suggestion.value + ', ' + suggestion.data);
    }
  }); 
});

Finally this is tags.php file, where is the query for the database.

    $query = isset($_GET['query']) ? $_GET['query'] : FALSE;

    global $wpdb;

    // escape values passed to db to avoid sql-injection

    $depts = $wpdb->get_results( "SELECT * FROM wp_firms WHERE firm_name LIKE '".$query."%' ORDER BY firm_name ASC" );

    $suggestions = array();
    $data = array();
    foreach($depts as $row) {
        $suggestions[] = $row->firm_name;
        $data[] = $row->firm_id;
    }
    $response = array(
        'query' => $query,
        'value' => $suggestions,
        'data' => $data,
    );

   echo json_encode ($response);

I tested autocomplete with manually filled array in js_auto.jsfile without tags.php, and it worked perfectly. However I want to get data array from the database.

Any ideas how to connect this tags.php (database query) to js_auto.js (autocomplete script)?

Sorry for a bit long explanation but I tried to describe as detailed as possible. I’m trying to solve it already second weekend so any help would be very appreciated.

Related posts

1 comment

  1. It might be that you need to change the serviceUrl to the full path:

    serviceUrl: 'http://yourdomain.com/path/tags.php',
    

    Depending on where exactly you’re hosting that tags.php file. You could use

    var template_uri = <?php get_template_directory_uri(); ?>;
    serviceUrl: template_uri + '/tags.php',
    

    Try using developers tools in chrome for example, and it will most likely show you where there js error is.

    And Replace:

    foreach($depts as $row) {
        $suggestions[] = $row->firm_name;
        $data[] = $row->firm_id;
    }
    $response = array(
        'query' => $query,
        'value' => $suggestions,
        'data' => $data,
    );
    

    with:

    foreach($depts as $row) {
        $data['value'] = $row->firm_name;
        $data['data'] = $row->firm_id;
    }
    $response = array(
        'suggestions' => $data;
    );
    

Comments are closed.