Complex MySQL search form - select statement

Hi Folks

Can anyone point me in the right direction with a MySQL statement.
I will try to layout my thinking here:

I have a form with three inputs area, name and search. I am trying to write a Mysql select statement that selects records from a single table if they match the criteria. Easy for two variables but I’m lost after that.

  1. The form includes these three inputs:

area - drop down menu (Any as default)
name - drop down menu (Any as default)
search box - text area (Blank as default)

  1. The form submits to itself leaving me with these three variables


SELECT * FROM database WHERE database.description LIKE ‘%$search%’ AND database.area LIKE ‘$area’ AND LIKE ‘$name’

  1. This is where I get confused. How do I get the SQL to Select everything correctly. I have tried using PHP if/else code to fix it but I end up running around in circles with six different Select statements and haven’t yet got that to work.

So I have come to the conclusion that there must be an easier way. I see search forms with dozens of search criteria on websites every day an d I only have 3 - so it can’t be this complicated. Right?
I know I need to start from the beginning again but can anyone let me know how to approach it before I begin?



PS - I know it probably does’t sound complex to you guys but it’s doing my nut in.

each of those conditions should be considered as optional

if a user doesn’t select an area, for example, then the query should return all areas, because that’s the default in the dropdown

the way to do it is to omit that condition in the sql, which would result in …

SELECT * FROM database WHERE database.description LIKE ‘%$search%’ AND LIKE ‘$name’

so how do you detect the “any” option with php? i am very sorry, because i don’t do php, so i can’t tell you

but i can give you a tip regarding how to construct the WHERE clause – start off with WHERE 1=1 and then you can append conditions to it with ANDs

I responded on a similar thread earlier today, take a look.

“The key is to get your sql working”

dude, you rock

:smiley: :smiley:

Thanks Guys,

That helped a lot. It is now working perfectly.