Codeigniter count_all_results then get return error number 1066

Question

I want to count all result by using $this->db->count_all_results() in my query then get the query result ($this->db->get) without reset any field value. i have followed the user guide on Limiting or Counting Results it's say

However, this method also resets any field values that you may have passed to select(). If you need to keep them, you can pass FALSE as the second parameter:

i have passed FALSE parameter to the function but i get Database Error:

Error Number: 1066 Not unique table/alias: 'my_table'

this is the code i have tried

$this->db->select('title', 'content', 'date');
$this->db->like('title', 'Post');
$this->db->order_by('title', 'DESC');

$records = $this->db->count_all_results('my_table', FALSE);
$query = $this->db->get('my_table', 20);

Thanks

Answer 1

Hope this will help you :

Make an alias of my_table in count_all_results like the below:

$this->db->select('p.title, p.content, p.date');
$this->db->like('p.title', 'title');
$this->db->order_by('p.date', 'DESC');

$data['count'] = $this->db->count_all_results('my_table p', FALSE);
$data['records'] = $this->db->get('my_table')->result();
print_r($data);

for more : http://www.mysqltutorial.org/mysql-alias/

Answer 2

Why not just count the rows from the query resultset like so:

$this->db->select('title', 'content', 'date');
$this->db->like('title', 'Post');
$this->db->order_by('title', 'DESC');

$query = $this->db->get('my_table');

$records = $query->num_rows();