Select rows from database where date is less than today's date

rush989

Hey,

Im pretty new to PHP and still learning the basics.

I'm trying to do three things while selecting rows from a database according to their dates which are recorded as yyyy-mm-dd so that they can be arranged in chronological order.

Each entree has two dates in it, a start date and an end date. For one select query I need to select entrees where today's date is greater than the end date. And then I need to select entrees where today is greater than or equal to the start date and less than or equal to the end date. And last, select where start date is greater than today's date.

Im pretty clueless, only thing i know of to try is $query = "SELECT * FROM raffles WHERE end_date<'$date'"; where $date = date('Y-n-d');

Any help would be appreciated. Thanks everyone!

Adam

devinemke

Originally posted by rush989
For one select query I need to select entrees where today's date is greater than the end date.

SELECT * FROM raffles WHERE end_date < CURDATE()

Originally posted by rush989
And then I need to select entrees where today is greater than or equal to the start date and less than or equal to the end date.

SELECT * FROM raffles WHERE start_date <= CURDATE() AND end_date >= CURDATE()

Originally posted by rush989
And last, select where start date is greater than today's date.

SELECT * FROM raffles WHERE start_date > CURDATE()

Frederick

You can also use the time() function
Add 2 fields for referencing. During your INSERT INTO process, add the time to the fields.

fieldname: start_date
fieldname: end_date

$start_date = time();
$end_date = time();

Now, in your query, you just have to do the following:

$current_time = time();
$sql = mysql_query("SELECT * FROM tablename WHERE start_date < $current_time AND end_date < $current_time") or die (mysql_error());

Using the time function, you can setup going back a set number of days or minutes. The following will go back 30 days.
Calculation: second x minuts x hours x days
60 * 60 24 30 = 2592000

$current_time = time()-2592000;
$sql = mysql_query("SELECT * FROM tablename WHERE start_date < $current_time AND end_date < $current_time") or die (mysql_error());