MySQL counter, how do I do total and today?
Moderator: General Moderators
MySQL counter, how do I do total and today?
My counter is working fine, it records the hits but, I want another counter, one holds total the other holds the hits for today. I kind of know how, add 1 to total and add 1 to the other counter, and when the day ends, reset the 'today' counter.. but how do I check if 24 hours passed?
for eaxmple by having a date field day and updating with a where-clause like UPDATE ... WHERE day=Now()
If no row was affected (see: http://php.net/mysql_affected_rows) you have to add a new record
If no row was affected (see: http://php.net/mysql_affected_rows) you have to add a new record
- AndrewBacca
- Forum Commoner
- Posts: 62
- Joined: Thu Jan 30, 2003 10:03 am
- Location: Isle of Wight, UK
checking a 5-minute-interval is a different thing 
But first the 24h-check:
If you query you'll get something like 12.05.2003 23:42:31, the current datetime. But mysql knows how to convert and compare it to a DATE(-only) field, ignoring the time.
Let's say you have a simple table hitcounter-> ('12.05.2003', 6).
In about half an hour (my timezone) Now() will return 13.05.2003 00:12:31, the where-clause expands to and the will be no record matching the condition (yet), so mysql_affected_rows() will return 0 and I know I (my script) have to insert a new record ('13.05.2003', 1)
If you want to check a 5-minute interval you can query all records (of that user) in descending order and check the first one. Since you only need the last one, limit it to only one result-record
But first the 24h-check:
If you query
Code: Select all
SELECT Now()Let's say you have a simple table hitcounter
- when - DATE
count - INT
Code: Select all
UPDATE hitcounter SET count=count+1 WHERE when='12.05.2003'In about half an hour (my timezone) Now() will return 13.05.2003 00:12:31, the where-clause expands to
Code: Select all
UPDATE hitcounter SET count=count+1 WHERE when='13.05.2003'If you want to check a 5-minute interval you can query all records (of that user) in descending order and check the first one. Since you only need the last one, limit it to only one result-record
Code: Select all
SELECT when FROM posts WHERE user=$_SESSIONїuname] ORDER BY when DESC LIMIT 1What is wrong with this: I updated the code below
Code: Select all
<?php
$database = 'counterDB1';
$hits_today = 0;
$hits_total = 0;
//Connect to db, and result will hold table1
mysql_connect('localhost') or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
//Todays hits
$result = mysql_query("SELECT * FROM table1 WHERE TheDate=now()");
$hits_today = mysql_result($result, 0, 'today');
//Set the new h1t count
$hits_today++;
$update_today = mysql_query("UPDATE table1 SET today='$hits_today' WHERE TheDate=now()");
if(mysql_affected_rows() === 0)
{
//0 rows affected, now() is not found, day passed. make a new record (new day + start at 1)
$dick = mysql_query("INSERT into table1(today, TheDate) values(1, now())");
$hits_today = 1;
}
else
{
#$update_total = mysql_query("UPDATE table_total SET total='$hits_total' WHERE total");
$result = mysql_query("UPDATE table1 SET today='$hits_today' WHERE TheDate=now()") or die(mysql_error());
}
print 'Today: '.$hits_today;
#print '<br>Total: '.$hits_total;
mysql_close();
?>that' how I would start (but it's untested)daily and total count might be retrieved in one query
Code: Select all
<?php
$database = 'DB_name';
$hits_today = 0;
$hits_total = 0;
//Connect to db, and result will hold table1
$dbConn = mysql_connect('localhost') or die(mysql_error());
mysql_select_db($database, $dbConn) or die(mysql_error());
mysql_query("UPDATE table1 SET today=today+1 WHERE TheDate='now()'", $dbConn) or die(mysql_error());
if(mysql_affected_rows($dbConn) < 1)
{
// now() is not found, meaning the day passed so make a new record (new day + start at 1)
// lock the table, since this should happen only once a day (a splitsecond), it does no harm
mysql_query('LOCK TABLES table1 WRITE', $dbConn) or die(mysql_error());
// just to be sure no other request has done this in the meantime
mysql_query("UPDATE table1 SET today=today+1 WHERE TheDate='now()'", $dbConn) or die(mysql_error());
if(mysql_affected_rows($dbConn) < 1)
mysql_query("INSERT into table1(today, TheDate) values(1, 'now()')", $dbConn) or die(mysql_error());
mysql_query('UNLOCK TABLES', $dbConn)or die(mysql_error());
}
$result = mysql_query("SELECT today FROM table1 WHERE TheDate='Now()' LIMIT 1", $dbConn) or die(mysql_error()) or die(mysql_error());
$hits_today = mysql_result($result, 0, 'today');
// having no different table for total count, get the sum of all daily counters
$result = mysql_query("SELECT sum(today) as total FROM table1", $dbConn) or die(mysql_error());
$hits_total = mysql_result($result, 0, 'total');
print 'Today: '.$hits_today;
print '<br>Total: '.$hits_total;
mysql_close();
?>Code: Select all
<?php
...
$result = mysql_query("SELECT a.today, sum(b.today) as total FROM table1 as a JOIN table1 as b WHERE TheDate.id='Now()' GROUP BY a.today LIMIT 1", $dbConn) or die(mysql_error());
$hits_today = mysql_result($result, 0, 'today');
$hits_total = mysql_result($result, 0, 'total');
...
?>