like command, please help

kidestranged

A site of mine has a table with a NAME and DESCRIPTION field. I have a search engine that displays the records that match the search words that a user enters. Here the part of the code that does that:

$sql= "SELECT * FROM $dbtable";
$sql.= " where antique_mantel ='$antique_mantel' and (description like $searchstring or name like $searchstring)";

if you go to the page (http://www.francisjpurcell.com/antiques.html) you’ll see that it works but it only shows matches where the order is the same.
For example, one of the descriptions might say, "a FRENCH rosewood TABLE." If you put french and wooden in the search box (in the order), mysql finds the record. If you put wooden and french (opposite order) mysql doesn't return the record. What gives?
Thanks for you help.

ace21

do you do anything with $searchstring before the sql statement?

if so can you post the code. else the like statement will look only for what was typed in. if you type in 'french wooden' it will only return results that have exactly that phrase in it not ones with either french or wooden it it.

Ace21

[deleted]

Did you read the mysql manual page about LIKE?
http://www.mysql.com/doc/S/t/String_comparison_functions.html

amcgrath

for what its worth, i did it both ways and didn't get anything....

[deleted]

thats probably because 'LIKE' without wildcards is the same as '=', which doesn't do what he wants. 🙂

tasistro

$sql.= " where antique_mantel ='$antique_mantel' and (description like %$searchstring% or name like %$searchstring%)";

or break down searchstring into search words

something like

do while strtok loop
{
$searchwords=strtok of searchstring on " ";
}

// build sql

for all elementes in $searchwords
{
$sql=$sql." description like %$searchwords[counter]% or name like %$searchwords[counter]% or";
}

$finalsql="select * from whatever_table where ".$sql." order by whateveryouwant... etc"

runsql on finalsql

It will return all rows which contain the searchwords in the description and name fields

Obviously it is quasi nemonics so the code should be further developed.

Also, take a look at full text index and full text searches, really cool stuff.

Saludos
Gerardo

kidestranged

First off, thanks to all you have helped so far.

$searchstring is actually the result of a code that converts the user input into something that mysql can understand. For example, it converts wood chair into '%wood%,%chair%' I just want a code that will return all the records that have either of those words in it.
Thanks again everyone.

kidestranged

Gerardo -
I played around with your code a bit. I think that I should put the search words into and array and run a loop in the select statment.

I'll play around with it and give you guys an update. Thanks to everyone for posting.

kidestranged

I figured it out for all you curious minds:

first I cleaned up the searchstring and seperate the words with commas then the following code takes it from there.

$eachword = explode(",",$searchstring);
$sql= "SELECT * FROM $dbtable";
$sql.= " where description like $eachword[0]";
for ($i=1;$i<count($eachword);$i++) {
$sql.= " and description like $eachword[$i]";
}
$query = mysql_query($sql, $con);

right now I have an 'and' in the clause which gives me results that have both words in the description but I can change it to 'or' if I want it to show results for either word. I may add a check box on the input page that asks if you want the results to match any word or all of the words.

[deleted]

You may want to read the 'slapping together a search engine' article in the articles section.