/    Sign up×
Community /Pin to ProfileBookmark

Quering unique hits per date

Copy linkTweet thisAlerts:
Nov 30.2006

I hav ebeen following a tutial on stats pages and am interested in showing unique hits over the past 7 days for example today being thursday it would show:
Unique Hits
Today: 2
Wednesday: 18
Tuesday: 3
Monday: 1
Sunday: 54
Saterday: 5
Friday: 54

And tomorrow it would show
Unique Hits
Today: 8
Thursday: 76
Wednesday: 18
Tuesday: 3
Monday: 1
Sunday: 54
Saterday: 5

How can I query my database to echo the day and the unique amount of hits using ip?

My table structure is like so:
id browser ip recieved refer
1 Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv… 87.113.14.220 2006-11-28 None
2 Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv… 87.113.14.192 2006-11-23 None
3 Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv… 87.112.26.158 2006-11-24 None
4 Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv… 87.113.14.220 2006-11-25 None
5 Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1;… 87.113.14.220 2006-11-26 None
6 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1;… 87.113.14.220 2006-11-29 None
7 Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv… 87.113.14.220 2006-11-27 None
8 Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv… 87.113.14.220 2006-11-22 None
9 Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv… 87.113.14.220 2006-11-29 [url]http://shogans.com/stats/[/url]
10 Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv… 87.113.14.220 2006-11-29 None
11 Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv… 87.113.14.220 2006-11-29 [url]http://www.shogans.com/index.php?do=newestmembers[/url]
12 Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv… 87.113.14.220 2006-11-29 [url]http://www.shogans.com/index.php?do=gotown:1[/url]
13 Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv… 87.113.14.220 2006-11-29 [url]http://www.shogans.com/index.php[/url]
14 Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv… 87.113.14.220 2006-11-29 [url]http://www.shogans.com/index.php?do=fight[/url]
15 Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv… 87.113.14.220 2006-11-29 [url]http://www.shogans.com/index.php?do=victory[/url]

to post a comment
PHP

11 Comments(s) ↴

Copy linkTweet thisAlerts:
@scousesheriffNov 30.2006 — Try:
[CODE]select count(distinct IP) from TABLE group by recieved[/CODE]
Copy linkTweet thisAlerts:
@PezmcauthorNov 30.2006 — so then how do I turn the date row in my sql (recieved) into a date and out put as well this is my script:

[code=php]
echo "<B>Last Seven Daily Averages:</B><BR>";
$sql = "select count(distinct IP) as hits FROM $dbsql TABLE group by recieved";

$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
echo "$row[weekday] $row[hits]<BR>";
}
[/code]
Copy linkTweet thisAlerts:
@scousesheriffNov 30.2006 — [code=php]$sql = "select count(distinct IP) as hits, receieved FROM $dbsql TABLE group by recieved";[/code]

Then use the PHP functions [URL=http://uk.php.net/manual/en/function.substr.php]substr[/URL], [URL=http://uk.php.net/manual/en/function.mktime.php]mktime[/URL] and [URL=http://uk.php.net/manual/en/function.date.php]date[/URL], to format $row['recieved'] as you like.

Hope this helps.

SS
Copy linkTweet thisAlerts:
@PezmcauthorNov 30.2006 — Now it gets messy

Code: [code=php]
echo "<B>Last Seven Unique Hits:</B><BR>";
$sql = "select count(distinct IP) as hits, recieved FROM $dbsql GROUP BY recieved ORDER BY recieved DESC LIMIT 7";
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
$target = mktime(0,0,0,$showme[month],$showme[day],$showme[year]);
echo date("l", $target);
echo ": $target: $row[hits] $row[recieved]<BR>$showme[month],$showme[day],$showme[year]<br>";
}
[/code]

Results:

Last Seven Unique Hits:

Thursday: -1: 4 2006-11-30

,,

Thursday: -1: 1 2006-11-29

,,

Thursday: -1: 1 2006-11-28

,,

Thursday: -1: 1 2006-11-27

,,

Thursday: -1: 1 2006-11-26

,,

Thursday: -1: 1 2006-11-25

,,

Thursday: -1: 1 2006-11-24

,,

I dont under stand why? How can I get the Aclual Day of the day recieved?
Copy linkTweet thisAlerts:
@scousesheriffNov 30.2006 — Try this:[code=php]
$year = subsr($row['recieved'],0,4);
$month = subsr($row['recieved'],5,2);
$day = subsr($row['recieved'],8,2);
$target = mktime(0,0,0,$month, $day, $year);
echo date("l", $target);
echo ": $row[hits] $row[recieved]<BR>$showme[month],$showme[day],$showme[year]<br>";
[/code]
Copy linkTweet thisAlerts:
@PezmcauthorNov 30.2006 — Current code:
[code=php]
echo "<B>Last Seven Unique Hits:</B><BR>";
$sql = "select count(distinct IP) as hits, recieved FROM $dbsql GROUP BY recieved ORDER BY recieved DESC LIMIT 7";
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
$year = subsr($row['recieved'],0,4);
$month = subsr($row['recieved'],5,2);
$day = subsr($row['recieved'],8,2);
$target = mktime(0,0,0,$month, $day, $year);
echo date("l", $target);
echo ": $row[hits] Recieved on: $row[recieved]<BR>";
} [/code]

Error: Fatal error: Call to undefined function: subsr() in /home/www/shogans.com/stats/detail.php on line 273

Ps tahnsk for help, ill give you a link at bottem of page if you get this working :p
Copy linkTweet thisAlerts:
@PezmcauthorNov 30.2006 — I presume you mean substr? I have changes it and now get:

Thursday: 4 Recieved on: 2006-11-30

Wednesday: 1 Recieved on: 2006-11-29

Tuesday: 1 Recieved on: 2006-11-28

Monday: 1 Recieved on: 2006-11-27

Sunday: 1 Recieved on: 2006-11-26

Saturday: 1 Recieved on: 2006-11-25

Friday: 1 Recieved on: 2006-11-24

Yay working :eek:

Whats ure website ill give you a link on my thanks page
Copy linkTweet thisAlerts:
@PezmcauthorNov 30.2006 — I have never used substr and can see how it can come in very use full, Is there anywhere i can swat up on advanced mysql queryies like: "select count(distinct IP) as hits, receieved FROM $dbsql TABLE group by recieved"

I have never realy under stood them thanks again, ?
Copy linkTweet thisAlerts:
@scousesheriffNov 30.2006 — sorry, should subsr be substr
Copy linkTweet thisAlerts:
@PezmcauthorNov 30.2006 — Thats ok where should I link you to at the bottem of my thanks page?
Copy linkTweet thisAlerts:
@scousesheriffNov 30.2006 — http://www.myphotofolio.co.uk

would be most appreciated.

Glad i could help!

As for SQL, I would just recommend getting a book like the complete reference to SQL (http://www.amazon.co.uk/SQL-Reference-James-R-Groff/dp/0072118458). There are loads of resources out there though. Best of luck.
×

Success!

Help @Pezmc spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.24,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...