CODEWITHSUNDEEP
phpmysqldate
Ben Packard
Ben Packard

Reputation: 26506

Comparing a PHP date with MySQL Date type

A beginner's question. I need to find all the records in my database that are dated with yesterday's date. This column is a DATE type.

In php:

$yesterday =  date('Y-m-d', strtotime($Date. ' - 1 day'));

This is producing '2013-04-11' as expected. But when I attempt

$gamesQuery = mysqli_query($con,"SELECT * FROM scores WHERE home_team = $teamId AND game_date = $yesterday");

I am getting no matches. There is definitely a record in my database with game_date = 2013-04-11.

(I found many similar SO questions but nothing basic enough to help).

Upvotes: 0

Views: 289

Answers (5)

Aleko
Aleko

Reputation: 970

Use this (only sql):

SELECT * 
  FROM scores 
 WHERE home_team = $teamId 
   AND DATE(game_date) = DATE(DATE_SUB(NOW(), INTERVAL 1 DAY))

Upvotes: 2

Kevin Rave
Kevin Rave

Reputation: 14456

Try:

 $gamesQuery = mysqli_query($con,"SELECT * FROM scores WHERE home_team = $teamId AND DATE(game_date) = '{$yesterday}'");

I have been using this for lot of mysql date related queries.

Upvotes: 1

Tiit
Tiit

Reputation: 520

I would try to set the correct format as well: 

$yesterday =  date('Y-m-d', strtotime($Date. ' - 1 day'));
$gamesQuery = mysqli_query($con,"SELECT * FROM scores WHERE home_team = ".$teamId." AND date_format(game_date,'%Y-%m-%d') = ".$yesterday);

Upvotes: 0

jcaruso
jcaruso

Reputation: 2494

I think your problem is the difference in how dates are used and stored in php vs mysql. Try http://www.richardlord.net/blog/dates-in-php-and-mysql

This will not only give you the answer but teach you the answer ;)

Upvotes: 0

John Conde
John Conde

Reputation: 219864

If you game_date field is in datetime format it won't match because the values aren't identical. Use DATE() to extract the date first and then do your comparison.

SELECT * FROM scores WHERE home_team = $teamId AND DATE(game_date) = $yesterday

Upvotes: 0

Related Questions