CODEWITHSUNDEEP
phpmysqlsqlsorting
vallentin
vallentin

Reputation: 26157

MySQL Visitor/Hit Counter Sorting Statistics

I'm building a Visitor & Hit counter for my website, where of course a visitor is a unique ip's per day and hits is just each time the page is requested!

I've already made the datebase and the System to insert and update a table where I keep the statistics!

What I am struggling with is, how to sort the date and then show it. Well I know how I want to show it, but I need it sorted correctly, and with the least amount of queries

First this is my table structure, and the current table name is "testing_statistics"

url  - varchar(256)
ip   - int(11)
date - datetime

I don't know if that's the 100% best way, but that's how I'm storing it currently, if any of you have a better idea feel free to tell me. The ip is converted to a long though PHP so it can be stored as an integer

Then I want all todays hits & visitors, all yesterdays hits & visitors, all current month's hits & visitors and last the total hits & visitors!

Here is an image of what I'm trying to get

I know how to make the layout with CSS, it's just the sorting/getting the data from the database.

This is how I currently get the total amount of hits of each url.

$query = "SELECT `url`, COUNT(*) as `hits` FROM `testing_statistics` GROUP BY `url`";

This is how I currently get the amount of hits today of each url.

$query = "SELECT `url`, COUNT(*) as `count2` FROM `testing_statistics` WHERE DATE(`date`) = CURDATE() GROUP BY `url`";

I don't really know how to get the rest, and how to get the total visitors of each url. So that's what I need some help with and how to puts them together so I don't create a query for each process (Though if not possible, I can live with creating a new query each time)

I'm really thankful if anybody can help me, even if it's just with some of the things! and the less queries, the better. (Though it really doesn't matter)

Upvotes: 2

Views: 2016

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

To get the total visitors, you can use count(distinct ip). The queries look like:

SELECT `url`, COUNT(*) as `hits`, count(distinct ip) as visitors
FROM `testing_statistics`
GROUP BY `url`

And:

SELECT `url`, COUNT(*) as `count2`, count(distinct ip) as visitors
FROM `testing_statistics`
WHERE DATE(`date`) = CURDATE()
GROUP BY `url`

You can actually do both in one query using conditional aggregation:

SELECT `url`, COUNT(*) as `hits`, count(distinct ip) as visitors,
       sum(case when DATE(`date`) = CURDATE() then 1 else 0 end) as TodayHits,
       count(distinct case when DATE(`date`) = CURDATE() then ip end) as TodayVisitors
FROM `testing_statistics`
GROUP BY `url`

Upvotes: 2

Related Questions