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