By sifuhall on

Hello everyone.

I need to create a block that displays a list of upcoming events until the repeat issue gets resolved.

Anyway, here is what I have, but no data is displayed

<?php

$result = db_query('SELECT DATE_FORMAT(node_data_field_dateandtime.field_dateandtime_value, \'%m/%d/%Y %h:%i %p\') AS date,
	node.title AS node_title,
	node_revisions.body AS node_revisions_body
	FROM node node 
	LEFT JOIN content_field_dateandtime node_data_field_dateandtime ON node.vid = node_data_field_dateandtime.vid
	LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
	WHERE DATE_FORMAT(node_data_field_dateandtime.field_dateandtime_value, \'%Y-%m-%d\') >= DATE_FORMAT(now(), \'%Y-%m-%d\')
	AND node.status <> 0 
	AND node.type in (\'masonic_event\')
	AND DATE_FORMAT(node_data_field_dateandtime.field_dateandtime_value, \'%Y-%m-%d\') <= DATE_FORMAT(adddate(now(), INTERVAL 45 DAY), \'%Y-%m-%d\')
	ORDER BY date ASC');

foreach ($result as $record) {print "in";
  print $record["date"];
  print $record['node_title'];
  print '<br />';
}
?>

If I execute the SQL statement in phpmyadmin it displays the correct records.

So what am I doing wrong?

⋅ Categories: Drupal 6.x

Comments

nevets’s picture

nevets commented

I suspect you need to "double up" all those percent signs so for example %d would be %%d (db_query uses % for argument replacement).

And a suggestion, for readability I would place the whole string in double quotes so you do not need to escape the inner single quotes.

benjaminlhaas’s picture

benjaminlhaas commented

Thank you!

This fixed an annoying problem I was having with date comparisons. The '%d' for the MySQL date_format was getting plucked out by the placeholder replacement in db_query.

cog.rusty’s picture

cog.rusty commented

Instead of

foreach ($result as $record)

try

while ($record = db_fetch_array($result))

because $result is not an array of arrays, it is a query result resource and needs processing.

sifuhall’s picture

sifuhall commented

Thanks nevets and cog.resuty.

That fixed it!