Getting today's date from MySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
lazersam
Forum Contributor
Posts: 105
Joined: Sat Nov 15, 2003 4:07 am
Location: Hertfordshire, UK

Getting today's date from MySQL

Post by lazersam »

Hi

I have stored timestamps in a database table in a feild called hit_date and would like to extract only those rows for today!

Code: Select all

$the_date = time();
		//calculate today
		$today = date("Ymd", $the_date);
			
		#Get hits today
		$sql = "select * from hits where aff_id = \"Admin\" and hit_date >= \"$today\"  ";
		$qry = mysql_query($sql, $conn) or die ("DID NOT hits "  .mysql_error());
		$num_hits_today = mysql_num_rows($qry);
This returns everything probably because $today = 20050420 which is less than the timestamps.

Do you know what the mysql line should read?

Larry.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Code: Select all

#Get hits today        
$qry = mysql_query("SELECT * FROM `hits` WHERE `aff_id` = 'Admin' and `hit_date` >= '".time()."'", $conn) or die ("DID NOT hits "  .mysql_error());        
$num_hits_today = mysql_num_rows($qry);
date converts your unix timestamp into readable dates, while your stored date in mysql is still a unix timestamp.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I prefer

Code: Select all

SELECT * FROM `hits` WHERE `aff_id` = 'Admin' AND DATE_FORMAT(`hit_date`, '%Y-%m-%d') = CURDATE()
or

Code: Select all

SELECT * FROM `hits` WHERE `aff_id` = 'Admin' AND DATE_FORMAT(FROM_UNIXTIME(`hit_date`), '%Y-%m-%d') = CURDATE()
Post Reply