Separating words in a search?

Archbob

In mysql select state. I know that the code:

SELECT * from table where name like '%space shooter&'

will give you all entries where the name field is space shooter but how do you get it to be where it selects all entries that contains either space or shooter without doing something like the following:

SELECT * from table where name like'%space%' or name like '%shooter%'

Thanks in advance.

RickyRak

You can use the word AND to seperate or the . function as well. where you would do something like this:

Select * FROM 'tablename' WHERE name=space AND name=shooter.

or try

Select tablename.space, tablename.shooter, FROM tablename

I believe either of these should work.

Archbob

Thanks, but thats not really what I was looking for is I have a form that has an input type of text like this:

<input type='text name='search' value='submit'>

and my SQL statement looks like this:


SELECT*from table where name like '%$search%

Now if the user enters two words in the search text field in the form how do I get results to show up that match either the first or second word instead of both together like my statement does now.

clearbreach

I won't write the code itself, but you could split the search text searching for spaces, like:

$eachsearch = split(" ", $search);

which would return an array with an item for each word (assuming each word is seperated by a space character). Then you would loop and create the SQL select statement, like:

$sqlstmt = "SELECT * FROM table WHERE"
for ($i = 0; i <= count($eachsearch); $i++)
{
    $sqlstmt = $sqlstmt . " AND name LIKE '%$eachsearch($i)%";
}

Bleh, obviously that loop wouldn't work ideally, but I hope that gives you a bit of a hint.

(I'd like to help more, but it's been a while since I've done any PHP programming and I'm rusty.)