duancai1904 2011-10-07 15:06
浏览 300
已采纳

这样写的MySQL函数输出什么?

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:

enter image description here

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.

  • 写回答

5条回答 默认 最新

  • dongqiaogouk86049 2011-10-08 12:26
    关注

    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.
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(4条)

报告相同问题?

悬赏问题

  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)
  • ¥20 怎么在stm32门禁成品上增加查询记录功能