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?