mysql - get results at mysql prompt, but not through script?
I'm using php to fetch some records from my db and it keeps returning "No results found". But, when I print the query to the screen and copy and paste it at the mysql prompt, I get 1 row. Granted, it's a monster query, but shouldn't I get the same results? What causes that to happen? Any suggestions on what to check?
Don't know if it's helpful, but here's the query:
$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;
开发者_StackOverflow社区
while($r = db_fetch_array($q)) {
$num = TRUE;
$line = 'ok, found something!';
}
if($num == TRUE) {
print $line;
} else {
print 'No records found';
}
Edit: You comment that you are using Drupal.
From Drupal's docs on db_query (Emphasis mine):
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.
So my guess is that Drupal is replacing your %d
s in your query.
Old answer:
db_query()
is no native PHP/mySQL/mysqli function I know of. Are you using a database wrapper? If so, you need to tell us which one that is, or use the standard mysql_*
instead.
If all settings are correct, the same result should come up.
What you should check:
- Add a
echo mysql_error();
after the query to see whether it silently fails - Make sure you are selecting the correct database using
mysql_select_db()
- Make sure you are using the exact same server and user data for both the phpMyAdmin and the script request.
It seems your db_query()
and db_fetch_array()
are custom functions. You should really var_dump($q)
and replace your output code at the end entirely with var_dump(db_fetch_array($q));
to get some debug info on your problem.
精彩评论