How to effectively measure bandwidth used by a user

[TheCracker]

Hello,

I have a downloading website, where i measure the bandwidth used by every member.

For example the member is downloading a 1GB file, i want to measure how amny MB the user has downloaded in.

I'm currently using this code, it works but it creates huge load on the my server

	while (!feof($fp) && (connection_status()==0)) {
		$recv = @stream_get_line($fp, 1048577);
		@print $recv;
		@flush();
		@ob_flush();

		$query = mysql_query("UPDATE userband SET today = today + 1  WHERE username='$username'"); 
		$query = mysql_query("UPDATE userband SET month = month + 1 WHERE username='$username'"); 
		$query = mysql_query("UPDATE userband SET total = total + 1 WHERE username='$username'");

	}

is there a more efficient way to do this? i keep track of bandwidth used in month, yeat and total. as you can see for every 1MB of chunk data 3 SQL statements are executed but the download happens fast so many many sql statements are executed in one second

Advice is appreciated, thank you

[weir-07]

Might be easier if you just create a 'logs' table with fields like username:action:mb:stamp:url:uip

After each action you could insert a record populating this - just make a function so you can perform it easily.
InsertStat($username,"downloaded $filename",$mb,time(),$url,$uip);

Then make a page which takes this data and spits it out on a page for you filtered user by user for the current month.

[tangoforce]

For a start, you're running 3 queries there all on the same table. Why don't you just set those 3 columns to their new values in one query?

Also, you're doing it on every pass of the loop. Why not just do it at the end of the script execution? You don't need those numbers in real time do you? (and even if you did, updating 3 columns in one query is still 1/3 of the workload on the sql server).

[TheCracker]

You are absolutely right in this, I don't know why i have done this. I have also though to add "LIMIT 1" at the end of the sql query to avoid it going through all of the records

But i do want it in real time, but in case i don't want and the user terminates the download in middle how can i execute a php command when the downloader disconnect?

Originally Posted by tangoforce

For a start, you're running 3 queries there all on the same table. Why don't you just set those 3 columns to their new values in one query?

Also, you're doing it on every pass of the loop. Why not just do it at the end of the script execution? You don't need those numbers in real time do you? (and even if you did, updating 3 columns in one query is still 1/3 of the workload on the sql server).

[tangoforce]

register_shutdown_function()

That will allow you to run a function when the script ends, user disconnects etc.

[TheCracker]

i think that is what i'm looking for

Thank you