dpmkif3097 2013-05-10 18:27
浏览 348
已采纳

虽然有数据,为什么mysql会返回null?

Check out this: image

My SQL query is:

SELECT @rownum := @rownum + 1 AS ID, SKU, SUM(Quantity) FROM orders, (SELECT @rownum := 0) r
WHERE ShipDate BETWEEN 01-05-2013 AND 11-05-2013 
GROUP BY SKU

And in my code, I am doing the following:

$sql = "SELECT @rownum := @rownum + 1 AS ID, SKU, SUM(Quantity) 
FROM orders, (SELECT @rownum := 0) r
WHERE ShipDate BETWEEN {$from_date } AND  {$to_date }
GROUP BY SKU";

I want to select data that falls between two values.

ShipDate is a text field and not a date field. I checked the table, and it holds the data, but MySQL returns null.

Why? What I am doing wrong?

How can I update my PHP code?

This code:

"SELECT @rownum := @rownum + 1 AS ID, SKU, SUM(Quantity) 
FROM orders, (SELECT @rownum := 0) r
WHERE STR_TO_DATE(ShipDate,'%d-%m-%Y') BETWEEN {$from_date } AND  {$to_date }
GROUP BY SKU";

is not working for me.

Please help me.

  • 写回答

2条回答 默认 最新

  • dreamwind1985 2013-05-10 18:30
    关注

    Use STR_TO_DATE() to convert the text into a date

    SELECT @rownum := @rownum + 1 AS ID, 
           SKU, 
           SUM(Quantity) 
    FROM orders, (SELECT @rownum := 0) r
    WHERE STR_TO_DATE(ShipDate,'%d-%m-%Y') BETWEEN '2013-05-01' AND '2013-05-11'
    GROUP BY SKU
    

    and add quotes around the date and rearrange the date to yyyy-mm-dd

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 MATLAB动图的问题
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名