[SOLVED] Convert SQL query into Drupal 7 PHP rules

Convert SQL query into Drupal 7 PHP rules

SELECT address
  FROM user_address
 WHERE username = '$user->name'
 ORDER BY time DESC
 LIMIT 1

Here is the SQL query that I can understand. How is it possible to convert it into Drupal's 7 PHP? I'm trying to figure that out for a day, and I tried different approaches, but it seems that I am just bad in that.

Solution

You can use db_select :

$results = db_select('user_address', 'ua')
        ->fields('ua', array('address'))
        ->condition('ua.username', $user->name, '=')
        ->orderBy('ua.time', 'DESC')
        ->range(0,1)
        ->execute()
        ->fetchAll();
  var_dump($results);

Otherwise you can use db_query if you want to write entire SQL :

$results = db_query("SELECT address
  FROM user_address
 WHERE username = :username
 ORDER BY time DESC
 LIMIT 1 ", array(':username' => $user->name))->fetchAll();

var_dump($results);

Finally you can use db_query_range :

$page = 0;
$limit = 1
$results = db_query_range("SELECT address
      FROM user_address
     WHERE username = :username
     ORDER BY time DESC", 
    $page * $limit, 
   $limit,   
   array(':username' => $user->name))
   ->fetchAll();

    var_dump($results);