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.

Parsing a Base64 encoded image string in PHP

Here's a quick way to parse a Base64 encoded image file to check if it's a proper image file. First, the PHP code:

$file_encoded = '[very LONG BASE64 STRING]';

$file_decoded = base64_decode($file_encoded);

$info = getimagesizefromstring($file_decoded);

print_r($size);

If the decoded data is a valid image, the getimagesizefromstring() function will return the following info if said string is a proper image:

Array
(
    [0] => 240
    [1] => 464
    [2] => 3
    [3] => width="240" height="464"
    [bits] => 8
    [mime] => image/png
)

Which makes it perfect for image validation. It returns FALSE if it's not able to parse the input as an image.

You'll need to have the GD image library installed to use this function.

SELECT COUNT(*) using Drupal db_select

Here's how to use Drupal's db_select function to perform a SQL equivalent of:

SELECT COUNT(uid) FROM users WHERE name = $username

JavaOne 2012 videos on YouTube

Here's list of videos from the recent JavaOne 2012 symposium:

http://www.theserverside.com/news/thread.tss?thread_id=72456

Have fun!