Page 1 of 1

Multi criteria search

Posted: Wed Mar 14, 2007 7:53 am
by thiscatis
Hi all,

I'm new to writing search scripts for your website, I've done it for a single field but
I'm experiencing some problems when using a form with multiple fields/criteria.

I have a form with multiple fields like name, surname, etc...
Which people can leave blank, so they decide which criteria they use to search the database.

I first tried it with an SQL Query using multiple > WHERE name LIKE '%name' AND surname LIKE ...
But that only gave results when all the fields were filled and matched correctly.
Then I changed the AND LIKE to OR LIKE but that just showed all the database entries.

I'm completely lost here, searched google but couldn't find any real usefull info {or am I not looking in the right places?}
Anyone has an idea on how to build your queries when having multiple criteria and partial matches..
For e.g if someone enters John Doe, but the database only contains a John Known it still shows it..
And should I use %..%, %... or ...% as a pattern?

Posted: Wed Mar 14, 2007 7:59 am
by feyd
Using a loop control you should be able to both detect empty fields and generate the SQL needed.

Posted: Wed Mar 14, 2007 8:11 am
by thiscatis
Thanks feyd, could you point me in the right direction for the loop statement?
I think I can figure the rest out then..

Posted: Wed Mar 14, 2007 8:15 am
by feyd
foreach.

Typically, I'll write the individual SQL statements into an array then implode() it afterward. If the array is empty I either fail, or remove the WHERE clause altogether.

Posted: Wed Mar 14, 2007 8:17 am
by thiscatis
Ok, that makes sense.
Thanks for your quick reply and i'll post what I came up with!