CodeIgniter Forums

Full Version: Trying to count all records from database
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

I am trying to grab the last 30 days of a table and using the pageid group them together but also add the groups together.  I have the following which does group them, but doesn't add them together.  I am not sure what I am missing.

PHP Code:
$query $this->db->query('SELECT *, COUNT(*)FROM analytics_page_views WHERE date BETWEEN NOW() - INTERVAL 30 DAY AND NOW() GROUP BY pageid');

Can anyone advise on how I can get the group to count them as well please?

PHP Code:
$num count($query->result()); 
Sorry that is not quite what I want.

What I am trying to do is to group together all the last 30 days with the same pageid and then with each of the groups add just them together. What you have given me is to add everything together into one row and then it breaks the page.
$query = $this->db->query('SELECT *, COUNT(pageid) as total_count
FROM analytics_page_views
GROUP BY pageid',array());
return $query->result();

You should add alias after count() since you use (*) to not conflict to other fields
I think I am almost there with this.

I currently have the following in the database:

page_hits  |  date            |    pageid
5                |  2017-09-14   |  testing
5                |  2017-09-01   |  testing

and on the page I am wanting it to have 

pageid   |  page_hits
testing     |  10

But instead I am getting:

pageid   |  page_hits
testing     |  2

Here is  how far I have got my code to:

PHP Code:
$date = new DateTime('FIRST DAY OF 0 MONTHS');
   $pm  $date->format('n'); 
   $pmy $date->format('Y'); 
   $this->db->select('*, COUNT(page_hits) as page_hits');
   $query $this->db->get();
   return $query->result();       

So i know that its using the page_hits as how many rows their is but not adding the values in page_hits together.
You have grouped it by pageid. Since both your pageid's are the same they are grouped as one row. There are two of them.

Also in the docs, group_by is near the bottom of this section:
Group_by ahould always be the last statement.

In his case he mat need to use COUNT DISTINCT.