MySQL - ORDER BY CASE WHEN doesn't work

Question

I have a table 'users' with two columns (id,full_name) i created a search box using PHP after that i searched how to make it order by relevance i found the solution but it's not working correctly so if there is three lines containing John Smith in full name then it gives me only one result here is the table

 id  |   full_name
 1   |   John Smith
 2   |   John Smith
 3   |   John Smith

And Here is my code

$find = $_GET['q'];
$values=explode(" ", $find);
$sql="SELECT * FROM users WHERE";
$i=0;
foreach($values as $v)
{
$v=trim($v);
if($i==0)
{
$sql.=" full_name LIKE '%$v%'";
}
else
{
$sql.=" OR full_name LIKE '%$v%'";
}

$i++;
}
$sql.="GROUP BY full_name ORDER BY CASE WHEN full_name like '$find %' THEN 0
           WHEN full_name like '$find%' THEN 1
           WHEN full_name like '% $find%' THEN 2
           ELSE 3
      END, full_name";
$query3 = mysql_query($sql) or die(mysql_error());
while ($row3 = mysql_fetch_array($query3)) {
echo $row3['full_name']."<br/>";
}

So my problem is that when i search for John it gives me only one result which is John Smith while i want it to give me

John Smith
John Smith
John Smith

I would appreciate any help

oke now you are changing your code change the extension you use too. As the mysql_* are deprecated as of PHP 5.5 and deleted from PHP 7.0 use [mysqli](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/book.pdo.php) instead. And also your code is open to SQL-injections use [prepared statements](http://stackoverflow.com/q/60174/5396496) when handling user input. please do this otherwise you're gonna regret it. — BRoebie, Dec 29 '15 at 13:15
Thanks for the advice i'll do it right now in my whole website — Tak Sid, Dec 29 '15 at 13:34

score 3 · Accepted Answer · answered Dec 29 '15 at 12:52

3

Remove the GROUP BY full_name it is grouping the John Smith names together.

answered Dec 29 '15 at 12:52

Clay

4,700
3
33
49

1

Thank you very much you saved my day <3,Pfff this rules it says you need 4 minutes to accept an answer – Tak Sid Dec 29 '15 at 13:01

MySQL - ORDER BY CASE WHEN doesn't work

1 Answers1