I'm in the process of building a basic searching function for another project I'm working on.
I've managed to overcome most of the basics. I can search by one, all, or some of the fields and I can do boolean searches. On the topic of boolean searches, there is something I think I'm missing. I've probably overloaded myself today, so I'm posting here in the hopes somebody can catch something I've missed.
Basically, a user will input a search string. Lets say it's "queen". Because of how boolean searching with MySQL works, 4 characters is the minimum. This search would work and be executed.
If a user enters "of the", the search won't go (because of the MATCH() AGAINST() syntax).
Actually, during writing this, I've found out a few things. MATCH() AGAINST() will automatically ignore anything less than 4 characters. It takes out extra whitespace characters. So, it does a pretty good job.
However, what if I search by 2 words that are 4 non-whitespace characters or longer? I want it to perform a boolean search and return the matching results.
I have 2 items in my DB. "Indiana Jones and the Temple of Doom" and "Indiana Jones and the Temple of Hacking". I search for "indiana doom" and want it to return only the "Temple of Doom" listing, but it returns both.
Can somebody check over my code and tell me how I can fix this?
<?php
require_once ("GlobalConf.php");
require_once ("include/MySQLDbConnect.php");
$connector = new MySQLDbConnect();
echo ("<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">\n");
echo ("<html>\n");
echo ("<head>\n");
echo ("\t<title>SuperWaD | Home</title>\n");
echo ("\t<meta http-equiv=\"Content-Type\" content=\"text/html; charset=\"iso-8859-1\">\n");
echo ("\t<link rel=\"stylesheet\" href=\"include/gray.css\" type=\"text/css\">\n");
echo ("\t<LINK REV=\"made\" href=\"mailto:jared.harder@gmail.com\">\n");
echo ("\t<META NAME=\"keywords\" CONTENT=\"blog, blog site, forums\">\n");
echo ("\t<META NAME=\"description\" CONTENT=\"SuperWaD offers blog hosting, among other things. Come check it out.\">\n");
echo ("\t<META NAME=\"author\" CONTENT=\"WaD\">\n");
echo ("\t<META NAME=\"ROBOTS\" CONTENT=\"ALL\">\n");
echo ("</head>\n");
echo ("<body>\n");
include ("ExpandScript.php");
include ("header.php");
if ($HTTP_POST_VARS)
{
$title = $_POST['title'];
$user = $_POST['username'];
$type = $_POST['type'];
$titleLength = strlen($title);
$userLength = strlen($user);
$typeLength = strlen($type);
if (isset($title) AND $title != '' AND $titleLength >= '4')
{
$titleQuery = "MATCH(`listingName`) AGAINST('$title*' IN boolean MODE)";
$titleIsSet = '1';
$doTitleQuery = '1';
}
elseif (!isset($title) OR $title == '')
{
$titleQuery = '';
$titleIsSet = '0';
$doTitleQuery = '1';
}
elseif (isset($title) AND $titleLength < '4')
{
$doTitleQuery = '0';
}
if (isset($user) AND $user != '' AND $userLength >= '4')
{
if ($titleIsSet == '1')
{
$userQuery = "AND MATCH(`listingOwner`) AGAINST('$user*' IN boolean MODE)";
$userIsSet = '1';
$doUserQuery = '1';
}
elseif ($titleIsSet == '0')
{
$userQuery = "MATCH(`listingOwner`) AGAINST('$user*' IN boolean MODE)";
$userIsSet = '1';
$doUserQuery = '1';
}
}
elseif (!isset($user) OR $user == '')
{
$userQuery = '';
$userIsSet = '0';
$doUserQuery = '1';
}
elseif (isset($user) AND $userLength < '4')
{
$doUserQuery = '0';
}
if (isset($type) AND $type != '' AND $typeLength >= '4')
{
if (($userIsSet == '1') OR ($titleIsSet == '1'))
{
$typeQuery = "AND MATCH(`listingType`) AGAINST('$type*' IN boolean MODE)";
$doTypeQuery = '1';
}
elseif ($userIsSet == '0')
{
$typeQuery = "MATCH(`listingType`) AGAINST('$type*' IN boolean MODE)";
$doTypeQuery = '1';
}
}
elseif (!isset($type) OR $type == '')
{
$typeQuery = '';
$doTypeQuery = '1';
}
elseif (isset($user) AND $typeLength < '4')
{
$doTypeQuery = '0';
}
if ($doTitleQuery == '1' AND $doUserQuery == '1' AND $doTypeQuery == '1')
{
$result = $connector->doQuery("SELECT * FROM `listingInfo` WHERE ". $titleQuery ." ". $userQuery ." ". $typeQuery ."");
$row = $connector->getRows($result);
echo ("<br>"); if ($row != '0')
{
for ($r = 0; $r < $row; $r++)
{
$array = $connector->fetchArray($result, 'MYSQL_NUM');
$listingID = "a". $array[5];
$listingLinkID = "a". $array[5] ."_link";
echo("<br>");
echo("<div style=\"border: 1px solid #000000; padding: 0px; background: #EEEEEE; \">\n");
echo("\t<table border\"0\" cellspacing=\"0\" cellpadding=\"2\" width=\"100%\" style=\"background: #000000; color: #FFFFFF; \">\n");
echo("\t\t<tr>\n");
echo("\t\t\t<td align=\"left\">[<a title=\"show/hide\" id=\"$listingLinkID\" href=\"javascript: void(0);\" onclick=\"toggle(this, '$listingID');\" style=\"text-decoration: none; color: #FFFFFF; \">-</a>] ". $array[0] ." (". $array[2] .")</td>\n");
echo("\t\t</tr>\n");
echo("\t</table>\n");
echo("\t<div id=\"$listingID\" style=\"padding: 3px;\">Owner: ". $array[2] ."<br>Type: ". $array[1] ."<br>Rating: ". $array[3] ."/10<br>Information Link: <a href=\"". $array[4] ."\" class=\"a000001\" target=\"_blank\">Click Here</a></div>\n</div>\n\n");
echo("\t<script language=\"javascript\">toggle(getObject('$listingLinkID'), '$listingID');</script>\n");
}
}
else
{
echo $titleQuery ."<br>". $userQuery ."<br>". $typeQuery ."<br>SELECT * FROM `listingInfo` WHERE ". $titleQuery ." ". $userQuery ." ". $typeQuery ."";
echo ("Sorry, no results found. :<");
}
}
else
{
echo ("Sorry, one of the entered values contained less than 4 characters. Search aborted!");
// echo "<br>". $doTitleQuery ."<br>". $doUserQuery ."<br>". $doTypeQuery ."<br>". $titleLength ."<br>". $userLength ."<br>". $typeLength;
}
}
else
{
echo ("Please enter at least one of the following:<br>");
echo ("<form name=\"search\" action=\"search.php\" method=\"POST\"><br>");
echo ("Title: <input type=\"text\" name=\"title\"><br>");
echo ("Username: <input type=\"text\" name=\"username\"><br>");
echo ("Type: <input type=\"text\" name=\"type\"><br>");
echo ("<input type=\"submit\" value=\"Search\"><br>");
echo ("<br><br>For testing, applicable values are:<br><li>Username = wad, daw<br><li>Title = queen of the damned, collateral, alias, saved by the bell<br><li>Type = tv, movie<br>");
}
echo ("<br>");
include "footer.php";
echo ("</html>");
?>
Thanks again for any help!
