What does this MySQL function output when written like this?

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 co开发者_JAVA百科rrectly 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.

---

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.

---

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

• make full use of an index on cart_date,
• make full use of the query cache, and
• return exactly those rows for which the date part of 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

---

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.