Query String For Search Results

[sintax63]

I currently have a functioning database search engine... thing :-\ on my web site. It works just fine if you are searching for one specific thing - for instance a persons first name. But when I do a search for a first AND last name (pulling from different fields in the same table), no results are displayed.

 

Meaning, "stan" works but "stan smith" does not.

 

Here is the query I am using if anyone could provide some assistance:

 

$srch="%".$search."%";

$query = "SELECT *, DATE_FORMAT( `date`, '%m.%d.%y' ) AS displayDate FROM sick WHERE (first LIKE '%$srch%' || last LIKE '%$srch%' || city LIKE '%$srch%' || hospital LIKE '%$srch%') AND status='1' ORDER BY last ASC";

$result = mysql_query($query);

 

Thanks in advance!

[sintax63]

I have been doing some digging on here and have changed up the code quite a bit. Still not working and is instead taking the last search term as the only one. For example "stan smith" only displays results for "stan".

 

Thus far, the $keywords_s outputs this format:

'%stan%' OR '%smith%'

 

<?

if ($search) // perform search only if a string was entered. 
{ 

include("connect.php");

$searched = $search; 

$letters = ereg_replace("[^A-Za-z0-9 ]", "", $searched);
$keywords = explode(" ", $letters);

foreach($keywords as $key => $value) {
if($value == "" || $value == " " || is_null($value)) {
unset($keywords[$key]); }
}

for($i=0; $i<count($keywords); $i++) {
if($i != (count($keywords)-1))
$keywords_s .= "'%" . $keywords[$i] . "%'" . " OR ";
else
$keywords_s .= "'%" . $keywords[$i] . "%'" ; }


$query = "SELECT *, DATE_FORMAT( `date`, '%m.%d.%y' ) AS displayDate FROM sick WHERE (first LIKE $keywords_s || last LIKE $keywords_s || city LIKE $keywords_s || hospital LIKE $keywords_s) AND status='1' ORDER BY last ASC";
$result = mysql_query($query);

if ($result) 
{ 

echo "<table id=\"table\" cellpadding=\"0\" cellspacing=\"0\" border=\"0\" summary=\"Results\"> \n\n";

echo "<tr> \n";
echo "<th scope=\"col\" abbr=\"Date\" class=\"nobg\">Date</th> \n";
echo "<th scope=\"col\" abbr=\"Last\">Last</th> \n";
echo "<th scope=\"col\" abbr=\"First\">First</th> \n";
echo "<th scope=\"col\" abbr=\"City\">City</th> \n";
echo "<th scope=\"col\" abbr=\"State\">State</th> \n";
echo "<th scope=\"col\" abbr=\"Alert\">Alert</th> \n";
echo "<th scope=\"col\" abbr=\"View\">View</th> \n";
echo "</tr> \n\n";

while( $row = mysql_fetch_assoc( $result ) ) {

$id		        = $row["id"];
$display_date	= $row["displayDate"];
$first		        = $row["first"];
$last		        = $row["last"];
$city		        = $row["city"];
$state		= $row["state"];
$location	        = $row["location"];
$hospital	        = $row["hospital"];

if($location == "0") {
$currently = "Currently At Home"; }

if($location == "1") {
$currently = $hospital; }

echo "<tr> \n";
echo "<td style=\"border-left: 1px solid #424242; letter-spacing: 0px;\">$display_date</td> \n";
echo "<td>$last</td> \n";
echo "<td>$first</td> \n";
echo "<td>$city</td> \n";
echo "<td align=\"center\">$state </td> \n";
echo "<td align=\"center\"><img src=\"/img/location$location.gif\" border=\"0\" title=\"$currently\" /></td> \n";
echo "<td align=\"center\"><a href=\"view.php?id=$id\"><img src=\"/img/view.gif\" border=\"0\" /></a></td> \n";
echo "</tr> \n \n";

}

echo "</table> \n\n";

} else { echo "there are database problems..."; } 

} else { echo "<p>Please go to the <a href=\"../search/\">search page</a> to perform a query on our database.</p> \n\n"; } 

?>

 

I could really use some help or suggestions. 

[sintax63]

