selecting values from table

ketanco

Hi
I have a drop down form with a few selections.

The Mysql query I am writing to bring up results is currently as follows:

$query=mysql_query("SELECT * FROM events WHERE category='$kat' AND startdate= '..............' AND state='$state' ORDER BY id DESC LIMIT 4");

My questions are:

1-When the user selects a category and state in the drop down menu, I can bring up those results. But if the user selects "Any" for the category (and for state), how do I bring up the results belonging to any category and/or any state?

2-Startdate column in the database contains the dates as yyyy-mm-dd...In the query above, how can I make it equal (how is the syntax) to a certain date based on $day $month $year variables set up earlier during the execution of php code?

3-General question about the type of queries above: Just trying to understand the efficiency...How does it search for all the criteria? With all those "AND" statemens above, does it look at the first criteria and then eliminates everything else and then look for the second criteria in the reamining results and then the third and so on?

djjjozsi

2:
you might read this site about the date abilities in mysql:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html

$day $month $year gives you a string,

$date="$year-$month-$day";

$sql="SELECT * FROM table WHERE startdate='$date' ORDER BY id DESC";

= sign could be > < != <= >=

its a little but slower method, but you can use the LIKE and % pairs.
and you can use with LIKE '$year-%'

coldwerturkey

1- you can build your query with variables, like so

$where = ($state != "any" || $category != "any") ? "WHERE": null;
$category_query = ($category != "any") ? "AND `category` = '{$kat}' " : null;
$state_query = ($state != "any") ? "AND `state` = '{$state}' " : null;

$query = mysql_query("SELECT * FROM `events` {$where} {$category_query}{$state_query } ORDER BY id DESC LIMIT 4");

so that if "any" isn't selected it builds to the query (while if it is "any" it doesn't add to the query)