使用PHP在MySQL表中搜索自动完成框中的多个字段

Question

Im trying to search two of the fields in my table. (Database table contains cocktail names, with their name, ingredients and description) When a user types their desired term into the search box, auto complete results are given using Ajax/jquery.

I've got it working searching one field (Name) but can't seem to get it to do more than the one(Name & Ingredients). Another problem is when the results are given (they are given as links) When you select them it puts the data in the entry box instead of taking you to the next page.

index.php:

<script>
$(document).ready(function(){
 $("#tag").autocomplete("autocomplete.php", {
        selectFirst: true
    });
});
</script>

    <label>Tag:</label>
    <input name="tag" type="text" id="tag" size="40"/>

autocomplete.php:

<?php
    $q=$_GET['q'];
    $my_data=mysql_real_escape_string($q);
    $mysqli=mysqli_connect('localhost','ignitet1','password','ignitet1_WhatCocktail') or die("Database Error");
    $sql="SELECT Name FROM tblCocktail WHERE Name LIKE '%$my_data%' ORDER BY Name";
    $result = mysqli_query($mysqli,$sql) or die(mysqli_error());

    if($result)
    {
        while($row=mysqli_fetch_array($result))
        {
            echo "<a href=\"details.php\">";
            echo $row['Name']."," , "<br />";
            echo "</a>";
        }
    }
?>

Any help would be appreciated.

Answer 1

I'm not sure about forcing a link to that page, but for a quick and dirty fix on the search I would add: OR Ingredients LIKE '%$my_dat%' before the ORDER clause.

Update:

user710502 is correct about the fields to be selected in the query. As for the autocomplete, I looked at the documentation and it looks like you will have to create a custom event handler for the autocompleteselect event. This event fires when the user chooses an option from the dropdown. The default behavior (as you know) is to fill the field with the 'value'. Your added code will look something like this:

$("#selector-for-field").bind('autocompleteselect',function(event,ui){
    event.preventDefault(); // this will stop the field from filling
    // maybe make the value sent from the serverside a hyperlink,
    // eg. http://www.wherever.com/you/want/the/item/to/link-to
    // then in here do something like:
    window.location(ui.item.value);
});

You'll have to double check that ui.item.value works because I didn't check it.