Multi word search?

[SlayerACC]

How can you setup you web seach to do a multi word like search?

I have a search on my site now that I put in.. it works great if you put in one word and spell it correct... but if you do not spell it right or have more that one word...

your results are 0...

if they have multi words and they are spelt correct and the way I have them in the table they will show...


What can I do about this problem..

Thanks.. Slayer.

[Old Pedant]

Can't do anything about spelling mistakes. Too bad for the stupid user.

But suppose the search is for "ocean resort"

You might do something like:

SELECT * FROM table WHERE somefield LIKE '%ocean%' OR somefield LIKE '%resort%'

Whether you use OR or AND between the two LIKEs depends on whether the search must find both (all) words or not.

Since you are showing NONE of your current code, I can't change non-existent code for you.

[SlayerACC]

This is what I currently have..

PHP Code:

SELECT * FROM campgrounds WHERE (`name` LIKE'%$find%') OR (`city` LIKE'%$find%') OR (`notes` LIKE'%$find%') OR (`moreinfo` LIKE'%$find%') OR (`amenities` LIKE'%$find%') ORDER BY `name` 


Using this search..

I would search for New Old Town and it would give me 0 results.
if I just search town I get 15 results
if I search just old I get 20 results
and if I search for new I get 7 results.

Not sure what I have done wrong.

Thanks... all help appreciated.


Slayer.

[Old Pedant]

ONE more time:

You *MUST* split up the $find into SEPARATE WORDS and use LIKE on *EACH* word!

So if $find is "New Old Town" your query would need to be:

SELECT * FROM campgrounds 
WHERE (name LIKE '%new%' AND name LIKE '%old%' AND name LIKE '%town%') 
   OR (city LIKE '%new%' AND city LIKE '%old%' AND city LIKE '%town%') 
   OR (notes LIKE '%new%' AND notes LIKE '%old%' AND notes LIKE '%town%') 
   OR (moreinfo LIKE '%new%' AND moreinfo LIKE '%old%' AND moreinfo LIKE '%town%') 
   OR (amenities LIKE '%new%' AND amenities LIKE '%old%' amenities city LIKE '%town%') 
ORDER BY name

The above assumes you want to find ALL THREE WORDS (in any order or place) in the SAME field (e.g., maybe moreinfo contains "This town has more old buildings than old."

If finding *ANY* ONE of the words in any of the fields is good enough, then replace all the AND with OR instead.

[SlayerACC]

how can you split up the words? and then search all the fields?

Sorry...


Slayer.

[Old Pedant]

I don't use PHP.

I'll give it a try, but don't be surprised if I mess up the PHP code.

<?php

$search = $_POST["search"];
$words = explode( " ", $search ); // get array of words
$pattern = "";
for ( $w = 0; $w < count($words); ++$w )
{
    $word = $wods[$w];
    if ( $word != "" )
    {
        if ( $pattern != "" ) $pattern .= " AND "; // or use " OR " as discussed!
        $pattern .= " ## LIKE '%" . $word . "%' ";
    }
]
if ( $pattern == "" )
{
    echo "You didn't give me anything to search for!  I quit!";
    exit( );
}

$pattern = "( " . $pattern . ") ";
$where = " WHERE " . str_replace( "##", "name", $pattern );
$where .= " OR " . str_replace( "##", "city", $pattern );
$where .= " OR " . str_replace( "##", "notes", $pattern );
$where .= " OR " . str_replace( "##", "moreinfo", $pattern );
$where .= " OR " . str_replace( "##", "amenities", $pattern );

$sql = "SELECT * FROM campground " . $where . " ORDER BY name";

// remove next line after it starts working
echo "DEBUG SQL: " . $sql . "<hr/>\n";

$result = mysql_query( $sql ) or die( mysql_error() );

,,, etc ,,,

[SlayerACC]

Hey Old Pedant..


this is working.... sort of

whe I search still for one word... works great...

when I search for multi...

I get this error..

I echoing the search so see what it looks like to..

## LIKE '%new%' AND ## LIKE '%old%'

and DEBUG SQL:

ArrayDEBUG SQL: Resource id #15

Thanks.

[SlayerACC]

I changed it to "OR"

if ( $pattern != "" ) $pattern .= " AND "; // or use " OR " as discussed!
$pattern .= " ## LIKE '%" . $word . "%' ";

and it works like a charm...


You are awesome...


thank you!! sooooooo much!!!

P.S. your php was just fine.... I got it worked out...


Slayer.

[Old Pedant]

Originally Posted by SlayerACC

and DEBUG SQL:

ArrayDEBUG SQL: Resource id #15

Pardon me, but that's impossible if you actually used the code I showed.

In my code $sql is nothing but a string. That looks like you echoed the $result.

And if the OR version worked, the AND version should work, too. But only if you actually had some data that matched the condition! In the $pattern that you showed:

## LIKE '%new%' AND ## LIKE '%old%'

that would mean that one of the tested fields has to contain *BOTH* "new" and "old".

As I said, it depends on what you want.

[SlayerACC]

you are correct Old Pedant,


It was from my code.. I was echoing that info to see what the output was gonna be..

I tested it using "And" too and you are right there as well..

All works great..

I wish I asked this question a year ago...

Thanks again for everything.

Slayer.

[Old Pedant]

You know, you COULD offer you users the choice of "Find matches on any words" vs. "Find matches on all words".

Do something like this:

<label>
    <input type="radio" name="delimiter" checked value=" OR " />
    Find matches on any one or more words
</label>
<label>
    <input type="radio" name="delimiter" value=" AND " />
    Find matches only on all words
</label>

And then in your PHP code, you do:

$delimiter = $_POST["delimiter"];
$search = $_POST["search"];
$words = explode( " ", $search ); // get array of words
$pattern = "";
for ( $w = 0; $w < count($words); ++$w )
{
    $word = $wods[$w];
    if ( $word != "" )
    {
        if ( $pattern != "" ) $pattern .= $delimiter;
        $pattern .= " ## LIKE '%" . $word . "%' ";
    }
}
... rest same ...

Now you have the best of both worlds: Both kinds of searches for the cost of a pair of radio buttons!

[SlayerACC]

That is a great idea.... I will have to implement that to the website.

Thanks again for everything.


Slayer.