[RESOLVED] Active Users

[aspekt9]

I'm creating an active users script for my website and I'm having a bit of trouble updating the table. It's not producing an error but its not updating the table either. The columns in the active_users table are username varchar(30) and timestamp int(11)

The code pertaining to the problem is:

	 $query = sprintf("UPDATE active_users SET username='%s' AND timestamp = UNIX_TIMESTAMP()",mysql_real_escape_string($user));
	mysql_query($query);

And here is what I use to retrieve who's currently active:

		$result = mysql_query("SELECT username FROM active_users WHERE timestamp > (UNIX_TIMESTAMP() - 900)");
				while($row = mysql_fetch_row($result))
				{
				echo "<b><a href='users.php?action=profile&user=$row[0]'>" . $row[0] . '</a><br /></b>';
				} 

[bradgrafelman]

You say it's not returning any errors? Are you sure? Have you tried doing this:

mysql_query($query) or die('MySQL error - ' . mysql_error() . "<hr>Query: $query");

If you do this, you should see a message like:

#1292 - Truncated incorrect DOUBLE value: ...

Why? Because MySQL doesn't expect to see the logical "AND" in your UPDATE query.

If you'll visit the MySQL manual page for the UPDATE Syntax, you'll see the proper format is a comma-delimited list of column names and their new values:

UPDATE `myTable` SET `col_name1` = 'value1', `col_name2` = 'value2'

[aspekt9]

I thought that was well, I tried it both way and even with the commas it still isn't posting to the database. I put in the error part as well and even if I leave AND in there it still doesn't display an error. Here's the whole function maybe this will help:

function loginform()
{
 global $LANG01;
 $_SESSION['user'] = $_POST['user'];
 $userip = $_SERVER['REMOTE_ADDR'];
 $remember = $_POST['remember'];
 if(get_magic_quotes_gpc()) {
 	$user = stripslashes($_SESSION['user']);
	$password = (md5stripslashes($_POST['pass']));
 } else {
	$user = ($_SESSION['user']);
	$password = md5(($_POST['pass']));
 }
$query = sprintf("SELECT * FROM users WHERE username='%s' and password='%s'",
			mysql_real_escape_string($user),
			mysql_real_escape_string($password));
$result = mysql_query($query);
$nrows = mysql_num_rows($result);

 if ($nrows == 1) {
	$_SESSION['usrlogin'] = 1;
	 $query = sprintf("UPDATE active_users SET `username` = '%s', `timestamp` = UNIX_TIMESTAMP()",mysql_real_escape_string($user));
	mysql_query($query) or die('MySQL error - ' . mysql_error() . "<hr>Query: $query");
	$query = sprintf("SELECT userlevel FROM users WHERE username='%s' AND password='%s'",mysql_real_escape_string($user),mysql_real_escape_string($password));
	$result = mysql_query($query);
	$row = mysql_fetch_row($result);
	$_SESSION['userlevel'] = $row[0];
	//echo '<meta http-equiv="refresh" content="0;URL=index.php">';
 } else {
	echo '<span class="invalid">'. $LANG01[10] .'</span><br /><br />';
	include 'forms/loginform.html';
 }
}

[bradgrafelman]

Okay, well, using the proper syntax, can you show us what you changed your code to?

Also, did you add the or die() code that I gave above? If so, does MySQL give you any errors? Can you echo out $query and show us what it looks like?

EDIT: Woops - didn't see that code before I replied. Still, does MySQL give any error messages at all? Can you echo out $query and show it to us?

EDIT2: Waaaait a minute! Why are you tyring to run an UPDATE query on every row in the table, and change the username to the $user variable? I think you meant to update the timestamp only, and use the username in a "WHERE" statement at the end of the query, e.g.:

UPDATE `active_users` SET `timestamp` = UNIX_TIMESTAMP() WHERE `username` = '%s'

[aspekt9]

Alrighty, echoed out $query and this is what it displays:

UPDATE active_users SET username = 'Paul' AND timestamp = UNIX_TIMESTAMP()

[aspekt9]

Hrm I think I see the problem. I only created the table I didn't populate it with new users signing up so username and timestamp are currently empty. I think I need to have it add the user to that database when they signup for an account? But I think updating the table should still work. It looks like UNIX_TIMESTAMP() isn't inputting a timestamp at all.

[bradgrafelman]

UPDATE, by definition, simply "updates columns of existing rows in tbl_name with new values" (MySQL Manual).

So, if you have no rows, there's nothing to UPDATE. If this is a "Who's Online?" type of table, you might want to do two things:

1. Add a UNIQUE key to the 'username' column.

2. Use a query like this:

   INSERT INTO `active_users` (`username`, `timestamp`)
   VALUES ('%s', UNIX_TIMESTAMP() )
   ON DUPLICATE KEY UPDATE `timestamp` = UNIX_TIMESTAMP()

   Reading the query, it should be pretty understandable what it does.

[aspekt9]

Awesome, worked like a charm, thanks for the help. How would I make it so that it updates everytime a user navigates to another page? Would I have to add the update function to every page on the site?

EDIT: Figured it out, put a simple script in the header of my site 🙂