Hi all,
Hopefully someone can help me with my problem - I am at my wits end here. This may be long winded but I am trying to explain my problem a completely as I can.
So I have a mySQL database with 4 fields I need to be able to search. Those fields are:
artist
showdate
city
state
I have created an html form for searching that refers to a php page.
So here is the problem. The initial query I set up went like this:
$result = mysql_query("SELECT * FROM collection WHERE artist='".$POST['artist']."'
AND showdate='".$POST['showdate']."'
AND city='".$POST['city']."'
AND state='".$POST['state']."'",$db);
Well, under this query, I had to put an item into each filed of the from to get a result - So if I just put in something like:
Artist: Aerosmith
Date: 2004-05-05
City:
State:
with the City and state fields blank, I would get no results returned, even i I had an entry that fit those requirements.
So I changed the query, switching the "AND"s to "OR"'s. It now looks like this:
$result = mysql_query("SELECT * FROM collection WHERE artist='".$POST['artist']."'
OR showdate='".$POST['showdate']."'
OR city='".$POST['city']."'
OR state='".$POST['state']."'",$db);
The problem with this is that if I did the same search as above, I get every record with Aerosmith and every record with the date 2004-05-05, so it isn't helping me limit my results.
It seems to me that I am closer to where I want to be by using the "AND" query. Is there a way that I can set up the query so that it ignores fields that are left blank.
Or, does anyone have a suggestion on another way to accomplish this.
Thanks for any help you could give me.
Nick
