A Better Search Engine for MySQL database

[matthewamzn]

I'm using quite a simple php search script to display results for queries into my mysql database. It stinks, the results aren't displayed based on relevance and you have to type in an exact phrase. I have tweaked it some, but it's still not very good.

I've been looking around, but can't find a program that will search my database and display the results in a well organized way.

Any suggestions?

[arran]

Hi matthewamzn

You may wish to check out MySQL's Full-Text Search [dev.mysql.com]

arran.

[matthewamzn]

I set a fulltext up, but now I'm not sure how to integrate it into my old search script:

<?php
// display the main search graphics
include "head.php";
?>
<table width="630" border="0" cellspacing="0" cellpadding="0" align="center"><tr>
<td valign="top"><?php

//MySQL options
$user = "example";
$pass = "example";
$host = "localhost";

//Database options
$database = "database"; // the database to search
$table = "table"; // the table to search
$col = "name"; //column to search.
$col2 = "description"; //Second column to search
$col_two = ""; //second column to display, it displays the cell in this colunm on the same row
$sorted= "price"; //what to sort by
$limit=25; // how many at max to display on one page

?>
<?php

if($col == ""){
$col = trim(@$_GET['col']); //trim whitespace from the stored
}else{}

if($sorted == ""){
$sorted = trim(@$_GET['sort']); //trim whitespace from the stored
}else{}

// Get the search variable from URL
$var = @$_GET['q'];
$trimmed = trim($var); //trim whitespace from the stored variable

$s = trim(@$_GET['s']);
// rows to return

// check for an empty string and display a message.
if ($trimmed == "")
{
echo "<p>Please enter a search...</p>";
exit;
}

// check for a search parameter
if (!isset($var))
{
echo "<p>We dont seem to have a search parameter!</p>";
exit;
}
mysql_connect($host,$user,$pass);
mysql_select_db($database) or die("Unable to select database"); //select which database we're using

$query = "select * from $table where $col like \"%$trimmed%\" or $col2 like \"%$trimmed%\" order by $sorted";

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

// If we have no results
echo "<title>search results for \"".$trimmed."\" in ".$col."</title>";
if ($numrows == 0)
{

//echo "<p span class=searchboxtype3>Sorry, your search: \"" . $trimmed . "\" returned 0 results.</span>";
}

// determine if s has been passed to script, if not use 0
if (empty($s)) {
$s=0;
}

// get results
$query .= " limit $s,$limit";
$result = mysql_query($query) or die("Couldn't execute query");

// display what the person searched for
//echo "<p>You searched for: \"" . $var . "\"</p>";

// begin to show results set
//echo "Results:<br>";
$count = 1 + $s ;
/* This its the header, be sure you update this to match your columns in your MySQL table */
echo "<table width=\"500\" align=center>";
echo "
<tr>
<td colspan=3 class=qtypes>There are $numrows products that match \"$trimmed\". Products are sorted by price.
</td>
</tr><tr>
<td colspan=3></td>
</tr>\n" ;

while ($row= mysql_fetch_array($result)) {
$col_1 = $row['name'];
$col_2 = $row['product_url'];
$col_3 = $row['company'];
$col_4 = $row['price'];
$col_5 = $row['thumbnail_url'];
$col_6 = $row['description'];
$col_7 = $row['image_url'];
$col_8 = $row['count'];

echo "<tr>";
echo "<td align=\"center\" valign=\"middle\"> <a href=\"$col_7\"><img src=\"$col_5\" border=\"0\"></a> </td>";
echo "<td>&nbsp;&nbsp;</td>";
echo "<td> <span class=searchboxtype3>$col_1:</span><br><span class=searchboxtype4>$col_6<br>Count: $col_8<br>Price: </span><span class=price>$$col_4</span><span class=searchboxtype4><br>From: $col_3<br></span><a href=\"$col_2\" target=\"_blank\"><img src=\"http://example.com/Images/buynow.gif\" border=\"0\"></a></td>";
/* End edit */
echo "</tr><tr><td colspan=3><hr></td></tr>\n";
$count++ ;
}
echo "</table>";
$currPage = (($s/$limit) + 1);

//break before paging
// echo "<br>";
?><table align="center"><tr><td align="center"><?php
// next we need to do the links to other results
if ($s>=1) { // bypass PREV link if s is 0
$prevs=($s-$limit);
print " <span class=searchboxtype3><a href=\"".$_SERVER["PHP_SELF"]."?s=".$prevs. "&q=" .$var. "&col=" .$col."&sort=" .$sorted."\">&lt;&lt; Prev ".$limit."&nbsp;&nbsp;</a></span> ";
}

// calculate number of pages needing links
$pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division

if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}

// check to see if last page
if (! ((($s+$limit)/$limit)==$pages) && $pages!=1) {

// not last page so give NEXT link
$news=$s+$limit;
echo "<span class=searchboxtype3><a href='" .$_SERVER["PHP_SELF"]. "?s=" .$news. "&q=" .$var. "&col=" .$col."&sort=" .$sorted."'>&nbsp;&nbsp;Next ".$limit." &gt;&gt;</a></span>";

}

$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;
echo "<span class=searchboxtype4><br><br>Showing results $b to $a of $numrows</span><br><br><br>";

?>

[edited by: tedster at 9:04 am (utc) on Aug. 29, 2005]
[edit reason] use example.com in code [/edit]

[matthewamzn]

I tried changing the query:
$query = "select * from $table where $col like \"%$trimmed%\" or $col2 like \"%$trimmed%\" order by $sorted";

to something like this:
$query = "select * from table where match(name, description, price) against('$trimmed');

But I couldn't get it to work. Any ideas?