Attempt to convert SQL to CodeIgniter's query builder emits parse error: unexpected 'as'

Question

I'm having trouble to convert the below SQL query into CodeIgniter query builder:

$row = $db->prepare("SELECT DATE(message_timestamp) Date, COUNT(DISTINCT messageid) totalCount FROM ic_deliveryreceipts GROUP BY DATE(message_timestamp)");

This is the latest I've tried:

$this->db->select(DATE(message_timestamp) as 'Date', COUNT(DISTINCT messageid) as 'totalCount');
$this->db->group_by(DATE(message_timestamp));
$query = $this->db->get($this->ic_receipts);

but this give a parse error:

syntax error, unexpected 'as' (T_AS), expecting ')'

This is just a simple matter of not quoting the string parameters in your methods. — mickmackusa
– mickmackusa ♦, Commented Sep 13 at 13:16

DFriend · Accepted Answer · 2018-01-17 03:40:18Z

2

$row = $this->db
              ->query("SELECT DATE(message_timestamp) Date, 
                       COUNT(DISTINCT messageid) totalCount 
                       FROM ic_deliveryreceipts 
                       GROUP BY DATE(message_timestamp)")
              ->row();

$row is a stdClass object with two properties: Date, totalCount

echo $row->Date . " " . $row->totalCount;

answered Jan 17, 2018 at 3:40

DFriend

8,9741 gold badge16 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nick · Accepted Answer · 2018-01-17 13:22:47Z

2

I think you need quotes, and also put false on the second (optional) parameter in $this->db->select, as seen in codeigniter docs:

$this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names. This is useful if you need a compound select statement where automatic escaping of fields may break them.

Try writing like this:

$this->db->select("DATE(message_timestamp) as 'Date', 
                   COUNT(DISTINCT messageid) as 'totalCount'"
                  , FALSE);

$this->db->group_by("DATE(message_timestamp)");

$query = $this->db->get($this->ic_receipts);

edited Jan 17, 2018 at 13:22

Nick

1,2084 gold badges26 silver badges38 bronze badges

answered Jan 17, 2018 at 13:14

miradexxx

214 bronze badges

Comments

mickmackusa · Accepted Answer · 2025-09-13 12:10:46Z

0

My original coding attempt only struggled with wrapping values in quotes.

This is what I finally come up with:

$this->db->select('DATE(message_timestamp) Date, COUNT(DISTINCT messageid) totalCount');
$this->db->from('ic_deliveryreceipts');
$this->db->group_by('DATE(message_timestamp)');
$query = $this->db->get();

edited Sep 13 at 12:10

mickmackusa♦

49.2k13 gold badges98 silver badges165 bronze badges

answered Jan 18, 2018 at 8:39

Olivier

1302 gold badges3 silver badges12 bronze badges

Collectives™ on Stack Overflow

Attempt to convert SQL to CodeIgniter's query builder emits parse error: unexpected 'as'

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related