Get exact search before LIKE?!

PHP
1

I’m trying to make a search function which first gives the exact search if exists and then all the ones like…

Her is what I have tried:

$search = "lenatk";

$sql = mysql_query("SELECT IF(username='$search') AS exact, username
FROM nf_users
WHERE username LIKE '%$search%'
ORDER BY exact DESC
LIMIT 7") or die(mysql_error());
while($row = mysql_fetch_assoc($sql)){
	
	echo $row['username'].'<br>';
	
}

This gives an error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘) AS exact, username FROM nf_users WHERE username LIKE ‘%lenatk%’ ORDER BY exa’ at line 1

Not sure where to go please help and thanks in advance…

2

The error is due to the syntax of if condition inside a query. The syntax for if in mysql query is:
IF(condition, true_value, false_value) AS somealias

But I am really not aware of the possibility of getting those records in that order i.e. exact rows first then likes. So instead of direct way in mysql query, there should be some tricky solution for this. But I would for the first hand do query exact records then other likes until I find another solution.

3

As Raju stated, your query is slightly off, but I can think of a possible two ways of accomplishing this.

Here is the way you used

$search = "lenatk";

$sql = mysql_query("SELECT IF(username='$search', 1, 0) AS exact, username
FROM nf_users
WHERE username LIKE '%$search%'
ORDER BY exact DESC, username
LIMIT 7") or die(mysql_error());
while($row = mysql_fetch_assoc($sql)){

    echo $row['username'].'<br>';

}

I also believe UNION could solve this too

$search = "lenatk";

$sql = mysql_query("SELECT *
FROM
(
  SELECT 1 AS exact, username
  FROM nf_users
  WHERE username = '$search'
    UNION
     SELECT 0 AS exact, username
     FROM nf_users
     WHERE username LIKE '%$search%'
)
ORDER BY exact DESC, username
LIMIT 7
") or die(mysql_error());
while($row = mysql_fetch_assoc($sql)){

    echo $row['username'].'<br>';

}

I’m not sure which will provide better performance, but I believe both should return the records in the same order.

4

or you could make your username field indexed as a FULLTEXT, and then MATCH username AGAINST your string to get relevancy results, and check if the first row = the string.

5

UNION not really required

(besides, cpradio, your UNION query returns exact matches twice, i.e. once from each SELECT)

SELECT CASE WHEN username = '$search'
            THEN 1 ELSE 0 END AS exact
     , username
  FROM nf_users
 WHERE username LIKE '%$search%'
ORDER
    BY exact DESC LIMIT 7
6

True, it was my “second option”, but I thought UNION returns non-duplicate results, but UNION ALL returns the duplicate results, am I mistaken on this? Or maybe it isn’t [url=http://www.mysqlperformanceblog.com/2007/10/05/union-vs-union-all-performance/]applicable in the same way to MySQL…

EDITED: AHH nevermind, I see why it would duplicate, due to the exact column. Duh!

7

I would do this with 2 different queries.

If exact match gets a result, halt, show result.

Else, do a like %% search (or as suggested maintain a FULLTEXT field).

I had success with systems similar to this which worked of search phrases.

That used the FULLTEXT search over several columns, and if less than, say 3 results were found - it broke the phrase into single words and did exact match searches for those words.

Having the GUI display these results clearly is probably the hardest thing to achieve though.

8

I ended up with this solution which works:

$sql = mysql_query("SELECT IF(username='$search' OR email='$search',1,0) AS exact, username, played, win, profileimg, id
FROM nf_users
WHERE username LIKE '%$search%' OR email LIKE '%$search%'
ORDER BY exact DESC, email ASC
LIMIT 1");

Thanks for all your replies :wink: