Searching multiple columns in MySQL for automplete results

Question

I'm using the following code along with Jquery autocomplete to populate the search suggestions on my site..

$results = mysql_query('SELECT DISTINCT artist FROM mm_albums WHERE artist LIKE "'. mysql_real_escape_string($_REQUEST['term']) .'%" ORDER BY artist ASC LIMIT 0,10', $dbcon);

$data = array();
if ( $results && mysql_num_rows($results) )
{
while( $row = mysql_fetch_array($results, MYSQL_ASSOC) )
{
    $data[] = array(
        'label' => $row['artist'],
        'value' => $row['artist']
    );
}
}

echo json_encode($data);
flush();

This is working fine but I want to be able to search across multiple columns for various results e.g. -

an artist name from the artist column
a track name from the name column
a record label form the label column

I've just spent 2 hours reading tons of different ways to do it but none of them are quite right for what i'm trying to do and I'm having trouble formatting the query correctly.

Has anybody got any suggestions?

--EDITED BELOW TO ADD THE SOLUTION--

This is how I managed to get it working thanks to Ed's suggestions in case anybody else is trying to do this.

This will search 4 different columns from 2 different tables and match the search term typed by the user with anything it finds in any of them while stripping out duplicate results. This works flawlessly with JQuery Autocomplete and here's how to set it up.

1) Create the following php file and name it search.php

<?php
if ( !isset($_REQUEST['term']) )
exit;

$connect = mysql_connect('dbhost', 'dbusername', 'dbpassword') or die( mysql_error() );
mysql_select_db('dbname');

$results = mysql_query('SELECT artist AS shout FROM albums WHERE artist LIKE "'. mysql_real_escape_string($_REQUEST['term']) .'%" UNION SELECT name FROM albums WHERE name LIKE "'. mysql_real_escape_string($_REQUEST['term']) .'%" UNION SELECT label FROM albums WHERE label LIKE "'. mysql_real_escape_string($_REQUEST['term']) .'%" UNION SELECT track_name FROM tracks WHERE track_name LIKE "'. mysql_real_escape_string($_REQUEST['term']) .'%" ORDER BY 1 LIMIT 10', $connect);

$data = array();
if ( $results && mysql_num_rows($results) )
{
while( $row = mysql_fetch_array($results, MYSQL_ASSOC) )
{
    $data[] = array(
        'label' => $row['shout'],
        'value' => $row['shout']
    );
}
}

echo json_encode($data);
flush();
?>

2) In your new script replace dbhost, dbusername, dbpassword & dbname with the actual details that you use to connect to your database. Although I would suggest that you didn't have this info stored in this file. Much better to store your connection info in a separate file, locate it outside of the webroot and `include' it here instead

3) Replace the queries with your own. This is how mine are set up so you can see what you need to replace..

First it's renaming the search string to shout (just for clarity) then it's searching the column artist in the table albums and then matching artist to the search term generated by Autocomplete.

This format repeats searching the following...

name from albums label from albums track_name from tracks

Just replace this info with the columns & tables that you wish to search, save the file and upload it.

4) Add the following search form to your page where pathtoyoursearchenginescript.php points to your search engine.

<form action="pathtoyoursearchenginescript.php" method="post">
Search: <input type="text" id="suggest" /> <input type="submit" value="Search" />
</form>

5) Assuming that you already have the required .js libraries loaded on your page you can just fling this chunk of code somewhere and you're done!

<script type="text/javascript">
jQuery(document).ready(function($){
$('#suggest').autocomplete({source:'search.php', minLength:2});
});</script>

Note: The code above assumes that search.php is in your webroot, if it isn't make sure that you update this with the correct location of your file.

Hope this is useful to somebody ;)

