[SOLVED] who's online script help

[Goose87]

Hi,

 

A friend of mine gave me the following:

 

SELECT `username` FROM `users` WHERE `updated` >= DATE_SUB(NOW(), INTERVAL 3 MINUTE) ORDER BY `id` ASC"

 

Ive added an "updated" field to the database in the users table, and ive put it as a Datetime (20) field.  I have also put on the page to update the database and set the updated field to "NOW()" every time the user loads a new page on my site, but it doesnt seem to work.

 

Any ideas?

[phpSensei]

try

 

or die(mysql_error());

 

see what comes up.

[Goose87]

All i get on the page is "n" on its own

[phpSensei]

Post the scripts please, I just want to examine it. I wrote a whois online script long ago, just want to see your method. And also, the query should work if there is no error.

[Goose87]

Well its not my script i dont really understand how it works! My friend just sent me this part in a message on my game:

 

echo "<h1>Online List</h1>";

$array = array(); 
$query = mysql_query("SELECT `username` FROM `a_users` WHERE `updated` >= DATE_SUB(NOW(), INTERVAL 3 MINUTE) ORDER BY `user_id` ASC"); 
while ($result = mysql_fetch_array($query)) {
$array[] = "{$result["username"]}";
} 
echo implode(", ", $array)."n"; 

 

Sorry i forgot to post the rest of it in the first message i thought i had! but then realised i only posted the query! My bad

[phpSensei]

try

 

echo "<h1>Online List</h1>";

$array = array(); 
$query = mysql_query("SELECT `username` FROM `a_users` WHERE `updated` >= DATE_SUB(NOW(), INTERVAL 3 MINUTE) ORDER BY `user_id` ASC") or die(mysql_error()); 
while ($result = mysql_fetch_array($query)) {
$array[] = "{$result["username"]}";
} 
echo implode(", ", $array)."n"; 

[Goose87]

I tried that a sec ago, and all i get is the "n"

[phpSensei]

<?php


function online_users($period = 300 /* seconds */) 
{ 
    $query = "DELETE FROM `online_users` WHERE `updated` < '".(($now = time()) - $period)."'"; 
    $result = mysql_query($query) or die ($query.' -- '.mysql_error()); 
    $query = "INSERT INTO `online_users` (`ip`, `updated`, `user`) ". 
             "VALUES ('".$_SERVER['REMOTE_ADDR']."', '".$now."', '".(isset($_COOKIE['user'])?1:0)."')". 
             "ON DUPLICATE KEY UPDATE `updated`='$now', `user` = '".(isset($_COOKIE['user'])?1:0)."'"; 
    mysql_query($query) or die ($query.' -- '.mysql_error()); 
    $query = "SELECT * FROM `online_users`"; 
    $result = mysql_query($query) or die ($query.' -- '.mysql_error()); 
    $return = array('users' => 0, 'guests' => 0); 
    if(mysql_num_rows($result) > 0) 
    { 
        while($row = mysql_fetch_assoc($result)) 
        { 
            if($row['user'] == 1) 
            { 
                $return['users']++; 
            } 
            else 
            { 
                $return['guests']++; 
            } 
        } 
    } 
    return $return;     
} 

?> 

<?php 

# in use 
$online = online_users(); 
$s['users'] = ($online['users'] != 1) ? 's' : NULL; 
$s['guests'] = ($online['guests'] != 1) ? 's' : NULL; 

#print results 
echo 
$online['guests'].$s['guests'];


?> 

 

 

 

Thats my script i got a long time ago, just fetch all the id's from the online table in your database, and echo the usernames.

 

 

Make sure you change the $_COOKIE's to $_SESSION, depending on what you are using.

 

 

ONLINE USER TABLE

 

- id int(11) UNSIGNED AUTO_INCREMENT,

- ip varchar(225) not null,

- user varhchar(225) not null

- updated TIMESTAMP

 

[Goose87]

Ah ok, I will try and fiddle to make that work on mine.  You code in a VERY different way to me. definately makes it shorter, but its a lot more complicated to read

 

Thanks for the reply, Ill try it now

 

James.

[phpSensei]

Ah ok, I will try and fiddle to make that work on mine.  You code in a VERY different way to me. definately makes it shorter, but its a lot more complicated to read

 

Thanks for the reply, Ill try it now

 

James.

 

hey, make sure you create the table i just posted in my post. I added some more information.

[Goose87]

aah ok, glad you mentioned the change cookie to session part i always use sessions, so that makes it more clear im trying to work out exactly how your coding works and how i would code it differently.  thanks for the extra info

[phpSensei]

aah ok, glad you mentioned the change cookie to session part i always use sessions, so that makes it more clear im trying to work out exactly how your coding works and how i would code it differently.  thanks for the extra info

 

.

 

 

Keep in mind if you read it slowly, its really easy to comprehend, don't be fooled by the size of a php script. Same thing with math/science, they go through massive amount of writting work, just to come up with an easy answer.

 

Read it line by line.

 

If you don't understand, i will explain it. Just tell me if the script worked.

 

 

[Goose87]

The script at the moment isnt inputting the username into the database, it also isnt updating the timestamp field with a proper value.

 

The script is only displaying 1 on the page.

[phpSensei]

Here i shortened the script for you, and you need to put this script on every page.

 

 
<?php
$period = 30; // USERS ONLINE FOR THE PAST 30 SECONDS//
$ip       = $_SERVER['REMOTE_ADDR']; // GUESTS ONLINE
$user    = (isset($_SESSION['user'])) ? 1 : 0;

