Autocomplete form populated by a database?_问答_开发者

Autocomplete form populated by a database?

开发者 https://www.devze.com 2023-03-07 00:08 出处：网络

I am currently working on a project in which I need to have an autocomplete form call its information from a db file. I have seen many tutorials on jquery autocomplete forms, but I do not know how to

I am currently working on a project in which I need to have an autocomplete form call its information from a db file. I have seen many tutorials on jquery autocomplete forms, but I do not know how to call a db file t开发者_开发知识库o populate the list.

I am working in PHP. Currently the code represents a simple drop down box that is calling on the db file for population.

    <?php
    global $wpdb;
    $depts = $wpdb->get_results( "SELECT * FROM departments ORDER BY department_name ASC" );
    echo '<select>';

    foreach($depts as $row) {
        echo '<option name="select_dept" value="'.$row->department_id.'">'.$row->department_name.'</option>';
    }
    echo '</select>';
?>

Any help would be awesome!

I recently have used this library for autocompletion - http://www.devbridge.com/projects/autocomplete/jquery/ So here is brief script based on yours:

<?php

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

if ($query) {
    global $wpdb;
    // escape values passed to db to avoid sql-injection
    $depts = $wpdb->get_results( "SELECT * FROM departments WHERE department_name LIKE '".$query."%' ORDER BY department_name ASC" );

    $suggestions = array();
    $data = array();
    foreach($depts as $row) {
        $suggestions[] = $row->department_name;
        $data[] = $row->department_id;
    }
    $response = array(
        'query' => $query,
        'suggestions' => $suggestions,
        'data' => $data,
    );
    echo json_encode($response);
} else {
?>
<html>
<body>
<input type="text" name="" id="box" />

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.1/jquery.min.js"></script>
<script src="http://www.devbridge.com/projects/autocomplete/jquery/local/scripts/jquery.autocomplete.js"></script>

<script type="text/javascript">

$(document).ready(function(){
    $('#box').autocomplete({ 
        serviceUrl:'/',
        // callback function:
        onSelect: function(value, data){ alert('You selected: ' + value + ', ' + data); },
    }); 
});
</script>
</body>
<html>
<?}?>

Please follow this very well written tutorial

http://www.nodstrum.com/2007/09/19/autocompleter/

JQuery UI includes an autocomplete, although you still need to write a PHP script to return the information to be added to the control as its done via AJAX. If you know in PHP how to connect to a database, query it, and return a list of the results - then you will have no problems with this. JQuery makes AJAX extremely simple.

Depending on how complicated your field/data set is - and assuming its not millions upon millions of unindexed records, I would be content to autocomplete from a:

SELECT thing WHERE thing LIKE '".$query."%'

So if you were searching for, say, food... the query "CA" would pull out CArrot and CAbbage and CAuliflower. If you added a % to the beginning of the LIKE, you could get results that contained your query, as opposed to just beginning with it.

The page your user hits would contain the JQuery part which both sends the request and creates the autocomplete effect from the results, and a very simple, separate PHP script which the AJAX request hits would return the potential 'matches'.

Take a look at the Autocomplete demos on JQuery UI