mysql searching by date

[hyster]

im trying to crete a page that will search mysql for entrys from ??? days previous.

 

ive not started on the php yet as i wanted to get the query done but im having a slight problem.

 

date is in dd/mm/yyyy format. if i run this query

SELECT * FROM `dsgi_serval` WHERE date >= '10/09/2010' 

 

it ONLY sorts by DD and not MM or YYYY, so anything greater than the 10th of the month shows regardless of what month.

 

when i get to the php ill either do a drop down box with a preset number or days or a text box where u enter the number of days or even a txt box u enter the actual day u want to search from. im undecided which would be best.

 

another problem i face with the php is how to tell the script to search from ? ?? when it says search from the past 3 days?

 

<?php

$date = date("d/m/y");    // todays date 04/12/2010
$_post= 3 days;           // search from 01/12/2010
$date - $_post = $search; // 01/12/2010;
$sql="SELECT * FROM $tbl_name" where date >= '$search';

echo the results


?>

 

i no the above is not proper php but i hope it saya what im trying to do.

 

Thanks

[PFMaBiSmAd]

The DATE data type (uses a YYYY-MM-DD format) exists so that you can store dates and compare dates efficiently (it uses the least amount of storage and that format allows dates to be compared/sorted.) Your first step will be to use a DATE data type to store your dates.

 

You can easily format your existing date into a YYYY-MM-DD format either in php or directly in your query using the mysql STR_TO_DATE() function and your can format a YYYY-MM-DD value back to your existing date format directly in a query using the mysql DATE_FORMAT() function.

 

Once you have your date stored using a DATE data type, you can use the couple dozen date/time functions directly in your query to accomplish what you are trying to do.