$query = "DELETE FROM `online_users` WHERE `updated` < '".(($now = time()) - $period)."'"; 
   
$result = mysql_query($query) or die ($query.' -- '.mysql_error()); 

$query = "INSERT INTO `online_users` (`ip`, `updated`, `user`) ". 
             "VALUES ('".$_SERVER['REMOTE_ADDR']."', '".$now."', '".$user."')". 
             "ON DUPLICATE KEY UPDATE `updated`='$now', `user` = '".$user."'"; 
             
             mysql_query($query) or die ($query.' -- '.mysql_error()); 

?>

 

 

make sure isset($_SESSION['user']) is the right $_SESSION.

 

tell me if this inserts the data into the database.

 

 

 

 

To select the users online

 

<?php

       while($row = mysql_fetch_array(mysql_query("SELECT * FROM online_users"))){

                   $user = mysql_fetch_array(mysql_query("SELECT * FROM user WHERE username = '$row[username']"));
      
                         echo $user[username] . "<br>";

   } 


?>

[Goose87]

Ok its all working except the timestamp field.  I managed to fiddle with the $_SESSION['user'] part, at first i thought it was meant to be the username, but then i realised what you were trying to do with it, so i sorted it out

 

It doesnt work at the moment, because whoever freshes any page on my site, deletes the last person who did it, thus, it always shows one person online I dunno why the the $now part isnt working with the db, it makes sense.. oh wait! 1min i will change it to erm.. int(20), because you're using the time() variable, and thats what i always store that as

[phpSensei]

Ok its all working except the timestamp field.  I managed to fiddle with the $_SESSION['user'] part, at first i thought it was meant to be the username, but then i realised what you were trying to do with it, so i sorted it out

 

It doesnt work at the moment, because whoever freshes any page on my site, deletes the last person who did it, thus, it always shows one person online I dunno why the the $now part isnt working with the db, it makes sense.. oh wait! 1min i will change it to erm.. int(20), because you're using the time() variable, and thats what i always store that as

 

I made a big MISTAKE

 

try

 

 
<?php
$period = 30; // USERS ONLINE FOR THE PAST 30 SECONDS//
$ip       = $_SERVER['REMOTE_ADDR']; // GUESTS ONLINE
$user    = $_SESSION['user'];

$query = "DELETE FROM `online_users` WHERE `updated` < '".(($now = time()) - $period)."'"; 
   
$result = mysql_query($query) or die ($query.' -- '.mysql_error()); 

$query = "INSERT INTO `online_users` (`ip`, `updated`, `user`) ". 
             "VALUES ('".$_SERVER['REMOTE_ADDR']."', '".$now."', '".$user."')". 
             "ON DUPLICATE KEY UPDATE `updated`='$now', `user` = '".$user."'"; 
             
             mysql_query($query) or die ($query.' -- '.mysql_error()); 

?>

 

 

 

Make you edit the $_SESSION['user'] to your own.

[Goose87]

I dont see the big error you made? it looks exactly the same..

 

At the moment I have:

 

<?php $period = 30; // USERS ONLINE FOR THE PAST 30 SECONDS//
$ip       = $_SERVER['REMOTE_ADDR']; // GUESTS ONLINE

$query = "DELETE FROM `online_users` WHERE `updated` < '".(($now = time()) - $period)."'"; 
   
$result = mysql_query($query) or die ($query.' -- '.mysql_error()); 

$query = "INSERT INTO `online_users` (`ip`, `updated`, `user`) ". 
             "VALUES ('".$_SERVER['REMOTE_ADDR']."', '".$now."', '".$user_id."')". 
             "ON DUPLICATE KEY UPDATE `updated`='$now', `user` = '".$user_id."'"; 
             
             mysql_query($query) or die ($query.' -- '.mysql_error()); 
?>

 

$user_id is my $_SESSION['user']; just defined as a variable.

 

It seems to work at the moment.  I had to make the field "user" in the table unique though, otherwise it duplicated results, and i also had to change the field updated to int(20) so that it stored the 11 digit number for time()-$period.

 

That seem right to you? or have i made a mistake?

[phpSensei]

the filed updated is a TIMESTAMP.

 

It should stay that way, and the mistake i made was that everytime a user was online, it put in the database "1" for online, and "0" for offline. What we want is to insert the username, to make it more flexible.

[Goose87]

Ive never managed to get the TIMESTAMP feature to work with anything when Ive been using time().  Ive always had to use INT, and then i just do a little bit of maths on it to make it into minutes if its > 60 seconds.

 

I have got a little list working out quite nicely now showing the username, and the time since their last action

 

Does that mean its working? Or have i bodged it together and you can see some mistakes? 

 

The other thing, I havent got the number of users online part working, just the one displaying the usernames and time since action.  Does that matter?

[phpSensei]

you can use mysql_num_rows for the number of rows in the online_users table, then just echo it.

 

 

<?php

$query = mysql_query("SELECT * FROM online_users");
$num_of_online = mysql_num_rows($query);

echo "There is <strong>" . $num_of_online . "</strong> online";


?>

 

 

 

Mark the topic solved once this works please.

[Goose87]

Sorry for not marking it as solved earlier.  Thanks a lot for your help I really appreciate it! Ive added an "online" feature on my highscores thanks to you as well, so thats a nice easy way for everyone to see if they are online or not