Simple Search Engine Problem

Anon

This is my first real work in PHP. I'm trying to make a search engine that uses mySQL database. It's quite simple as all information is stored in the database already.

Here is what it looks like right now:
http://www.zybez.com/search/?search=&showresults=5&submit=Search

I need it to search by relavance and not by database row position.

Here is the code that I'm using:

<head><link href=../styles.css rel=stylesheet type=text/css></head>
<body>
<form name="form">
Search<br>
<input type="text" name="search" style="background-color:#555555; color:white" value="<? echo "$search"; ?>"><br>
Maximum Results:<br><select name="showresults" style="background-color:#555555; color:white">
<option value="5">5</option>
<option value="10">10</option>
<option value="20">20</option>
<option value="50">50</option>
</select><br>
<input type="submit" name="submit" value="Search" style="background-color:#555555; color:white">
</form>
<?
//Format search
$search = str_replace(" ","%%","$search");
//-Break into array:
//$search = explode(" ",$search);

//Connect to mySQL
$dbcnx = @mysql_connect("localhost", "zybez","8tpfdca");
if (!$dbcnx) {
echo( "<p>Unable to connect to the database server at this time.</p>" );
exit();
}

//Open Database
mysql_select_db("zybez_db", $dbcnx);
if($showresults<1){
$showresults=30;
}

//$result = @("SELECT * FROM Search WHERE pagekeywords LIKE '%$search%' OR pagetitle LIKE '%$search%' OR pagedescription LIKE '%$search%' LIMIT 0,$showresults ");
$result = @("SELECT id,pagetitle,pagedescription,pageurl,pagerank,site,count($search) as score FROM Search WHERE pagekeywords IN($search) ORDER BY score DESC");
if (!$result) {
echo("<p>Error performing search: " . mysql_error() . "</p>");
exit();
}
// Display the results in a paragraph for each entry
$numreturn=0; // This variable counts the number of entries returned

while ( $row = mysql_fetch_array($result) ) {
$numreturn = $numreturn+1;
echo("<table align=center border=1 width=60% bordercolorlight=#808080 cellspacing=1 bordercolordark=#808080 bordercolor=#C0C0C0 cellpadding=0><ul><li><b>$numreturn</b>");

echo("<tr><td bgcolor=#505050 valign=top>");
echo("<b><a href=$row[4]>$row[1]</a></b>");
echo("</td></tr>");
echo("<tr><td valign=top>");
echo("$row[2]");
echo("</td></tr>");
echo("<tr><td bgcolor=#505050 valign=top");
echo("<p align=right><font size=1>Website: $row[6] | Rating: $row[5] | Entry: $row[0]</font>");
echo("</td></tr>");
echo("</li></ul></table><br>");
}

// Unformat search
$search = str_replace("%%"," ","$search");

if($numreturn>0){ //Check to see if no entries are returned
echo("<center><font size=1>Your search for &quot<b>$search</b>&quot returned <b>$numreturn</b> results</font></center>");
}
else{
echo("<center><font size=1>Your search for &quot<b>$search</b>&quot returned no results.<br>Try again and use just one word</font></center>");
}
?>
</body>

Here is what the table looks like:

id is simply 1,2,3,4...
pagetitle - stored as 'varchar' text
pagedescription - this is blob
pagekeywords - example:'articles newspaper'
pageurl - also a blob
pagerank - simply a small int
site - a varchar with text

Again, I need it to search by relevance.

W13

Anon

Sorry for the cluttered code, here is a slightly less messy version:

http://www.zybez.com/search/index.php.txt

W13

blackmamba

There is a far easier way to do what you're attempting, MySQL provides it. Follow the relevant documentation here http://www.mysql.com/doc/F/u/Fulltext_Search.html and you should have a much simpler and far more efficient script. By the way, instead of incrementing $numreturn in a loop, simply use the mysql_num_rows() function to get the number of rows returned i.e.
$numreturn = mysql_num_rows($result);