LIKE on three columns. Show login, firstname ,and lastname on autocomplete list but only select login when user selects

I have this php :

$sql = ("SELECT login, firstname, lastname FROM $temp WHERE login LIKE '%$q%'");
$rsd = mysql_query($sql);
while($rs = mysql_fetch_array($rsd)) {
    $login = $rs['login'];
    $fname = $rs['firstname'];
    $lname = $rs['lastname'];
        echo "$login ";
        echo "$fname ";
        echo "$lname \n";
}

I have already escaped my variables, etc. I was wondering how I could use LIKE on three columns, how the user would se开发者_开发百科e all three values on the autocomplete list, and then on selecting, would only see the login in the text area. Im using a jquery autocomplete plugin btw:

<script type="text/javascript">
$().ready(function() {
    $("textarea#user").autocomplete("autocomplete.php", {
        width: 260,
        matchContains: true,
        selectFirst: false
    });
});

It is not possible to use LIKE in your query for auto complete.

You would need to do something like this

$().ready(function() {
    $("textarea#field1").autocomplete("autocompleteField1.php", {
        width: 260,
        matchContains: true,
        selectFirst: false
    });
});

$().ready(function() {
    $("textarea#field2").autocomplete("autocompleteField2.php", {
        width: 260,
        matchContains: true,
        selectFirst: false
    });
});

In your php files you do

$sql = ("SELECT DISTINCT firstname FROM $temp WHERE login LIKE '%$q%' LIMIT 0,10");//You don't want to have 20 autocomplete fields
$rsd = mysql_query($sql);
while($rs = mysql_fetch_array($rsd, MYSQL_ASSOC)) {
        echo "$rs['firstname']";

}

The plugin you are using is deprecated. If you haven't worked on it much I would recommend using another plugin which has better support and works pretty well.

This plugin is a good one.

For other jQuery Plugins.

In your SQL you probably want something like:

... where `login` like '...' or `firstname` like '...' or `lastname` like '...'

So that any of the three fields will match.

There are a few variations of the jquery.autocomplete plugin. I used one of them and something like the code below works for me well.

$("textarea#user").autocomplete("autocomplete.php", {
    width: 260,
    matchContains: true,
    selectFirst: false,
    formatItem: function(row, pos, num, query) {
        // alert(row.join(',')); // See this alert, I don't remember in which order substrings are packed into "row"

        // Return html we want to be displayed in an item within the dropdown list.

        return row[0] + '<br /><em>' + row[1] + '</em> ' + row[2];
    }
}).result(function(event, data, formatted) {
    // alert(data.join(',')) // See what's in the "data", there should be your "login", "firstname", "lastname" of the item selected by user

    // Do what you want here with the event.

    $("textarea#user").val(data[0]);
});

I haven't tested the code though.

Also the .result(...) section seems to be unnecessary for your case, autocomplete will insert the first substring of a row into the input, I'm just showing that you can do custom handling of the "item selected" event.

Cheers.

EDIT:

Oh and I forgot you might need two more options for autocomplete: matchContains: 'word', matchSubset: false,