CodeIgniter Forums
Trying to count all records from database - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forum-5.html)
+--- Forum: General Help (https://forum.codeigniter.com/forum-24.html)
+--- Thread: Trying to count all records from database (/thread-68918.html)



Trying to count all records from database - doomie22 - 09-13-2017

Hi,

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');
return 
$query->result(); 

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

Thanks,
Doomie


RE: Trying to count all records from database - InsiteFX - 09-13-2017

PHP Code:
$num count($query->result()); 



RE: Trying to count all records from database - doomie22 - 09-13-2017

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.


RE: Trying to count all records from database - lorenzoallopez91 - 09-13-2017

$query = $this->db->query('SELECT *, COUNT(pageid) as total_count
FROM analytics_page_views
WHERE date BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
GROUP BY pageid',array());
return $query->result();


You should add alias after count() since you use (*) to not conflict to other fields


RE: Trying to count all records from database - doomie22 - 09-14-2017

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');
 
   $this->db->from('analytics_page_views');
 
   $this->db->where('MONTH(date)'$pm);
 
   $this->db->where('YEAR(date)'$pmy);
 
   $this->db->group_by('pageid');
 
   $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.


RE: Trying to count all records from database - PaulD - 09-14-2017

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.

https://www.w3schools.com/sql/sql_groupby.asp

Also in the docs, group_by is near the bottom of this section:
https://www.codeigniter.com/user_guide/database/query_builder.html#looking-for-similar-data


RE: Trying to count all records from database - InsiteFX - 09-14-2017

Group_by ahould always be the last statement.

In his case he mat need to use COUNT DISTINCT.