MySQL - 按案例排序什么时候不起作用

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

Answer 1

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