4

I have a mysql table as below..

screenshot

The data in the table is as follows,

screenshot

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();
?>
7

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;
}
  • I have got the drupal 7 style code for the above query. Since I cant answer the question, am editing the question with my answer. – akshaynhegde Feb 20 '12 at 15:20
  • @akshaynhegde Dynamic queries are not more Drupal 7 style than static queries. Dynamic select queries should be used for queries you want to be altered from other modules. If you look at Drupal code, most of the queries are static, as the one reported in this answer. – kiamlaluno Feb 20 '12 at 19:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.