What code is equivalent to this SQL query?

Question

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?

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

Answer 2

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