Ive got the PHP search. How do i list with relevancy

[corvinedesign]

i have a large mysql database. I wrote a search that looks into my keywords field to see if that listing has a match. This is my problem. I need the script to explode the keyword phrase entered by the user. find the matches in the many meta data fields in my database. and then sort it all based on how many times there are matches thus showing the results with relevancy. here is the code.
by the way its sloppy sorry.

<?PHP
mysql_connect (localhost, root, iqs);
mysql_select_db (production);
if ($Meta == "") {$Meta = '%';}
$sql = "SELECT * FROM KEYWORDPT1 WHERE Meta LIKE '%$Meta%'";
$result = mysql_query($sql);
$num_rows = mysql_num_rows($result);
if ($row = mysql_fetch_array($result)) {
print "<table border=0 cellspacing=2 cellpadding=5><tr><td align=left><b>Search Results for "; print "$Meta,"; print "</b></td><td>&nbsp &nbsp&nbsp&nbsp"; echo "Your Search Returned<b> $num_rows </b>Results"; print "</td></tr></table>";

print "<table border=0 cellspacing=2 cellpadding=3>"; print "<tr><hr></tr>";
do {
print "<tr>";
print "<td>"; print "<a href=http://"; print $row["URL"]; print ">"; print "<font size=-1>"; print $row["URL"]; print "</font></a>"; print "</td>";
print "<td><font size=-1>"; print $row["Title"]; print "</td>";
print "</tr>"; print "<tr><td><hr></td><td><hr></td><td><hr></td></tr>";

} while($row = mysql_fetch_array($result));
print "</table>"; }

else print "Sorry, no records were found!"
?>
</td>
<td width="24" background="02_Images/middler.gif" height="1%"><br>
</td>
</tr>
<tr valign="top">
<td width="106" height="129"><a href="mailto:sales@*********.com"><img src="02_Images/footerl.gif" width="106" height="129" border="0"></a>
</td>
<td height="129"><a href="http://www.*********.com"><img src="02_Images/footerm.gif" width="430" height="129" border="0" align="top"></a></td>
<td width="24" height="129"><img src="02_Images/footerr.gif" width="104" height="129" border="0" align="top"></td>
</tr>
</table>

</div>
</body>

</html>

[ukgimp]

Here is what I got working without the ranking.

Hope that helps a bit.

##############################
$keywords = trim(urldecode($keywords));
$keywords = ereg_replace("([ ]+,)"," ",$keywords);

if(!ereg(" ",$keywords))
{
// Only 1 keyword
$KeyWords[0] = "$keywords";
} else {
$KeyWords = explode(" ",$keywords);
}

$sql = "SELECT DISTINCT + your SQL (";
$count = count($KeyWords);

if($count == 1)
{
$single = $KeyWords[0];
$sql .= "Field1 LIKE '%$single%' OR Field2 LIKE '%$single%' )";
} else {
$ticker = 0;
while ( list ($key,$word) = each ($KeyWords) )
{
$ticker++;
if(!empty($word))
{
if($ticker!= $count)
{
$sql .= "(Field1 LIKE '%$word%' OR Field2 LIKE '%$word%') OR ";
} else {
// Last condition, omit the trailing OR
$sql .= " (Field1 LIKE '%$word%' OR Field2 LIKE '%$word%') ";
}
}
}
$sql .= ") ORDER BY Whatever";
}

[ukgimp]

By the way welome to WebmasterWorld

:)

[corvinedesign]

Why thanks for the welcome. could i just sort the results by count ascending. would that work.

[corvinedesign]

With the code above this will oxplode the keyword phrase,search the correct field and then assign a match count to each match. How do i then get it all to display in a relevant order.

[jatar_k]

Welcome to WebmasterWorld corvinedesign

What about just listing them by count ascending as you mentioned?