php - mysql - 在 mysql 提示符下获取结果，而不是通过脚本？

Question

我正在使用 php 从我的数据库中获取一些记录，但它一直返回“未找到结果”。但是，当我将查询打印到屏幕上并将其复制并粘贴到 mysql 提示符时，我得到了 1 行。诚然，这是一个巨大的查询，但我不应该得到相同的结果吗？是什么原因导致这种情况发生？关于要检查什么的任何建议？

不知道是否有帮助，但这是查询:

$q = db_query("SELECT node.nid AS nid, gallery.field_attach_gallery_value AS gallery, 
node.type AS type, node.vid AS vid, ce.field_brochure_link_url AS ce_brochure_url, 
ce.field_brochure_link_title AS ce_brochure_title, 
ce.field_brochure_link_attributes AS ce_brochure_attributes, 
location.field_location_value AS location_field_location_value, 
ce.field_ongoing_value AS ce_field_ongoing_value, 
ce.field_poster_link_url AS ce_poster_url, 
ce.field_poster_link_title AS ce_poster_title, 
ce.field_poster_link_attributes AS ce_poster_attributes, 
ce.field_press_release_link_url AS ce_press_release_url, 
ce.field_press_release_link_title AS ce_press_release_title, 
ce.field_press_release_link_attributes AS ce_press_release_attributes, 
(DATE_FORMAT(STR_TO_DATE(ce.field_start_date_value,'%Y-%m-%dT%T'),'%M %d, %Y %h:%i %p')) AS start, 
(DATE_FORMAT(STR_TO_DATE(ce.field_start_date_value2,'%Y-%m-%dT%T'),'%M %d, %Y %h:%i %p')) AS end, 
ce.field_web_resources_url AS ce_web_resources_url, 
ce.field_web_resources_title AS ce_web_resources_title, 
ce.field_web_resources_attributes AS ce_web_resources_attributes, 
node_revisions.body AS body, 
node_revisions.format AS node_revisions_format, 
node.title AS title 
FROM node node 
LEFT JOIN content_field_attach_gallery gallery ON node.vid = gallery.vid 
LEFT JOIN content_type_exhibitions_and_programs ce ON node.vid = ce.vid 
LEFT JOIN content_field_location location ON node.vid = location.vid 
LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid 
WHERE (node.status <> 0) 
AND (node.type in ('exhibitions_and_programs')) 
AND '2010-01-19' BETWEEN (DATE_FORMAT(STR_TO_DATE(ce.field_start_date_value, '%Y-%m-%dT%T'), '%Y-%m-%d')) 
AND (DATE_FORMAT(STR_TO_DATE(ce.field_start_date_value2, '%Y-%m-%dT%T'), '%Y-%m-%d')) 
ORDER BY (DATE_FORMAT(STR_TO_DATE(ce.field_start_date_value, '%Y-%m-%dT%T'), '%Y-%m-%d')) DESC");

$num = FALSE;

while($r = db_fetch_array($q)) {
   $num = TRUE;
   $line = 'ok, found something!';
}

if($num == TRUE) {
  print $line;
} else {
  print 'No records found';
}

Answer 1

编辑:您评论说您正在使用 Drupal。

From Drupal's docs on db_query (强调我的):

Runs a basic query in the active database.

User-supplied arguments to the query should be passed in as separate parameters so that they can be properly escaped to avoid SQL injection attacks.

Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose in '') and %%.NOTE: using this syntax will cast NULL and FALSE values to decimal 0, and TRUE values to decimal 1.

所以我的猜测是 Drupal 正在替换您查询中的 %d。

旧答案:

db_query() 不是我所知道的原生 PHP/mySQL/mysqli 函数。你在使用数据库包装器吗？如果是这样，您需要告诉我们是哪一个，或者改用标准的 mysql_*。

如果所有设置都正确，应该会出现相同的结果。

您应该检查的内容:

• 在查询后添加一个echo mysql_error();，看是否静默失败
• 确保使用 mysql_select_db()
选择正确的数据库
• 确保您为 phpMyAdmin 和脚本请求使用完全相同的服务器和用户数据。