1

I have a SQL query that works just fine on its own.

SELECT timestamp FROM accesslog
WHERE uid = 1 
AND path = 'mypath' 
AND timestamp > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 4 WEEK));

I am trying to write the code to execute the equivalent query for Drupal 7. With the following code, I get all the values for the user.

$ts = 'UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL '.$interval.'))';
$query = db_select('accesslog', 'a');
$query->addField('a', 'uid');
$query->addField('a', 'path');
$query->addField('a', 'timestamp');
$query->condition('a.uid', $uid);
$query->condition('a.path', $path);
$query->condition('a.timestamp', $ts, '>');
$result = $query->execute();

This code throws an exception.

$result = db_query("
SELECT timestamp 
FROM accesslog
WHERE uid = :uid 
AND path = :path
AND timestamp > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL :interval))",
array(':uid'=>$uid,
      ':path'=>$path,
      ':interval'=>$interval))->fetch();    

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '))' at line 3: SELECT timestamp FROM accesslog WHERE uid = :uid AND path = :path AND timestamp > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL :interval)); Array ( [:uid] => 237 [:path] => meplus [:interval] => 4 WEEK ) in check_page_visited_in_time_interval()

I'm guessing the issue is with the timestamp > part, but I am not sure how to get that part done.

What code should I use to execute that query?

0

2 Answers 2

1

Instead of the above one try the following query,

$date = date_create();
date_modify($date, '-4 week');
$interval =  $date->format('U');

$result = db_query("
SELECT timestamp 
FROM accesslog
WHERE uid = :uid 
AND path = :path
AND timestamp > :interval",
array(':uid'=>$uid,
      ':path'=>$path,
      ':interval'=>$interval))->fetchAll();
0
0

For code that works with MySQL, PostgreSQL, and SQLite, I would do what the Statics module does in statistics_cron(): Instead of depending on the date and time functions supported from the database engine, I would calculate the timestamp on the PHP side. (I assume the intent is getting all the pages the user visited in the last 4 weeks.)

$query = db_select('accesslog', 'a');
$uid_alias = $query->addField('a', 'uid');
$path_alias = $query->addField('a', 'path');
$timestamp_alias = $query->addField('a', 'timestamp');
$result = $query->condition($uid_alias, $uid)
 ->condition($path_alias, $path)
 ->condition($timestamp_alias, REQUEST_TIME - 2419200, '>=') // 4 weeks are 2419200 seconds. 
 ->execute();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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