I've ordered a project from a developer, and I'm trying to fix the errors I find along the way.
One is a MySQL query with the DATE_FORMAT() function, that makes the query not return anything.
SELECT * FROM food_cart
WHERE cart_userId='3'
AND cart_itemId='8'
AND date_format(cart_date,'%Y-%m-%d %H')='".date("Y-m-d")."'
The code seems to be correctly formatted, as it does not return an error when executed in phpMyAdmin. It does however, not return anything.
The table looks like this:
I think it is supposed to try to look at just the date (i.e. 2011-10-07) and not the exact time, as it is not relevant for the query. I'm just a beginner at MySQL and I'm not quite sure what I'm doing here.
Any help appreciated!
Mike.
As requested, here's my version of Johan's answer:
$date = date( "Y-m-d", $timestamp );
$sql = "SELECT * FROM food_cart
WHERE cart_userId = '3'
AND cart_itemId = '8'
AND cart_date >= '$date'
AND cart_date < '$date' + INTERVAL 1 DAY";
This query will
cart_date
,cart_date
equals $date
.you can use function date() instead. http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date
MySQL does not need date translation for its own fields.
Just make sure you input your parameter in yyyy-mm-dd
format.
$date = date('Y-m-d',$adate);
$query = "SELECT * FROM food_cart
WHERE cart_userId = '3'
AND cart_itemId = '8'
AND cart_date BETWEEN '$date' AND DATE_ADD('$date', interval 1 day)
-- and car_date <> DATE_ADD('$date', interval 1 day)
If you are walking this query day by day uncomment the last part to eliminate the 1 second overlap that is in the all-inclusive BETWEEN. This will prevent spurcious duplicates.
When date("Y-m-d")
is passed to the query, it assumes 00:00:00 for the hours, minutes, seconds. That's why it's not returning anything. you might need to do a range search to get from midnight to 11:59:59 of that same date.
Change this line:
date_format(cart_date,'%Y-%m-%d %H')='".date("Y-m-d")."'
This will produce something like the following (that will never be correct):
'2011-10-07 16' = '2011-10-07'
Instead do
date( cart_date )='".date("Y-m-d")."'
If you're comparing dates, make sure that both sides of the equation have the same level of precision.
Edit: here's the full cleaned-up PHP code:
$date = date( 'Y-m-d' );
$sql = 'SELECT * '
. 'FROM food_cart '
. 'WHERE cart_userId = 3 '
. ' AND cart_itemId = 8 '
. " AND date( cart_date ) = '$date'";
It should generate a statement like SELECT ... FROM ... WHERE ... AND date( cart_date ) = '2011-07-10'
which will handle the date-filtering properly.