...

View Full Version : Find exact matches first



Arnaud
10-14-2011, 08:26 PM
Hi folks!

I am building an ajax like (jQuery + JSON) search function here and I have a few issues / questions.

I have a MySQL table with around 160'000 rows with a field named "CODE" with the following values for example:

- ACAO
- ACA
- AC
- AC
- ACAT
- BOOA
...

There is also a "NAME" field in which I want to perform the search, but I don't really expect people to input an exact match as the search string.

1. If my search string is AC, I want it to return all the "AC" rows first (exact match), then all others that contain AC.

2. I need to return the results in JSON format and I need to know on the return (on the javascript side) which results are "exact matches" to the search string.


I found 2 solutions:

Solution 1 works quite well but involves 2 queries to the db and returns the exact matches twice (once in each query...)



// Select exact matches first
$sql = "select * from nav where code='" . $search . "' or name='" . $search . "' order by code asc limit 0,300";
$req = mysql_query($sql) or die('Erreur SQL !<br>' . $sql . '<br>' . mysql_error());

while ($r = mysql_fetch_assoc($req)) {

// Add param to JSON output for exact match

$r['exact'] = "1";
$po[] = $r;
}

// Select like matches
$sql = "select * from nav where code like '%" . $search . "%' or name like '%" . $search . "%' order by code asc limit 0,300";
$req = mysql_query($sql) or die('Erreur SQL !<br>' . $sql . '<br>' . mysql_error());

while ($r = mysql_fetch_assoc($req)) {

$po[] = $r;
}

print json_encode($po);



Solution 2 achieves what I want with only one query but is painfully slow!



$sql = "SELECT * from nav WHERE code like '" . $search . "' or name like '" . $search . "' union all SELECT * from nav WHERE code LIKE '%" . $search . "%' or name LIKE '%" . $search . "%' and code not like '" . $search . "' and name not like '" . $search . "'";
$req = mysql_query($sql) or die('Erreur SQL !<br>' . $sql . '<br>' . mysql_error());

while ($r = mysql_fetch_assoc($req)) {

// If exact match, add param to JSON output for exact match

if ($r['code'] == strtoupper($search)) {

$r['exact'] = "1";
}

$po[] = $r;
}

print json_encode($po);


Hence my questions:

1) Why is number 2 so slow? (I mean it's reeeeally slow...)

2) Would you see any better way to modify the JSON return so that I know which results are "exact matches"?

3) Any other way to achieve what I want with less code and have something that works faster? It's an ajax search, you know...

Thanks in advance for any help on that!

A.

Old Pedant
10-14-2011, 08:51 PM
Number 2 is slow because you are using UNION ALL and because of the NOT LIKE.

How about this?


SELECT IF(name='$search' OR code='$search',1,0) AS exact, name, code
FROM nav
WHERE name LIKE '%$search%' OR code LIKE '%$search%'
ORDER BY exact DESC, code ASC
LIMIT 300

And now you already have the value of exact ready to use?

Arnaud
10-14-2011, 10:22 PM
How about this doesn't only look perfect but even works perfectly?

You answered my 3 questions in no time.
Working and fast code, information, what else could I ask for?

Thank you!

:thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum