Why do I get a syntax error on this query?

Question

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?

Answer 1

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