+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Trying to count all records from database (/showthread.php?tid=68918)
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.