MySQL counter, how do I do total and today?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
scr0p
Forum Newbie
Posts: 23
Joined: Mon May 05, 2003 6:49 pm
Location: NY

MySQL counter, how do I do total and today?

Post 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?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
scr0p
Forum Newbie
Posts: 23
Joined: Mon May 05, 2003 6:49 pm
Location: NY

Post 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
scr0p
Forum Newbie
Posts: 23
Joined: Mon May 05, 2003 6:49 pm
Location: NY

Post 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).
User avatar
AndrewBacca
Forum Commoner
Posts: 62
Joined: Thu Jan 30, 2003 10:03 am
Location: Isle of Wight, UK

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

checking a 5-minute-interval is a different thing ;)
But first the 24h-check:
If you query

Code: Select all

SELECT Now()
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
  • when - DATE
    count - INT
and 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
scr0p
Forum Newbie
Posts: 23
Joined: Mon May 05, 2003 6:49 pm
Location: NY

Post 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)
	&#123;
		//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;
	&#125;
	else
	&#123;
		
		#$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());
	&#125;



	print 'Today: '.$hits_today;
	#print '<br>Total: '.$hits_total;
	mysql_close();
?>
scr0p
Forum Newbie
Posts: 23
Joined: Mon May 05, 2003 6:49 pm
Location: NY

Post by scr0p »

update code on top again, now it kind of works but if you refresh fast, it makes a new row..
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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');
...
?>
scr0p
Forum Newbie
Posts: 23
Joined: Mon May 05, 2003 6:49 pm
Location: NY

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

ah yes, no 's for Now()
My fault.
Post Reply