MYSQL Query - how to not fetch a record if the date is earlier than today - HTML Forums

GameGodUK · 06-28-2009, 04:54 PM

how can i not fetch gigs that have a date earlier than today,
this is how far ive got
BUT it doesnt work :L
and i cant understand why :@

$date = date("o-m-d");

$gig_query = "SELECT users.*, users_gigs.* FROM users, users_gigs WHERE users.id=users_gigs.mid AND users_gigs.date < $date";

************users_gigs.date isnt a time stamp it is a date field with the date that a user has selected for the gig in the same format ads $date

GrillMonkey · 06-29-2009, 12:05 PM

I could be completely wrong here as I don't use php and mysql (I'm .net and mssql) but I believe you should be using now() instead of $date... ?

GameGodUK · 06-29-2009, 02:21 PM

Quote:

Originally Posted by GrillMonkey

I could be completely wrong here as I don't use php and mysql (I'm .net and mssql) but I believe you should be using now() instead of $date... ?

the $date is a variable set from my

date("o-m-d");

which gets the date now in format 2009-06-29 < same as is inputted into database,

cheers anyway

Horus_Kol · 06-29-2009, 09:57 PM

1. is 'date' a DATE type or a DATETIME/TIMESTAMP? this affects the test

DATE fields are just 2009-01-01
DATETIME/TIMESTAMP are 2009-01-01 01:01:01

2. you should use Y and not o to get the year - you will get some odd behaviour around the 1st of January if you use o

3. if you are putting a string into a database ( $date = date("Y-m-d") ) then you need to put quotes around it:

Code:

$gig_query = "SELECT users.*, users_gigs.* FROM users, users_gigs WHERE users.id=users_gigs.mid AND users_gigs.date < '$date' ";

4. finally - I don't think that you can do this comparison straight like that - simpler to use an integer:

PHP Code:


			

$date = date("Ymd"); // returns example: 20090630

$gig_query = "SELECT users.*, users_gigs.* FROM users, users_gigs WHERE users.id=users_gigs.mid AND users_gigs.date < $date";

that will work if date is a DATE field
if it is DATETIME or TIMESTAMP, then you will have to do this:

PHP Code:


			
$date = date("Ymd") . "000001"; // returns 1 second past midnight - first second of today

Vege · 07-14-2009, 06:04 PM

Quote:

SELECT users.*, users_gigs.* FROM users, users_gigs WHERE users.id=users_gigs.mid AND users_gigs.date < date(now())

This should work regardless if the date -column is varchar, date or datetime.