按日期排序返回错误值 [英] Order by date returns incorrect values

问题描述

我有这个查询:

$sql = "SELECT   likes, date
        FROM     statistics_pages
        WHERE    idnum = '".$_COOKIE['id']."'
          AND    page_name = '".$row_fpages['page_name']."'
        ORDER BY date DESC
        LIMIT 7";

它返回完全不正确的数字(likes)就像给任何值+500k额外，我不知道为什么会发生这种情况，因为当我删除ORDER BY时值是准确的(但排序不正确).

And it returns totally incorrect numbers (likes) it's like giving any value +500k extra and i have no idea why is that happening because when i remove ORDER BY the values are exact (but not ordered correctly).

编辑表结构:

  `id` int(5) NOT NULL AUTO_INCREMENT,
  `idnum` int(5) NOT NULL,
  `page_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `page_id` varchar(12) COLLATE utf8_unicode_ci NOT NULL,
  `likes` int(12) NOT NULL,
  `date` date NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=243 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

有什么建议吗?

推荐答案

SQL 注入

如上所述，您的查询存在 SQL 注入问题.

As explained your query has SQL-injection issues.

将代码重写为:

$idnum = mysql_real_escape_string($_COOKIE['id']);
$pagename = mysql_real_escape_string($row_fpages['page_name']);
$sql = "SELECT   likes, date
        FROM     statistics_pages
        WHERE    idnum = '$idnum'
          AND    page_name = '$pagename'
        ORDER BY date DESC
        LIMIT 7";

对于整数，您也可以使用 intval()，但我更喜欢使用一个函数来进行所有转义.

For integers you can also use intval(), but I prefer one function for all my escaping.

除此之外，查询没有任何问题.date 一词不是 mysql 保留字，不需要反引号.
请参阅此处的保留字列表:http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

Other than that there is nothing wrong with the query. The word date is not a mysql reserved word and does not need backticks.
See the list of reserved words here: http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

为什么您的查询不起作用
问题很可能是您表中的虚假数据，请删除 limit 7 并研究输出.
查询本身是 100% 正确的.

Why your query is not working
The problem is most likely bogus data in your table, remove the limit 7 and study the output.
The query itself is 100% correct.

对您的表的评论

  `id` int(5) NOT NULL AUTO_INCREMENT,   
  `idnum` int(5) NOT NULL,
  `page_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `page_id` varchar(12) COLLATE utf8_unicode_ci NOT NULL,
  `likes` int(12) NOT NULL,
  `date` date NOT NULL DEFAULT '0000-00-00',   <<-- not recommended use `null` 
                                               <<-- for `no date` 
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)  <<-- PK's are always unique, you can drop this.
) ENGINE=MyISAM AUTO_INCREMENT=243 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

int(5) 和 int(12) 实际上是同一个 32 位整数.只是 int(5) 只会显示 5 位数字.

int(5) and int(12) is really the same 32-bit integer. It's just that int(5) will only display 5 digits.

这篇关于按日期排序返回错误值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！