CODEWITHSUNDEEP
phpmysqlcodeignitergroup-bycount
Olivier Salmon
Olivier Salmon

Reputation: 11

Codeigniter SQL query build

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

Any help more than welcome! Thank you

Upvotes: 1

Views: 75

Answers (3)

Olivier
Olivier

Reputation: 130

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

Thank you for your suggestions

Upvotes: 0

miradexxx
miradexxx

Reputation: 21

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

Upvotes: 2

DFriend
DFriend

Reputation: 8964

$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;

Upvotes: 2

Related Questions