Well I came up with a sloppy way to get the search accomplished (or at least I imagine it's sloppy)

 

for($i=0; $i<count($keywords); $i++) {
if($i != (count($keywords)-1))
$keywords_s .= "first LIKE '%" . $keywords[$i] . "%'" . " || last LIKE '%" . $keywords[$i] . "%'" . " || city LIKE '%" . $keywords[$i] . "%'" . " || hospital LIKE '%" . $keywords[$i] . "%' OR ";
else
$keywords_s .= "first LIKE '%" . $keywords[$i] . "%'" . " || last LIKE '%" . $keywords[$i] . "%'" . " || city LIKE '%" . $keywords[$i] . "%'" . " || hospital LIKE '%" . $keywords[$i] . "%' "; }


$query = "SELECT *, DATE_FORMAT( `date`, '%m.%d.%y' ) AS displayDate FROM sick WHERE ($keywords_s) AND status='1' ORDER BY last ASC";
$result = mysql_query($query);

 

 

[fenway]

Why not have a full-text index across all these columns?

[sintax63]

That would be awesome!

Can you explain a bit on how that is done?  :-\

[fenway]

You should check the refman for the syntax.

[sintax63]

You should check the refman for the syntax.

 

Fenway -

 

I have read through http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html as well as some of the linked documents, but am not really sure how to translate my current code (above) to the MATCH()... AGAINST routine, although it sounds like a much more efficient means of searching.

 

I'm not asking you to write out my script, but could you supply a quick example of the syntax?

[sintax63]

I noticed that my code has changed a bit since I last posed it above (just for reference)...

 

$searched = $search; 

$letters = ereg_replace("[^A-Za-z0-9 ]", "", $searched);
$keywords = explode(" ", $letters);

foreach($keywords as $key => $value) {
if($value == "" || $value == " " || is_null($value)) {
unset($keywords[$key]); }
}

for($i=0; $i<count($keywords); $i++) {
if($i != (count($keywords)-1))
$keywords_s .= "first LIKE '%" . $keywords[$i] . "%'" . " || last LIKE '%" . $keywords[$i] . "%'" . " || city LIKE '%" . $keywords[$i] . "%'" . " || location LIKE '%" . $keywords[$i] . "%'" . " || hospital LIKE '%" . $keywords[$i] . "%' OR ";
else
$keywords_s .= "first LIKE '%" . $keywords[$i] . "%'" . " || last LIKE '%" . $keywords[$i] . "%'" . " || city LIKE '%" . $keywords[$i] . "%'" . " || location LIKE '%" . $keywords[$i] . "%'" . " || hospital LIKE '%" . $keywords[$i] . "%' "; }


$query = "SELECT *, DATE_FORMAT( `date`, '%m.%d.%y' ) AS displayDate FROM sick WHERE ($keywords_s) AND status='1' ORDER BY last ASC";

 

 

[fenway]

You want "WHERE MATCH( first, last, city, location, hospital ) AGAISNT '$keywords[$i]'"

 

But you can put all of the words together....

[sintax63]

You want "WHERE MATCH( first, last, city, location, hospital ) AGAISNT '$keywords[$i]'"

 

But you can put all of the words together....

 

Oh yeah, that is much easier on the eyes. Of course I am still having issues. I finally realized that there is a typo in the word "AGAINST" in your above example, but sadly that still didn't get it working.

 

$searched = $search; 

$letters = ereg_replace("[^A-Za-z0-9 ]", "", $searched);
$keywords = explode(" ", $letters);

foreach($keywords as $key => $value) {
if($value == "" || $value == " " || is_null($value)) {
unset($keywords[$key]); }
}

for($i=0; $i<count($keywords); $i++) {
if($i != (count($keywords)-1))
$keywords_s .= "city LIKE '%" . $keywords[$i] . "%'" . " || state LIKE '%" . $keywords[$i] . "%'" . " || zip LIKE '%" . $keywords[$i] . "%'" . " || area LIKE '%" . $keywords[$i] . "%'" . " || name LIKE '%" . $keywords[$i] . "%' OR ";
else
$keywords_s .= "city LIKE '%" . $keywords[$i] . "%'" . " || state LIKE '%" . $keywords[$i] . "%'" . " || zip LIKE '%" . $keywords[$i] . "%'" . " || area LIKE '%" . $keywords[$i] . "%'" . " || name LIKE '%" . $keywords[$i] . "%' "; }

$query = "SELECT *, DATE_FORMAT( `date`, '%m.%d.%y' ) AS displayDate FROM sick WHERE MATCH( first, last, city, location, hospital ) AGAISNT '$keywords[$i]' AND status='1' ORDER BY id ASC LIMIT 7";
$result = mysql_query($query);

 

Any other suggestions?

 

 

[fenway]

What's not working about it.