CODEWITHSUNDEEP
codeigniter
unplugngo
unplugngo

Reputation: 175

Codeigniter SELECT can't handle this query

I've tested the following SQL in phpMyAdmin and have confirmed that it works fine. However, when I try to use it in CodeIgniter as follows, I get error messages.

$this->db->order_by("vch_name", "asc");
$this->db->select('SELECT *, (SELECT COUNT(*) FROM tbl_contact WHERE fk_client_id = tbl_pro_client_id) AS count_contacts FROM tbl_pro_client');
    $query = $this->db->get('tbl_pro_client', $num, $offset);
    return $query;

Is this too complicated for a CI select, or is there a way around it? A more obvious answer of course is that I'm probably doing something incredibly stupid. And advice, pointers, etc greatly appreciated.

Upvotes: 1

Views: 50

Answers (2)

Girish
Girish

Reputation: 12117

You should read more about database query manipulation click here

limit parameters are looking wrong, CI provide limit() function, try this code And in select() function no need FROM table_name, and Use FALSE to skip (`)

$this->db->order_by("vch_name", "asc");
$this->db->select('SELECT *, (SELECT COUNT(*) FROM tbl_contact WHERE fk_client_id = tbl_pro_client_id) AS count_contacts', false);
$this->db->limit($num, $offset); 
$query = $this->db->get('tbl_pro_client');
return $query;

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

Only the actual fields you want to select should be given to the select() call (ie the SELECT keyword and FROM ... should not be there). Something like;

$this->db->order_by("vch_name", "asc");
$this->db->select('*, (SELECT COUNT(*) FROM tbl_contact WHERE fk_client_id=tbl_pro_client_id) AS count_contacts', false);
$query = $this->db->get('tbl_pro_client', $num, $offset);
return $query;

Upvotes: 1

Related Questions