Getting last run SQL from CodeIgniter's DB class

Here's a convenient way to obtain the last executed SQL query when using CodeIgniter's database class.

Code snippet for this example:

$this->db->select('status');
$this->db->from('tbl_user');
$this->db->where('user_id', $user_id);

$results = $this->db->get();

echo $this->db->last_query();

The last call to last_query() will output the actual SQL executed.

SQL JOINS using Drupal db_select

Here's how to use Drupal's db_select function to perform a SQL INNER JOIN like below:

SELECT n.*, u.name FROM node n 
INNER JOIN users u on n.uid = u.uid
WHERE u.id = 3

Drupal db_select version:

$q = db_select('node', 'n');
$q->join('users', 'u', 'u.uid = n.uid');

$q->fields('n');
$q->fields('u', array('name'));

$q->condition('u.uid', 3);
  
$results = $q->execute();

Output XLS file to servlet output stream using JExcelAPI

Here's a quick way (although not the best one) to stream an Excel file generated by JExcelAPI (http://jexcelapi.sourceforge.net/). I'm using the servlet output stream in this case.

HttpServletResponse response = getContext().getResponse();

ServletOutputStream sos = response.getOutputStream();

WritableWorkbook workbook = Workbook.createWorkbook(sos);
WritableSheet sheet = workbook.createSheet("Report", 0);
sheet.addCell(new Label(1, 1, "asdfasdfasdf"));

response.setContentType("application/vnd.ms-excel");
response.setHeader("Expires", "0");
response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
response.setHeader("Pragma", "public");
response.setHeader("Content-Disposition", "inline; filename=report-.xls");

workbook.write();
workbook.close();
sos.close();

Formatting Drupal's UNIX timestamp dates

Drupal stores date/time value as int columns in MySQL. Its value is UNIX timestamp based. You will not be able to determine the actual date/time by selecting from the table.

Here's a convenient way to convert the date/time columns directly from SQL:

SELECT cid, data, FROM_UNIXTIME(created) FROM main_cache

You can also use this in the WHERE clause like below:

SELECT COUNT( * ) 
FROM  main_commerce_product 
WHERE FROM_UNIXTIME( created ) 
BETWEEN  '2013-07-17 00:00:00'
AND  '2013-07-17 23:59:59'

Here's the result:

Programmatically parse webform submissions

Here's how to retrieve a webform submission and process its submission values programmatically. We'll need two IDs. First is the webform node id (nid) and the second is the submission id (sid). You can retrieve these IDs from viewing the webform results listing. The URL is something like below:

http://localhost/dt/node/124/submission/18

In this case, 124 is the node id while 18 is the submission id.

Now, on to the code.

$nid = 124;
$sid = 18;

$webform = node_load($nid);
$sub = webform_get_submission($nid, $sid);

// Capture the component IDs for easier array mapping.
foreach ($webform->components as $cid => $cfield) {
  $components[$cfield['form_key']] = $cid;
}

// Retrieve user info
$user_info = new stdClass();
$user_info->username = $sub->data[$components['username']][0];
$user_info->email = $sub->data[$components['e_mail_address']][0];

// And the rest of your processing...

You can use this method to easily reference the fields stored in the row by field name instead of index number.