2

I have a very simple db_query. I want to look for any node created between two timestamps.

This query works fine in MySQL shell

SELECT nid as id,created as start,title FROM node WHERE created BETWEEN '1403564400' AND '1403650800';

This works fine in Drupal

$data = db_query("SELECT nid as id,created as start,title FROM node")->fetchAll();
echo json_encode($data);

As soon as I add in the BETWEEN conditions below I get an Internal Server Error.

$data = db_query("SELECT nid as id,created as start,title FROM node WHERE created BETWEEN '1403564400' AND '1403650800'")->fetchAll();

I am unsure what I am doing wrong here, perhaps someone can point me in the right direction please!

6

You can try using db_select instead wich is a better way to create query with Drupal (assuming you are using Drupal 7).

$start_time = '1403564400';
$end_time = '1403650800';
$query = db_select('node', 'n')
  ->fields('n', array('nid', 'created', 'title'))
  ->condition('created', array($start_time, $end_time), 'BETWEEN');
$result = $query->execute();
$record = $result->fetchAll();
  • Thank you - I have been told that db_select is slower and for critical performance related tasks, db_query is the better option. I also only wnated to use DRUPAL_BOOTSTRAP_DATABASE for this task as I need to retrieve this data quickly and did not want to do a full Drupal boostrap call. – Jeremy McMinn Jun 25 '14 at 15:29
  • My mistake, db_query was working, I just had some wrong code elsewhere. – Jeremy McMinn Jun 25 '14 at 15:40
  • Ok, but beware when using db_query to escape your vars. Glad your problem was solved :-D – pbonnefoi Jun 25 '14 at 15:50
  • Use placeholders instead of escaping your variables. These work in db_query(). Also, if you are querying against the {node} table you should just about always use a db_select() with ->addTag('node_access') to make sure you don't have a permission bypass problem. – mpdonadio Jun 25 '14 at 16:16

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.