active users problem?

homer09001

ok heres my code:

<?php 

include_once('dbconnect.php3');  // include database params & conn var 

$query = "INSERT INTO `usercount` (Remote_IP, last_page, lastView) VALUES ('".$_SERVER['REMOTE_ADDR']."', '".$_SERVER['REQUEST_URI']."', NOW()) ON DUPLICATE UPDATE last_page='".$_SERVER['REQUEST_URI']."', lastView=NOW()"; 
$res = @mysql_query($query); 
if(!$res){ echo mysql_error(); } 

$oldest = 60*10; // Set to 10 minute maximum limit (60 seconds * 10 minutes) 

$query = "SELECT count(id) AS active FROM `usercount` WHERE UNIX_TIMESTAMP(lastView)>UNIX_TIMESTAMP(NOW())-$oldest"; 
$res = mysql_query($query); 
$users = mysql_result($res, 0, 'active'); 
?>

error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE last_page='/myphpwcms/index.php', lastView=NOW()' at line 1

any ideas?

konrad

The correct syntax for what you want to do is:
ON DUPLICATE KEY UPDATE
This can update UNIQUE indexes or PRIMARY KEY. Look at manual for more info.

homer09001

😕 im confused?

are you saying i need to change it to:

ON DUPLICATE UNIQUE UPDATE?

konrad

Sorry for not beeing clear enough. Here is a paragraph from documentation

If you specify ON DUPLICATE KEY UPDATE (added in MySQL 4.1.0), and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have identical effect:

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

The rows-affected value is 1 if the row is inserted as a new record and 2 if an existing record is updated.

so you should try with

$query = "INSERT INTO `usercount` (Remote_IP, last_page, lastView) VALUES ('".$_SERVER['REMOTE_ADDR']."', '".$_SERVER['REQUEST_URI']."', NOW()) ON DUPLICATE KEY UPDATE last_page='".$_SERVER['REQUEST_URI']."', lastView=NOW()";

homer09001

Sorted taht was teh key thanks for the help dude