Mysql queries help

Question

I have a mysql table as below..

The data in the table is as follows,

I want to get the total calorie by a user on a particular day (let's say Feb 20). I execute the below query to get the result.

SELECT sum( calorie ) AS sum
FROM test
WHERE extract(DAY FROM date ) = 20 AND extract(MONTH FROM date ) = 02
AND extract(year FROM date) = 2012
AND user_name = 'akshaynhegde';

The result I get is...1500 (as sum), which is exactly what I want. How do I execute this query in Drupal 7? I know how to execute it in Drupal 6, but I just upgraded to Drupal 7 and I am having many difficulty in executing queries.

//////////////////////MY ANSWER//////////////////////////////////

Here is a working Drupal & style code for the above query...

<?php
$query = db_select('test', 't')
->condition('t.user_name', 'akshaynhegde', '=');
$query->addExpression('SUM(calorie)', 'sum');
$query->where('EXTRACT(DAY FROM date) = :day', array('day' => '20');
$query->where('EXTRACT(MONTH FROM date) = :month', array('month' => '02');
$query->where('EXTRACT(year FROM date) = :year', array('year' => '2012');
$result = $query->execute()->fetchAll();
?>

Answer 1

You could use db_query() in your template file:

function theme_get_calories($user, $year, $month, $day) {
   $result = db_query("SELECT sum( calorie ) AS sum
                      FROM test
                      WHERE extract(DAY FROM date) = :day 
                      AND extract(MONTH FROM date) = :month
                      AND extract(year FROM date) = :year
                      AND user_name = :user",
                      array(
                        ':day' => $day,
                        ':month' => $month,
                        ':year' => $year,
                        ':user' => $user
                      )
   )->fetch();
   return $result;
}