Tell me more ×
Drupal Answers is a question and answer site for Drupal developers and administrators. It's 100% free, no registration required.
up vote 2 down vote favorite
1

How do I retrieve a DateTime column from my MySQL database using Drupal Database API + PHP. The DateTime format is in YYYY-MM-DD HH:MM:SS(2013-02-20 23:59:59). I want to retrieve all the rows in the database that match a specific date, and then store the hour inside an array.

For example, I want to grab all the data from January 1, 2013. I then only want to store hours 00:00 to 23:59 inside my array, not the entire date.

I do not want to use any modules to accomplish this.. because I need to code it inside my custom module.

Thanks for your help!

Current Code:

$query = db_select('Project', 'p');
$query->fields('p', array('submissions', 'date_time'));
$query->condition('p.user_id', $user->uid);
$results = $query->execute()->fetchAll();
foreach($results as $r) {
    $rows[] = array(strtotime(date("H:i:s", $r->date_time))*1000, $r->submissions);
}

I plan to display the data on my Flot line graph.

share|improve this question
add comment (requires an account with 50 reputation)

2 Answers

up vote 2 down vote accepted

If you want the benefits of a SelectQuery, but with the more fine-grained control over the SQL, you can always use the where() method to inject (sanitised) raw SQL into the WHERE clause. e.g

$query = db_select('Project', 'p');
$query->fields('p', array('submissions', 'date_time'));
$query->condition('p.user_id', $user->uid);

// Fetch only records where date_time is greater than 30 days ago
$query->where('date_time > DATE_ADD(NOW(), INTERVAL -30 DAY)');

$results = $query->execute()->fetchAll();
foreach($results as $r) {
  $rows[] = array(strtotime(date("Y-m-d", $r->date_time))*1000, $r->numberOfPackages);
}

I don't know of any 'structured' way to get those sort of advanced conditions into a SelectQuery object so resorting to plain ol' SQL is necessary sometimes.

share|improve this answer
I know this question is more PHP related but... after retrieving the rows with the matching date, do you know how to format it so that I only store the Hour:Minutes:Seconds in my array? – John Smith Feb 20 at 9:37
1  
date('H:i:s', $r->date_time) should do the trick - there's a list of all the available formats in the docs if you're interested – Clive♦ Feb 20 at 9:47
add comment (requires an account with 50 reputation)
up vote -1 down vote

The code below works for me for fetching a group, so you can quickly adapt it for your own purpose. 

$query = db_select('og')
    ->fields('og', array('etid'))
    ->condition('gid', $gid,'=');
$results = $query->execute();

$result = $results->fetchAssoc();
share|improve this answer
This doesn't address the question... – Clive♦ Feb 20 at 9:29
add comment (requires an account with 50 reputation)

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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