[SOLVED] help with my online_list

[pkedpker]

my online_list works of mysql and then reloads everything after 1 load from txt file for performance purposes calling mysql all the time is not worth it. Now if that wasn't enough I ran into a problem where I must call mysql all the time to update the users activity date etc.. if I don't they get removed from list in 15 mins..

 

now that gave me a huger problem if a user is already removed from list.. that doesn't mean hes logged out so that turns into a problem where "UPDATE query" doesn't work anymore.. since u cannot update a row that doesn't exist anymore.

 

I was wondering if there was any code to fix this.. I replaced the UPDATE with INSERT ALL the time.. and it still doesn't work well..

 

<?php
function updateUserOnline() {
if (session_is_registered('id'))  {	
	global $mysql_host, $mysql_user, $mysql_pass, $mysql_db;
	$link = mysql_pconnect($mysql_host, $mysql_user, $mysql_pass);
	mysql_select_db($mysql_db, $link);
	mysql_query("INSERT INTO online_list (id,hidden,date) VALUES (".$_SESSION['id'].", 0, NOW()) ON DUPLICATE KEY UPDATE date=NOW()", $link); 
}
}
?>

 

<?php

	mysql_connect($mysql_host, $mysql_user, $mysql_pass) or die(mysql_error());
	mysql_select_db($mysql_db) or die(mysql_error()); 

	$result = mysql_query("DELETE FROM online_list WHERE date < DATE_SUB(NOW(), INTERVAL 15 MINUTE)") or die("failed to delete old peeps online list: " . mysql_error());
	$result = mysql_query("SELECT count(*) FROM online_list") or die("failed to get members online list: " . mysql_error());
	$row = mysql_fetch_row($result);
	saveData("online_list_num", $row[0]);

 

no errors so i dont know why i'm posting I just want a QUERY that does INSERT when doesn't exist OTHERWISE does UPDATES..

[.josh]

so do a query to select based on the info. If you get a result, it exists. therefore update. If not, insert.

[pkedpker]

yah I did that.. but since this will be called like crazy I made it persistent why open new connections all the time.. heard many bad things about persistent mysql didn't understand much of it.. and if the worst goes like a mysql connection will be open for 8 hours if no one comes.. then the website is a failure anyways 

 

<?php

function updateUserOnline() {
if (session_is_registered('id'))  {	
	global $mysql_host, $mysql_user, $mysql_pass, $mysql_db;
	$link = mysql_pconnect($mysql_host, $mysql_user, $mysql_pass);
	mysql_select_db($mysql_db, $link);
	mysql_query("UPDATE online_list SET date=NOW() WHERE id=".$_SESSION['id'], $link); 
	if(mysql_affected_rows() == 0)
		mysql_query("INSERT INTO online_list (id,hidden,date) VALUES (".$_SESSION['id'].", '0', NOW()) ON DUPLICATE KEY UPDATE date=NOW()", $link);
}
}

?>

 

but since my mysql_affected_rows() has no link in it i am getting feelings it might screw up in the future.

[Philip]

Ehh, theres a few things about this I'll throw my $0.02 into.

 

You're going to be querying the database a lot. No big deal - as long as you create a clean query. I mean, seriously, how do you think forums handle the load? At the bottom of this page:

Page created in 0.05 seconds with 19 queries.

 

Anyways. I think after 15 minutes, the user should be pulled from the list - typically its about 5 minutes before they are considered inactive.

 

Here's what I would do:

• Use a INSERT... ON DUPLICATE KEY UPDATE... for your query. Use their user ID as the primary key, and a timestamp.
  
• On your online list, have in your WHERE clause something like timestamp > date_sub(NOW(), INTERVAL 15 MINUTE)
  
• Don't use persistent connections
  

[pkedpker]

I have all that now why not use persistent I have not gotten any replies why not? It saves time instead of opening tons of connections.. I actually heard its WORSE not to open persistent due to all open connections even after closed they have some delay built into the Windows kernel which cannot be avoided.. it can set lower and avoided on *inx systems but I got XP cannot do jack about that I like XP..

 

yah my page gets generated in 0.005 lol much better than that..

Generated in 0.0072009563446045 seconds.

 

I use microtime() on top and after all includes are done at very end of page I run a comparison of microseconds.

 

I even went lower then that with 0.0009  with flat-file saving.. but its very complicated with online_list system due to file being in use if refresh website extremely fast and stuff.. i gave up on that.