search code

Anon

Hi,
How would I write a script that would search several fields in a MySql db? Example I am trying to match $last, to the data in field lname, o1last, o2last, o3last, etc.
Thanks
FTW

Anon

Sample:

You have three fields, name, surname,description and address. You want to match the rows where the name begin with J, the surname end with C and the description includes the word mistake, even if it is into another word.

SELECT * FROM people WHERE name LIKE 'J%' AND surname LIKE '%C' AND description LIKE '%mistake%'

What % means:

J% - Begin with J, no matter what is after J

%C - End in C, no matter what is before C

%mistake% - No matter what comes before or after mistake, even if it is into another word, it is valid (mistaken, for example)

Replace the last of the SQL by

AND description LIKE 'mistake'

and you only get the word by itself, not as part of another.

This is my way to do it, and it works to me.

Hope it helps

fLIPIS

Anon

Hi,
Thanks for the script using LIKE and %. I use both of them in other searches, but in this case, I need a shortcut that will actually search 11 seperate fields for the same keyword.
Thanks
FTW

Anon

Ok, try this:

SELECT * FROM table WHERE field1 AND field2 AND ... (to field 11) IN ('keyword') (You can also use the % in this query)

Hope this helps

fLIPIS

Anon

WTF. I need some sleep. Just forget my last post. Won't work. Sorry, m8

fLIPIS

Anon

$q = "select * from t where '" . $last . "' in (lname, o1last, o2last, o3last)" ;

That's the shortest if you need an exact match. If you have to use like then you need to specify each predicate separately.

Anon

Hi,
Thanks for the script. In this case, it will be for an exact match, so this shortcut will work just great.