I have written a code to search for ads using tags. The code delimits(space) the search string entered by the user and matches each word with the tag in database separated by commas using LIKE clause. I am getting correct result. But what i want is that the row which matches more tags with the search strings word should appear at the top.
For example:- tag in 1st rows(in db) is "samung,mobile,phone" and in the 2nd row its "samsung,phone" and the search string is "samsung mobile phone" then the 1st row should appear first as it has more matches then 2nd row. How can i do it using ORDER BY.
My code is:-
<?php require_once("db_connect.php"); ?>
<form action="" method="POST">
<input type="text" name="searchtext" />
<input type="submit" name="submit" value="Search" />
</form>
<?php
if(isset($_POST["submit"]))
{
$searchquery = $_POST["searchtext"];
$searcharray = array();
$searcharray = explode(" ",$searchquery);
if(!isset($searcharray[1]))
$searcharray[1] = "XXXXXXXX";
if(!isset($searcharray[2]))
$searcharray[2] = "XXXXXXXX";
if(!isset($searcharray[2]))
$searcharray[2] = "XXXXXXXX";
if(!isset($searcharray[3]))
$searcharray[3] = "XXXXXXXX";
$query = "select * from search where tag LIKE '%$searcharray[0],%' OR tag LIKE '%$searcharray[1],%' OR tag LIKE '%$searcharray[2],%' OR tag LIKE '%$searcharray[3],%' ";
$result=mysql_query($query);
while($row = mysql_fetch_array($result))
{
echo $row["name"]. '<br>' ;
}
Comparison operators return 0 or 1 depending on whether they matches, so you can add them to count the number of matches:
$searcharray = explode(" ",$searchquery);
$matchexpr = implode(' + ', array_map(function($x) { return "(find_in_set('$x', tag) > 0)";}, $searcharray));
$query = "select *,
($matchexpr) as matches
from search
having matches > 0
order by matches desc";
No there are no spaces around the commas.
I have changed the line :-
$matchexpr = implode(' + ', array_map(function($x) { return "find_in_set('$x', tag) > 0";}, $searcharray));
to
$matchexpr = implode(' OR ', array_map(function($x) { return "find_in_set('$x', tag) > 0";}, $searcharray));
Its getting the rows correctly but the order is not correct. Its returning the rows as in the original order i think its because OR does not concatenate the returned value.
Using ur code :_
"samsung mobile" word is returning 2 result (viv and samsung psp). "mobile samsung" is returning only 1 result (sony mobile).