Daily stats

arkismad

hey im looking to make a daily stats section on my website, the only problem is that i dont have a clue on how to make a new row in a mysql table every day. Any help is greatly appreciated

coldwerturkey

use mysql_num_rows() on a query to see if a row exists for today, if it doesn't INSERT a new row.

Piranha

Insert a new row with the time every time someone visits your page. Then you can generate all kind of stats you want including hourly, daily, per page, per ip, per session, per login and in any combination.

arkismad

ok thanks i understand, but whats the best way of formatting the date?

coldwerturkey

arkismad;10910742 wrote:
ok thanks i understand, but whats the best way of formatting the date?

I'd use the mysql timestamp YYYY-MM-DD HH:MM:SS so you're able to use a bunch of date functions of mysql within your queries,

arkismad

is there any command that can do that?

coldwerturkey

when you create your timestamp column, you can use

`timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

which will upon insertion/update of record, record the current time

(assuming you're using mysql)

arkismad

ok cheers m8 i shall give that a shot just now

arkismad

ok i see what you mean, but now is there a command so i can get the same date format in php and can then use an if statement ect

coldwerturkey

date()

more specifically,

date('Y-m-d H:i:s'); //YYYY-MM-DD HH:MM:SS

arkismad

include("mainfunctions.php");
connect();

$date = date('Y-m-d H:i:s'); //YYYY-MM-DD HH:MM:SS 

 $raw = mysql_query("SELECT * FROM `globaldaily`") or die       ( 'Error Finding Global Settings');

	 $global = mysql_fetch_assoc($raw) ; 

if($global['date'] == $date){

 echo "Date Allready Added";

}else{
	 mysql_query("INSERT into globaldaily (hits) values ('0')") or die       ( 'Broken');
}

this is what i have, and its not working 🙁

coldwerturkey

arkismad;10910805 wrote:

include("mainfunctions.php");
connect();

$date = date('Y-m-d H:i:s'); //YYYY-MM-DD HH:MM:SS 

 $raw = mysql_query("SELECT * FROM `globaldaily`") or die       ( 'Error Finding Global Settings');

	 $global = mysql_fetch_assoc($raw) ; 

if($global['date'] == $date){

 echo "Date Allready Added";

}else{
	 mysql_query("INSERT into globaldaily (hits) values ('0')") or die       ( 'Broken');
}

this is what i have, and its not working 🙁

probably because your query should be more like

"SELECT * FROM globaldaily ORDER BY date DESC LIMIT 1"

but, like Piranha said, that logic is a little .. un-good .., a better approach would be to add new row each visit and store more information, ie; ip addresses, browser type, time etc.

also after your second query, if you just want the hit counter to increase +1 every visit on todays row, you can do

"UPDATE globaldaily SET hits = hits +1 WHERE date='{$date}'"

..Another alternative, is to write a query that creates the rows for the next two years.. that way if 0 people visit the site one day, they'll be a record for 0 entries instead of a row not even existing for that day,

bradgrafelman

Seems like the best solution to me would be to add a key (PRIMARY or UNIQUE) on the date column.

That would mean you do the same query (only ONE query) every time, something like:

INSERT INTO globaldaily SET hits=1, date=CURDATE() ON DUPLICATE KEY UPDATE hits=hits+1

That's assuming 'date' is a DATE column (is there really a need to record the time that each hit occurs?).

coldwerturkey

bradgrafelman;10910908 wrote:
Seems like the best solution to me would be to add a key (PRIMARY or UNIQUE) on the date column.

That would mean you do the same query (only ONE query) every time, something like:
INSERT INTO globaldaily SET hits=1, date=CURDATE() ON DUPLICATE KEY UPDATE hits=hits+1
That's assuming 'date' is a DATE column (is there really a need to record the time that each hit occurs?).

you're smart ..copies into notes