CODEWITHSUNDEEP
phpmysql
Lalith Mohan
Lalith Mohan

Reputation: 3906

creating analytics chart with php

i'm developing a web application with php and mysql. i have videos in my website, users can upload their videos.

Now, i want to increase the interactivity by creating simple analytics chart for the video views.

The graph chart will be containing the views for every month. The problem is i don't know where to begin with.

Can anybody tell me the logic to store the video views in the database of every month and then view in form of a bar chart? ( Like google analytics says the number of visitors every month or every week)

Upvotes: 1

Views: 2168

Answers (3)

Ahatius
Ahatius

Reputation: 4875

When to count a view?

You'll have to define when you count a view. Probably best would be to analyze the time gaps in which a certain user visited a video, how long it played and probably some identificators like his User-Agent, to be able to differenciate between several users behind an IP adress (if the user isn't logged in).

Inserting the view

Once you've decided if you count a view, you'll have to insert it into the database. This depends on what database and table structure you use. Have a look at a PHP MySQL Tutorial, or if you think you can do more, have a look PDO, which can be more secure and useful.

In order to be able to analyze monthly statistics, you'll have to insert each view with a timestamp. At the end, you'll just have to select the values for the specified date (in your case, the last month).

This requires that you at least know how to setup your database tables, in which you insert your data.

Showing the views

Since you'll have an entry for every view in you database, you're best shot is to create an automatic job, which updates another table with the total of the views. This will reduce the load, and also prevent your users from instantly seeing the view counts (might be helpful if someone is trying to bump view count --> he can't immediatly see if he's successfull).

Creating graphs

First you'll have to select the values. Let's say your user is able, to specify the date-range of which he wants to see the views, you'll have to take this range, query your database with it, and then proceed with the result. How you select it, depends on how you want to display it. If you want to show every single day of the month, you'll have to group the views by day.

How you actually display the data is open to you. It could be something really simple, something with tables, or you could use something like JpGraph to create graphs as images. I personally use JpGraph for stuff like that. Needs some working into, but it's worth the effort.

I hope this gave you somehow an idea how to solve your problem. If you have more specific problems, post some code and what you're trying to do, because that solution doesn't exist.

Upvotes: 1

user1403947
user1403947

Reputation:

Google Charts will be perfect for what you are looking to do. I use them myself. Just output the stats with PHP json_encode.

Then use a cron job to fetch the current amount of veiws or other stats and store them in a data type text row.

https://developers.google.com/chart/

Upvotes: 1

JHS
JHS

Reputation: 7871

Have a table with columns video_id, count, month and year. The video_id, month and year combined as an Unique Key.

Whenever a video is viewed (in programming terms requested) try to insert the video_id, 1, month and year. Check whether you get a unique key violation. If you do then instead of inserting you update the count with count + 1.

To fetch the month and year you can use MYSQL Date functions. video_id would be sent from the front end and count can be easily incremented.

Once you have this table you can always get the count for a specific video for that month and year combined.

Upvotes: 0

Related Questions