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

问题描述：

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

我有一个表'users'有两列（id，full_name）我在那之后使用PHP创建了一个搜索框我搜索了如何通过相关性使其按顺序排列我找到了解决方案，但它没有正常工作所以如果有三行包含全名的John Smith那么它只给我一个结果就是表 p> id | full_name 1 | 约翰史密斯 2 | 约翰史密斯 3 | John Smith code> pre>

这是我的代码 p>


  $ find = $ _GET ['q']; \  n $ 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。=“ 或者full_name LIKE'％$ v％'“; 
} 
 
 $ i ++; 
} 
 $ sql。=”GROUP BY full_name ORDER BY CASE当full_name像'$ find％'那么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']。“＆lt; br /＆gt;”; 
} 
  code>  pre> 
 
  所以我的问题是，当我搜索John时，它只给我一个结果 John Smith  strong>，而我希望它给我 p> 
 
 
  John Smith  strong>  li> 
 
  John Smith  strong>  li> 
 
  John Smith  strong>   li> 
  ul> 
 
 我将不胜感激任何帮助 p  > 
  div>

答

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

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

相关推荐