0

I need some help with this query:

$result = db_query("SELECT
  UNIX_TIMESTAMP(FROM_UNIXTIME(s.timestamp, '%Y%m%d')) AS date,
  s.nid, COUNT(*) AS all_count,
  COUNT(DISTINCT s.ip_address) AS unique_count
FROM {simpleads_impressions} s
WHERE FROM_UNIXTIME(s.timestamp, '%Y-%m-%d') BETWEEN CURDATE() - INTERVAL 1 DAY AND CURDATE() - INTERVAL 1 SECOND
GROUP BY date, s.nid");

The main issue is the WHERE clause, and the count/group issue.

$query = \Drupal::database()
    ->select('simpleads_impressions', 's')
    ->fields('s', array('nid'));
$query->addExpression("UNIX_TIMESTAMP(FROM_UNIXTIME(timestamp, '%Y%m%d'))", 'date');
$query->addExpression('COUNT(*)', 'all_count');
$query->addExpression('COUNT(DISTINCT ip_address', 'unique_count');
$query->where("FROM_UNIXTIME(timestamp, '%Y-%m-%d') BETWEEN CURDATE() - INTERVAL 1 DAY AND CURDATE() - INTERVAL 1 SECOND");
$query->groupBy('date');
$query->groupBy('nid');
$result = $query->execute()->fetchObject();

This is the error message I get:

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS unique_count FROM simpleads_impressions s WHERE (FROM_UNIXTIME(timestamp, '' at line 1: SELECT s.nid AS nid, FROM_UNIXTIME(timestamp, '%Y-%m-%d') AS utime, DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS startdate, DATE_SUB(CURDATE(), INTERVAL 1 SECOND) AS enddate, UNIX_TIMESTAMP(FROM_UNIXTIME(timestamp, '%Y%m%d')) AS testdate, COUNT(*) AS all_count, COUNT(DISTINCT ip_address AS unique_count FROM {simpleads_impressions} s WHERE (FROM_UNIXTIME(timestamp, '%Y-%m-%d') BETWEEN CURDATE() - INTERVAL 1 DAY AND CURDATE() - INTERVAL 1 SECOND) GROUP BY date, nid; Array ( ) in simpleads_cron() (line 23 of D:\www\nla-refactor\modules\custom\simpleads\simpleads.module).

What code should I use to fix the error?

  • This is going to be the usual un-escaped percents problem - use '%%Y-%%m-%%d' etc and should start working – Clive Jul 4 '16 at 12:04
  • thanks for the suggestion @Clive, the issue was not grouping the 'timestamp' field, this was addressed here: link – Postovan Dumitru Jul 4 '16 at 12:36
0

I changed the code to the following one, and I don't get the error anymore.

$query = \Drupal::database()
    ->select('simpleads_impressions', 's')
    ->fields('s', array('nid'));
$query->addExpression("FROM_UNIXTIME(timestamp, '%Y-%m-%d')", 'utime');
$query->addExpression("UNIX_TIMESTAMP(FROM_UNIXTIME(timestamp, '%Y%m%d'))", 'date');
$query->addExpression('COUNT(*)', 'all_count');
$query->addExpression('COUNT(DISTINCT ip_address)', 'unique_count');
$query->where("FROM_UNIXTIME(timestamp, '%Y-%m-%d') BETWEEN CURDATE() - INTERVAL 1 DAY AND CURDATE() - INTERVAL 1 SECOND");
$query->groupBy('date');
$query->groupBy('nid');
$query->groupBy('timestamp');
$result = $query->execute()->fetchObject();

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.