• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Help with collating database information

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

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

#3
@PaulD,

Why do you give me ideas?!?!? lol
Reply

#4
(01-14-2017, 11:05 PM)enlivenapp Wrote: @PaulD,

Why do you give me ideas?!?!?   lol

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();
$page_data = array(
 
   'user_name' => $user['user_name'],
);
$this->load->view('home_page'$page_data); 

You could write

PHP Code:
Get the user_name from my_table where user_id is $user_id
Show that in the home_page view

Still looking forward to CI5 Mind Reader Interface.

Paul.
Reply

#5
@PaulD

I should have given credit to you and the OP.

It's like "I have a huge list of things to do... Oooo! I like this... I'll add it to my list."

I too am looking forward to CI5's MRI!
Reply

#6
(01-14-2017, 12:24 PM)doomie22 Wrote: 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

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(*)
FROM tablename
WHERE datetime BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
GROUP BY pagename
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.