I created a simple view filtered by a specific day( I will switch this out for a variable when I use this on the website). The field is a CCK date field. The view works great. The SQL it generates is as follows: It just prints a list of titles from published nodes on a specific date (cck date field, not node date)

SELECT node.nid AS nid,
node.title AS node_title
FROM node node
LEFT JOIN content_field_event_date_time node_data_field_event_date_time ON node.vid = node_data_field_event_date_time.vid
WHERE (node.status = 1) AND(DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node_data_field_event_date_time.field_event_date_time_value), SEC_TO_TIME(-25200)), '%Y-%m-%%d') = '2012-08-21')

I took this query and pasted it into the Query box in Phpmyadmin and it returns 0 rows. If I remove this part:

AND(DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node_data_field_event_date_time.field_event_date_time_    value), SEC_TO_TIME(-25200)), '%Y-%m-%%d') = '2012-08-21')

and it returns all the rows excluding the date filtering (which is what I really need).

Eventually I want to do a count operation and use php to have this printed in a custom view field (or other places) to show the total events on a specific day.

Is there something specific I need to do with the formatting to make this query work in Phpmyadmin like it does in views? Or is something else wrong?

| improve this question | | | | |
  • I should add I make sure there was no whitespace before or after any of the lines when I ran this query in Phpmyadmin – user1073319 Aug 21 '12 at 23:18
  • 2
    a quick look suggests you have two %'s before d where you need only one in '%Y-%m-%%d' – Jimajamma Aug 22 '12 at 2:08
  • @Jimajamma - THANKS That's what I missed, the %%d was escaped for use with Drupal's db_query, I removed the extra % and it works great in phpmyadmin – user1073319 Aug 22 '12 at 10:49
  • been there, done that, got the t shirt, glad I could help out :) – Jimajamma Aug 22 '12 at 13:58
  • @user1073319 - Dumb question - why do you need only need to escape the %d argument? Why not %m or %Y? I asked because I had this exact problem, but in reverse when using the db_query. I ended up using %e for testing, but you now solved my problem. – Rider_X Aug 22 '12 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.