Page 1 of 1
MySQL counter, how do I do total and today?
Posted: Sun May 11, 2003 7:37 pm
by scr0p
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?
Posted: Sun May 11, 2003 8:09 pm
by volka
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
Posted: Mon May 12, 2003 2:48 pm
by scr0p
Can you explain it again? I get 1 part, I put a date type field named "Date" and then to update the "today: xxx" counter, I do "UPDATE something WHERE Date=now()" which will update the field that's for today, but if no rows are affected, what do you mean by add a new record
Posted: Mon May 12, 2003 2:55 pm
by scr0p
Also how do I check if 5 minutes passed? I want to make a guestbook, but I don't want the user to post more than 1 post within 5 minutes (spam).
Posted: Mon May 12, 2003 3:37 pm
by AndrewBacca
wot ive done is ive got 1 counter it adds the date to the database, then searchs for all the records that are of todays date.
if you get my meaning.
Andrew
Posted: Mon May 12, 2003 4:46 pm
by volka
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
hitcounterand a record
('12.05.2003', 5). Now using the given value of Now() the where-clause expands to
Code: Select all
UPDATE hitcounter SET count=count+1 WHERE when='12.05.2003'
->
('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
Code: Select all
UPDATE hitcounter SET count=count+1 WHERE when='13.05.2003'
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
Code: Select all
SELECT when FROM posts WHERE user=$_SESSIONїuname] ORDER BY when DESC LIMIT 1
Posted: Fri May 16, 2003 4:31 pm
by scr0p
What 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();
?>
Posted: Fri May 16, 2003 7:02 pm
by scr0p
update code on top again, now it kind of works but if you refresh fast, it makes a new row..
Posted: Sat May 17, 2003 1:26 am
by volka
that' how I would start (but it's untested)
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();
?>
daily and total count might be retrieved in one query
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');
...
?>
Posted: Sat May 17, 2003 9:24 am
by scr0p
Nice! thanks, It works now, I need to try it every day to see how its going, but anyway in the Databse, its storing 0000-00-00 for the date. Why?
--update: I removed the ' ' from now() and it seems to be working now. we will see.
Posted: Sat May 17, 2003 11:38 am
by volka
ah yes, no 's for Now()
My fault.