Help with collating database information |
Hi,
I am at a stage where i have a load of information in a database that i want to put into a webpage for people to see. For example i have a table that has page names that people have visited but what i want to do is say collate all of the page views of say the home page for the month. But i am really not sure how i can tell codeigniter to do this as i dont want to hard code which pages to search (as pages may change name and be added/taken away). So what i am trying to do is say like this example Home - 200 views About us - 150 views Contact us - 100 views Etc.... Has anyone got any ideas on how to start please? Thanks, Doomie
I suspect this might be quite difficult to do with the CI query builder. However you can just write an SQL statement and implement that with the $this->db->query(). (https://www.codeigniter.com/user_guide/d...ml#queries)
If you are using mysql you would do a query on pages, and as a sub query count all the records in the view page table that match the page name. (You should really use a page_id as names can be changed and then the views will not be counted.) You can read about it here: http://www.mysqltutorial.org/mysql-subquery/ as an example introduction. I am sure there are loads but this was the first google result I found for you. Another way to do this far more simply is to have a column for page_views_count in the pages table, and every time you log a page view, increment the page count. Then showing page views for all pages would be very easy. Or have a separate table with one row per page, with page summary data in that, like last edited, created by, age, views, etc. Then your CI query would be a simple join to get all the pages and all summary data. Handy for things like most recently edited lists as well as most viewed, least viewed, newest etc. Hope that helps, Paul.
(01-14-2017, 11:05 PM)enlivenapp Wrote: @PaulD, Not sure how to take that... Why do you give me ideas?!?!? - meaning "I want code, you idiot". Why do you give me ideas?!?!? - meaning "I want answers, not ideas!". Why do you give me ideas?!?!? - meaning "It makes me have to rework my sites". Here is an idea, wouldn't it be great to have a language that you could write in plain english. Instead of: PHP Code: $user = $this->db->from('my_table')->where('user_id', $user_id)->get(); You could write PHP Code: Get the user_name from my_table where user_id is $user_id. Still looking forward to CI5 Mind Reader Interface. Paul.
(01-14-2017, 12:24 PM)doomie22 Wrote: Hi, Like @PaulD said an SQL statement would probably work best, depending on how you have this saved in database. eg. if its all in the one table.. recordID, pagename , datetime 1,homepage , 1000-01-01 00:00:00 2,homepage , 1000-01-01 00:00:00 3,about us , 1000-01-01 00:00:00 something like this should work.. Code: SELECT pagename, COUNT(*) |
Welcome Guest, Not a member yet? Register Sign In |