How to fetch data from database in mail template

Question

I'm desperatly trying to get data from the database using a custom module (which is based on ubercart funds) to send a costomized email.

Here is how it makes email :

drupal_mail('ubercart_funds', 'escrow_cancel_from', user_load($transaction['uid'])->mail, language_default(), array('transaction' => $transaction['transaction_id']));

Implementing the mail template :

function ubercart_funds_theme() {
  return array(
'escrow_cancel_from' => array(
          'variables' => array('transaction' => NULL),
          'template' => 'theme/emails/ubercart-escrow-cancel-from',
      ),
   )
}

Sending the email :

function ubercart_funds_mail($key, &$message, $params) {

  $message['headers']['MIME-Version'] = '1.0';
  $message['headers']['Content-Type'] = 'text/html; charset=UTF-8; format=flowed';
  $message['headers']['Content-Transfer-Encoding'] = '8Bit';

  if ($key == 'escrow_cancel_from') {
    $message['subject'] = token_replace('Escrow Payment at [site:name] was Cancelled');
    $message['body'][] = theme('escrow_cancel_from', array('transaction' => $params['transaction']));
  }
}

}

And in the template (ubercart-escrow-cancel-from.tpl) :

$transaction = db_query("SELECT * FROM {ubercart_funds_transactions} WHERE transaction_id=" . $variables['transaction']);

So actually I should have all the data from ubercart_funds_transactions which store the amount for example. But impossible to get any values. Moreover I would like to have the user name whereas I just have the user uid in ubercart_funds_transactions. So I tried to load it with user_load() but things seems to be protected ... Here is what I get if I do a print_r($transaction); :

DatabaseStatementBase Object ( [dbh] => DatabaseConnection_mysql Object ( [needsCleanup:protected] => [target:protected] => default [key:protected] => default [logger:protected] => [transactionLayers:protected] => Array ( ) [driverClasses:protected] => Array ( [SelectQuery] => SelectQuery [DatabaseSchema] => DatabaseSchema_mysql [UpdateQuery] => UpdateQuery ) [statementClass:protected] => DatabaseStatementBase [transactionSupport:protected] => 1 [transactionalDDLSupport:protected] => [temporaryNameIndex:protected] => 0 [connectionOptions:protected] => Array ( [database] => **** [username] => **** [password] => ****, [host] => localhost [port] => [driver] => mysql [prefix] => Array ( [default] => ) ) [schema:protected] => DatabaseSchema_mysql Object ( [connection:protected] => DatabaseConnection_mysql Object *RECURSION* [placeholder:protected] => 0 [defaultSchema:protected] => public [uniqueIdentifier:protected] => 5989e447343638.05601838 ) [prefixes:protected] => Array ( [default] => ) [prefixSearch:protected] => Array ( [0] => { [1] => } ) [prefixReplace:protected] => Array ( [0] => [1] => ) [escapedNames:protected] => Array ( [cache_bootstrap] => cache_bootstrap [registry] => registry [name] => name [filename] => filename [cache] => cache [base.uid] => base.uid [base] => base [uid] => uid [pass] => pass [mail] => mail [theme] => theme [signature] => signature [signature_format] => signature_format [created] => created [access] => access [login] => login [status] => status [timezone] => timezone [language] => language [picture] => picture [init] => init [data] => data [users] => users [base.fid] => base.fid [fid] => fid [uri] => uri [filemime] => filemime [filesize] => filesize [timestamp] => timestamp [file_managed] => file_managed [cache_field] => cache_field [m.entity_type] => m.entity_type [m.entity_id] => m.entity_id [m] => m [entity_id] => entity_id [revision_id] => revision_id [metatag] => metatag [cache_rules] => cache_rules [cache_form] => cache_form [ubercart_funds_user_funds] => ubercart_funds_user_funds [transaction_id] => transaction_id [ubercart_funds_transactions] => ubercart_funds_transactions [cache_token] => cache_token ) [escapedAliases:protected] => Array ( [name] => name [filename] => filename [uid] => uid [pass] => pass [mail] => mail [theme] => theme [signature] => signature [signature_format] => signature_format [created] => created [access] => access [login] => login [status] => status [timezone] => timezone [language] => language [picture] => picture [init] => init [data] => data [fid] => fid [uri] => uri [filemime] => filemime [filesize] => filesize [timestamp] => timestamp [entity_id] => entity_id [revision_id] => revision_id ) ) [queryString] => SELECT * FROM ubercart_funds_transactions WHERE transaction_id=39 )

How can I get the amount and user name ? What am I doing wrong ?

Answer 1

You're not getting the transaction data because you haven't executed the statement:

$transaction = db_query(...)->fetchObject();

A couple of other notes:

1. Don't run queries in the template, use a preprocess function to set up the vars and pass them in.
2. Don't concatenate parameters into an SQL string, you'll leave yourself wide open to injection attacks.

All-in-all, something like:

function mymodule_preprocess_escrow_cancel_from(&$vars) {
    $sql = 'SELECT * FROM {ubercart_funds_transactions} WHERE transaction_id = :id';
    $args = [':id' => $vars['transaction']];
    $vars['transaction'] = db_query($sql, $args)->fetchObject();

    $vars['user_name'] = $GLOBALS['user']->name;
}

That will convert the $transaction variable in your template into the object loaded from the DB, and give you the user name in $user_name too.

You'll need to clear cache after adding the preprocess function for the registry to pick it up.