Welcome Guest, Not a member yet? Register   Sign In
Trying to count all records from database
#1

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
Reply
#2

(This post was last modified: 09-13-2017, 01:40 PM by InsiteFX.)

PHP Code:
$num count($query->result()); 
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#3

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.
Reply
#4

$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
Reply
#5

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.
Reply
#6

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/d...milar-data
Reply
#7

(This post was last modified: 09-14-2017, 08:50 AM by InsiteFX.)

Group_by ahould always be the last statement.

In his case he mat need to use COUNT DISTINCT.
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply




Theme © iAndrew 2016 - Forum software by © MyBB