Do I really need three queries here?

[bokeh]

Do I really need three queries here? How can I optimise this?function online_users()
{
$query = "DELETE FROM `online_users` WHERE `ip` = '".$_SERVER['REMOTE_ADDR']."' OR `updated` < '".(time() - 1800)."'";
$result = mysql_query($query) or die ($query.' -- '.mysql_error());
$query = "INSERT INTO `online_users` (`ip`, `updated`) VALUES ('".$_SERVER['REMOTE_ADDR']."', '".time()."')";
mysql_query($query) or die ($query.' -- '.mysql_error());
$query = "SELECT * FROM `online_users`";
$result = mysql_query($query) or die ($query.' -- '.mysql_error());
return mysql_num_rows($result);
}

[NogDog]

If you make ip a key field, then you could combine the 1st two queries with:

$now = time();
$query = <<<EOD
INSERT INTO `online_users` (`ip`, `updated`)
VALUES ('{$_SERVER['REMOTE_ADDR']}', '$now')
ON DUPLICATE KEY UPDATE `updated`='$now'
EOD;

[bokeh]

Ok! But then I would be loosing this bit: OR `updated` < '".(time() - 1800)."'

[NogDog]

Ok! But then I would be loosing this bit: OR `updated` < '".(time() - 1800)."'
If that's important, then I guess you'll still have to do separate queries (though the delete could just be for those with the older times if you go ahead and use the ON DUPLICATE KEY UPDATE thing for the insert query).

[bokeh]

I realise I could use the DUPLICATE KEY idea (the column is unique so it would work without changes) and do the time checking in the SELECT query and not delete anything at all but the table size would grow pretty rapidly. It's not a high traffic site but it still writes a couple of thousand lines to the log daily so the table would soon be enormous and my guess is that alone would make things inefficient.

[NogDog]

How about this sequence?

1. Insert new record with "on duplicate key update" clause.
2. select count(*) to get current number of records
3. if(current number of records > max allowed records)
do delete based on time
current number of records -= mysql_affected_rows()

[bokeh]

Sorry I'm just not SQL savvy (yet). I'm a bit confussed with points 2 and 3 above. I am printing the output direct to the page like this:echo 'The number of users online is '.online_users();And I can't see how I am going to retrieve the correct number if either I don't have a WHERE clause in the SELECT based on updated or DELETE the outdated rows. The truth is it works as it is but for some reason I'm not happy with 3 queries; maybe I need to consult a doctor! Anyway here is what I have after adding DUPLICATE KEY to the UPDATE query.function online_users()
{
$now = time();
$query = "DELETE FROM `online_users` \n".
"WHERE `updated` < $now - 1800";
$result = mysql_query($query) or die ($query.' -- '.mysql_error());
$query = "INSERT INTO `online_users` (`ip`, `updated`) \n".
"VALUES ('{$_SERVER['REMOTE_ADDR']}', '$now') \n".
"ON DUPLICATE KEY UPDATE `updated`='$now'";
mysql_query($query) or die ($query.' -- '.mysql_error());
$query = "SELECT * FROM `online_users`";
$result = mysql_query($query) or die ($query.' -- '.mysql_error());
return mysql_num_rows($result);
}

[NogDog]

Ahhh. I didn't originally understand the underlying purpose of the code. Looks like you need 3 queries. :(

[bokeh]

3 queries and a doctor it is then (or maybe a beer).

[ShrineDesigns]

function online_users()
{
$limit = time() - 1800;
@mysql_query("DELETE FROM `online_users` WHERE `updated` <= $limit");
$result = @mysql_query("REPLACE INTO `online_users` VALUES ('{$_SERVER['REMOTE_ADDR']}', '" .time(). "')");

if(!$result || !@mysql_affected_rows())
{
echo @mysql_error();
}
$result = @mysql_query("SELECT COUNT(*) FROM `online_users`");

if(!$result || !@mysql_num_rows($result))
{
echo @mysql_error();
}
list($n,) = @mysql_fetch_array($result, MYSQL_NUM);
return $n;
}

[bokeh]

function online_users()
{
$limit = time() - 1800;
@mysql_query("DELETE FROM `online_users` WHERE `updated` <= $limit");
$result = @mysql_query("REPLACE INTO `online_users` VALUES ('{$_SERVER['REMOTE_ADDR']}', '" .time(). "')");

if(!$result || !@mysql_affected_rows())
{
echo @mysql_error();
}
$result = @mysql_query("SELECT COUNT(*) FROM `online_users`");

if(!$result || !@mysql_num_rows($result))
{
echo @mysql_error();
}
list($n,) = @mysql_fetch_array($result, MYSQL_NUM);
return $n;
}Is that more efficient somehow? After all there are still three queries. Am I missing something.