Stuck! Creating Dyanmic MySQL Search Query

[L8knight]

I have been "brain cramped" on this problem for two days and I'm hoping someone here can at least get me pointed in the right direction. I've looked everywhere for a good example or explanation for what I'm trying to do and nothing has given me what I think I need... 😕

I have a search form that allows the user to search for one or all of the various fields: $Server (Server name), $time (Timeframe which has a start and end date), and $status (which can be Successful '1', Partial '2' or Failure '3' - this is also a 'multiple' in the form so they could conceivably pick all three).

So, I am trying to write the PHP side that takes their input and determines the SQL Query but all the possibilities and how to handle the AND's and OR's for any combination is confusing me.

I have started with:
$base = "SELECT * FROM servers,backup,status,images,contact WHERE ";

if (isset($status)){
for($x=0;$x<(count($status)-1);$x++)
{
$base .= " field='$status[$x]' OR ";
}
$base .= " field='$status[(count($status)-1)]'";
}

But I am at a complete loss... and only confusing myself more.

Can anyone point me to a tutorial or possible sample code for what I'm trying to do... or if there is a simpler way to do this, I'm open to that to 🙂

[swr]

Usually you just want "match any of these conditions" (using OR) or "match all of these conditions" (using AND). Combining the two can give more power, but makes it very hard to explain to the average user how the search form works, so it's usually best to avoid it. If you're giving the user a "match any" or "match all" option, then you just need to flip between AND and OR based on that option.

When building a SQL "where" clause, I find it's usually best to start with something like this:

select * from table where 1 = 1

The 1 = 1 is always true, but is there to simplify the syntax. After that, I can just keep appending " and field = $value" without worrying about which is the first or last dynamic part. It simplifies things a lot.

If you're using OR instead of AND, change the "1 = 1" part to "1 = 2" (which is always false), then append " or field = $value" as needed.