Last updated July 16, 2012. Created on March 24, 2011.
Edited by jn2, jonawebb, tiagojsag, ressa. Log in to edit this page.

Main topic described: Database API
Main function described: db_select()

Next we will create a custom function to retrieve the most recent posts. When a node is first created, the time of creation is stored in the database. We'll use this database field to find our data. We could include this code in the hook we will implement in the next tutorial page. Putting this section into a separate function keeps the code cleaner, easier to read and to maintain.

We will call the function current_posts_contents. We continue to follow the naming convention by beginning with the module short name. Then we use a descriptive word that is not a Drupal hook. The function begins with getting the time numbers. Here's the first part (without a closing brace because it's not the complete function):

<?php
/**
 * Custom content function.
 *
 * Set beginning and end dates, retrieve posts from database
 * saved in that time period.
 *
 * @return
 *   A result set of the targeted posts.
 */
function current_posts_contents(){
 
//Get today's date.
 
$today = getdate();
 
//Calculate the date a week ago.
 
$start_time = mktime(0, 0, 0,$today['mon'],($today['mday'] - 7), $today['year']);
 
//Get all posts from one week ago to the present.
 
$end_time = time();
?>

This code gets the current time, then calculates the time (in seconds since epoch start, see mktime for more information on time format) for midnight a week ago. These functions are straight PHP; you can look them up on the PHP website (php.net) for more details.

Next we use Drupal's Database API to retrieve our list of current nodes. This is the second part of the custom function:

<?php
 
//Use Database API to retrieve current posts.
 
$query = db_select('node', 'n')
    ->
fields('n', array('nid', 'title', 'created'))
    ->
condition('status', 1) //Published.
   
->condition('created', array($start_time, $end_time), 'BETWEEN')
    ->
orderBy('created', 'DESC') //Most recent first.
   
->execute();
  return
$query
}
?>

Built into Drupal is a very robust query builder, using a special object-oriented API. INSERT, UPDATE, and DELETE queries need this special care in order to behave consistently across all different databases. Although our SELECT query doesn't require this structure, this is a good opportunity to become familiar with it. You will see this structure used throughout Drupal.

  1. We build the query using the db_select method, which takes a table name ('node') and alias ('n') as arguments.
  2. The fields method uses the table assigned the alias 'n' to select the fields listed in the array in the second argument.
  3. The condition method takes three arguments. The first is the field, the second the value, the third the operator. If no operator is specified, as in 'status' above, = is assumed.
  4. The orderBy method sorts according to the field in the first argument, in the order specified by the second argument.
  5. The execute method compiles and runs the query and returns a result set/statement object.

Here's the complete function:

<?php
/**
 * Custom content function.
 *
 * Set beginning and end dates, retrieve posts from database
 * saved in that time period.
 *
 * @return
 *   A result set of the targeted posts.
 */
function current_posts_contents(){
 
//Get today's date.
 
$today = getdate();
 
//Calculate the date a week ago.
 
$start_time = mktime(0, 0, 0,$today['mon'],($today['mday'] - 7), $today['year']);
 
//Get all posts from one week ago to the present.
 
$end_time = time();

 
//Use Database API to retrieve current posts.
 
$query = db_select('node', 'n')
    ->
fields('n', array('nid', 'title', 'created'))
    ->
condition('status', 1) //Published.
   
->condition('created', array($start_time, $end_time), 'BETWEEN')
    ->
orderBy('created', 'DESC') //Most recent first.
   
->execute();
  return
$query
}
?>

(Remember not to include the closing ?> in your code.)

See also

Looking for support? Visit the Drupal.org forums, or join #drupal-support in IRC.

Comments

insatiabledev’s picture

How can we determine that a future hook will not have the function name we choose to describe a module custom function?

crihak’s picture

The db_select code didn't work for me unless I added a call to fetchAll():

<?php
$query
= db_select('node', 'n')
  ->
fields('n', array('nid',' title', 'created'))
  ->
condition('status', 1) // Published
 
->condition('created', array($start_time, $end_time), 'BETWEEN')
  ->
orderBy('created', 'DESC') // Most recent first
 
->execute()
  ->
fetchAll();
?>

Edit: I know the guidelines say I should just edit the page, but I am still only learning drupal, so I have no idea how specific this problem is to my database setup.

ss54’s picture

I think you should have waited till you have finished (Generating block content) tutorial item which comes after this one.

iyerdevil’s picture

I followed all the steps in same order ... i can see the current posts in blocks.... but this last code of data retreiving is not working .... please help
P.S. even the ->fetchAll(); is giving an error.

antonyanimator’s picture

<?php
 
//Use Database API to retrieve current posts.
 
$query = db_select('node', 'n')
    ->
fields('n', array('nid', 'title', 'created'))
    ->
condition('status', 1) //Published.
   
->condition('created', array($start_time, $end_time), 'BETWEEN')
    ->
orderBy('created', 'DESC') //Most recent first.
   
->execute(); {
    return
$query;
}
?>
tyrant’s picture

Isn't using SQL queries in Drupal a bad idea?

frankblaze’s picture

Please, i am not very close to form building in drupal 7. I love the code but please i need the form function to search and retrieve data from the database.
What i mean is that, the code above plus the form that was used in searching from the database. i am always having problem with search. And i need each result to be displayed in a grid format not a table form.
Example: When students in a particular class are searched, each student information is display in different grid not all in a single table.
I will appreciate if my question is treated.
Thanks all