Examining variables

[thefisherman]

Hi,

i've got this little search engine where users can type in a keyword, in order to find a photograph.

The search engine searches through the description of the photographs (using MySQL) to see if there's a match, something like this:

$SQL_statement = "SELECT * FROM table WHERE description LIKE '%$keyword%' LIMIT " . $start . "," . $limit;

That works fine, BUT..

If a user searches for let's say: bathtub, he gets three matches, but if a user types this: bath tub, he gets nothing.

Is there a way to deal with this?, i mean, in this case, if i take out the space and combine the two words and search for that, it would work.

Or what about three words or more, like: big bath tub, or: big white bath tub.

thnx!

thefisherman

[ligraci]

Try this:

when $keyword="bathtub" the $SQL_statement should be
$SQL_statement = "SELECT * FROM table WHERE description LIKE '%$keyword%' LIMIT " . $start . "," . $limit;

but when $keyword="big bath tub" transform $keyword="big", $keyword2="bath", $keyword3="tub" then use
$SQL_statement = "SELECT * FROM table WHERE ((description LIKE '%$keyword%') OR (description LIKE '%$keyword2%') OR (description LIKE '%$keyword3%')) LIMIT " . $start . "," . $limit;

[khaine]

You may be trying to hold the users hand just a little too much. If they search for bath tub then thats what they get.

However you could always do a repetitive search by dropping various spaces.

<?

$input_string="big bath tub";
$bits=explode(" ", $input_string);

$search_string=str_replace(" ", "", $input_string);
echo "Search string is : ".$search_string."\n<BR>\n";

for ($loop=0; $loop<(count($bits)-1); $loop++)
{
    unset($search_string);
    for ($loop2=0; $loop2<$loop; $loop2++)
    {
        $search_string.=" ".$bits[$loop2];
    }
    $search_string.=" ".$bits[$loop].$bits[$loop+1];
    for ( $loop2=$loop+2; $loop2<count($bits); $loop2++ )
    {
        $search_string.=" ".$bits[$loop2];
    }
    trim($search_string);
    echo "Search string is : ".$search_string."\n<BR>\n";
}

?>

That should show you how to group up the words. 😃

BTW using OR in a database query can slow down the matching. If the database is very large it can be quicker to run seperate queries.

[cahva]

I dont know how much this will slow down your search(I use it with table more than 10000 rows with good speed) but it would shorten your code 🙂

$search=$_GET['search'];
$split=explode(' ',$search);
$search2=implode('|',$split);
$SQL_statement = "SELECT * FROM table WHERE description REGEXP '".$search2."' LIMIT " . $start . "," . $limit;

[khaine]

I didn't know you could use regexp's in mysql. Looks like I just learnt something new.

I like it when someone shows a much better way to do something. It just proves to me that I don't know as much as I think I do 😃