Please don't use `mysql_*` functions in new code. There's good reasons why you should look into using mysqli or PDO instead. See more details here: http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — jcsanyi, Jun 12 '13 at 03:20
Yes I know, I have a PDO version of this script as well but the old way is much easier to read and understand. I don't know why they've depreciated the old code when the PDO way of writing this exact same function is almost twice as long! — Grant, Jun 12 '13 at 03:27
The `mysqli_` functions can be used in almost the exact same way you're using the `mysql_` functions. It just doesn't provide the same level of protection that you can get with bound parameters when you use it that way - but at least it's not an unsupported library. — jcsanyi, Jun 12 '13 at 03:31
Yes very true. But I have an entire ecommerce site built the old way which I don't fancy re-scripting just yet ;) — Grant, Jun 12 '13 at 03:38

score 1 · Accepted Answer · answered Jun 12 '13 at 03:11

1

I'd use a UNION for this. A UNION will remove duplicate values so you don't need the DISTINCT keyword:

SELECT artist FROM mm_albums WHERE artist LIKE <value>
UNION SELECT name FROM mm_albums WHERE name LIKE <value>
UNION SELECT label FROM mm_albums WHERE label LIKE <value>
ORDER BY 1 LIMIT 10

This will pull keywords from the artist, name and label columns.

answered Jun 12 '13 at 03:11

Ed Gibbs

25,924
4
46
69

Gonna try it this way but how would I need to modify my array output? – Grant Jun 12 '13 at 03:16
You shouldn't have to. The column will still be named `artist` because that's the name in the first `SELECT` statement. I know that's misleading now because the column may have an artist or a name or a label, so if you want you can assign a more appropriate name in the first `SELECT`, for example: `SELECT artist AS searchvalue FROM ...` and continue with the rest of the query. If you do that then you'll want to address the array as `$row['searchvalue']`. Your call though, pick what you're most comfortable with. – Ed Gibbs Jun 12 '13 at 03:22
Fantastic, that worked perfectly! Thanks so much for your help and for explaining it in a nice easy way to understand. Cheers! – Grant Jun 12 '13 at 03:34

score 0 · Answer 2 · answered Jun 12 '13 at 03:08

$results = mysql_query('SELECT DISTINCT artist FROM mm_albums WHERE artist LIKE "'. mysql_real_escape_string($_REQUEST['term']) .'%" or name LIKE "'. mysql_real_escape_string($_REQUEST['term']) .'%" or label LIKE "'. mysql_real_escape_string($_REQUEST['term']) .'%" ORDER BY artist ASC LIMIT 0,10', $dbcon);

score 0 · Answer 3 · answered Jun 12 '13 at 03:11

0

Something like this will do what you need:

$results = mysql_query('SELECT DISTINCT artist FROM mm_albums WHERE artist LIKE "%'.
  mysql_real_escape_string($_REQUEST['artist']) .'%" OR name LIKE "%'.
  mysql_real_escape_string($_REQUEST['track']) .'%" OR label LIKE "%'.
  mysql_real_escape_string($_REQUEST['label']) .'%" ORDER BY artist ASC LIMIT 0,10', $dbcon);

you will also need to pass more parameters back in your REQUEST - three in this case, one for each value you want to query.

answered Jun 12 '13 at 03:11

Duncan Lock

12,351
5
40
47

Trying this way to.. Couple of questions.. 1) The request has to be 'term' every time (and not artist, track, label) as that is what autocomplete is sending. Is this ok? 2) What would I put in the $row array? At the moment is just using the artist result but I obviously need it to scan all three. – Grant Jun 12 '13 at 03:22
Can you edit your questions and add your client side JS that's making the request? - that would be helpful. – Duncan Lock Jun 12 '13 at 04:19
You could just use `term` instead of `artist`, `track` and `label` if you want to search for the same string in each of the three fields. – Duncan Lock Jun 12 '13 at 04:20
It's ok Duncan I managed to do it using Ed's method above and it works perfectly. Thanks for your help though. – Grant Jun 12 '13 at 12:29

Searching multiple columns in MySQL for automplete results

3 Answers3