Solved

Drupal db_select with BETWEEN not working

Posted on 2011-10-01
3
2,072 Views
Last Modified: 2012-05-12
I am trying to build a list of nodes that have a publication date between two days ago and now.  I tried this:

  $query = db_select('node', 'n');
  $query->join('field_data_field_pubdate', 'f', 'n.nid = f.entity_id'); //JOIN node with pubdate
  $query->fields('n', array('nid', 'created', 'title'));
  $query->addField('f', 'field_pubdate_value', 'pubdate');
  $query->condition('n.type', variable_get('ain_sitemap_node_types', array_keys(node_type_get_names())));
  $query->condition('n.status', NODE_PUBLISHED);
  $query->condition('f.field_pubdate_value', array(REQUEST_TIME - 172800, REQUEST_TIME), 'BETWEEN');
  $query->addTag('node_access'); 
  $query->orderBy('f.field_pubdate_value', 'DESC'); 
  $query->range(0, 1000); 
  $nodes = $query->execute()->fetchAll(); 

Open in new window


But it's not working - now, it's just the one row with the BETWEEN clause (Line 7) - if I replace with this:

$query->condition('f.field_pubdate_value', REQUEST_TIME - 172800, '>=');

Open in new window


It works fine.  But this returns nodes with a pubdate value greater than right now, which I don't want.
0
Comment
Question by:michaelgiaimo
  • 2
3 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36898162
With time values, I would do two anyway. So:

$query->condition('f.field_pubdate_value', REQUEST_TIME - 172800, '>=');
$query->condition('f.field_pubdate_value', REQUEST_TIME, '<=');

Open in new window

0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36898174
By the way, your syntax appears correct per documentation: http://drupal.org/node/310086
Not sure why it did not work. Might be something with how it is handling the conversion of the date/time when it hits MySQL. You may need to format those, so they pass in as YYYY-MM-DD formatted strings.
0
 

Author Comment

by:michaelgiaimo
ID: 36898222
Yeah, that was it - field_pubdate_value was a mysql date field, while REQUEST_TIME returns the Unix time.  Good catch.  Thanks!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article, we will look at our content management system as a whole, to get an idea of how we will start to manage these content items.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question