Multi criteria search

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
thiscatis
Forum Contributor
Posts: 434
Joined: Thu Jul 20, 2006 11:00 am

Multi criteria search

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Using a loop control you should be able to both detect empty fields and generate the SQL needed.
thiscatis
Forum Contributor
Posts: 434
Joined: Thu Jul 20, 2006 11:00 am

Post 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..
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
thiscatis
Forum Contributor
Posts: 434
Joined: Thu Jul 20, 2006 11:00 am

Post by thiscatis »

Ok, that makes sense.
Thanks for your quick reply and i'll post what I came up with!
Post Reply