1

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

Pradeep
  • 9,667
  • 13
  • 27
  • 34
Abu Dawud
  • 11
  • 5

2 Answers2

1

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();
Simon K
  • 1,503
  • 1
  • 8
  • 10
  • oh yes i'm forget. because i want to limit query result to 20. $query = $this->db->get('my_table', 20); – Abu Dawud Jul 28 '18 at 16:18
  • so what the different between limit and $records, is not the same value (20) ? i want to know how many record exactly but want to limit the query result. – Abu Dawud Jul 28 '18 at 16:40
  • limit only returns a maximum of this number of results. `$records` in my example will return the actual number of rows that were returned - i.e - <= 20 – Simon K Jul 28 '18 at 16:41
  • yes i know about it. but i want to know how many records before limited based on sql statement and then get query result with desired limit. – Abu Dawud Jul 28 '18 at 16:52
  • You don't need to!!! Take the limit part off my query - this will then return all results. You can then use the `$records` variable to display the number of rows in your view if you need to. In short, not setting a limit will return all rows that match your query - you don't need to know the limit first! – Simon K Jul 28 '18 at 16:57
  • of course i can limit how many record that will be displayed with simple php script. but i just want to know ho to use `count_all_results` – Abu Dawud Jul 28 '18 at 17:09
  • Dude, you don't need to use it if it is causing you problems. The answer which I posted performs the same outcome and does not cause you any issues!!! – Simon K Jul 28 '18 at 17:13
  • nice idea dude. – Abu Dawud Jul 28 '18 at 17:16
  • All this effort (correct answer) and you decide to award to someone else with no upvote for me? What the hell? – Simon K Jul 28 '18 at 17:37
0

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/

Pradeep
  • 9,667
  • 13
  • 27
  • 34
  • can you give me a good reference about alias. is it a sql statement or codeigniter term ? and why i must to use alias – Abu Dawud Jul 28 '18 at 16:27
  • did it work for u? its mysql term, i have tested it and used it , just try then let me know its working for you or not, – Pradeep Jul 28 '18 at 17:00
  • I'm sure its working, but its not easy to implement because my `select` and `like` statement is an array that also used in other function. should i modify the string in array before passed to the `select` ? or you have other suggestion ? – Abu Dawud Jul 28 '18 at 17:00
  • in like clause u can pass an array as docs says, but in select clause you have to pass comma separated string as in my answer, if ur select is an array u can use implode method to convert it in comma separated values, and use `array_map` to add table alias to every element of an array – Pradeep Jul 28 '18 at 17:18
  • by the way if my answer helps you pls don't hesitate to check it as green and do upvote also – Pradeep Jul 28 '18 at 17:20