CODEWITHSUNDEEP
phpmysqlsearchsql-order-bycase
Tak Sid
Tak Sid

Reputation: 25

MySQL - ORDER BY CASE WHEN doesn't work

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

I would appreciate any help

Upvotes: 0

Views: 410

Answers (1)

Clay
Clay

Reputation: 4760

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

Upvotes: 3

Related Questions