Who's Online Database

I have a table that contains a Session ID, user ID, and Last Activity field. Each time a user accesses the website, their session ID is inserted into the database.

If a Session ID is present, but User ID is set to 0, then the user is marked as a guest.

If a Session ID is present, and the User ID field is not 0, then the user is marked as a registered online users

My Question:

What's the best way to开发者_开发百科 display the total users online, and split this information up in to how many are guests, and how many are registered? Can this be done with one query?

Example: There are currently xxx users online. xxx are registered, and xxx are guests.

Many Thanks,

FishSword

---

EDIT:

My session MySQL table contains the following fields:

• sid - Stores the session ID.
• user - Stores the user id of a logged in user. 0 is stored if the user is a guest.
• ip - Stores the ip address of the user.
• updated - stores a timestamp of when the user was last active.

Example Data:

See example data below. sud, user, ip, updated

• sd456asdfas65asf465, 0, 192.168.128.33, 1315181434
• v654xc654v65xc4v65z, 24, 192.168.128.65, 1315181529
• dfsddas654g4sa6g4s6, 0, 192.168.128.33, 1315203155
• y4g4df65gv4ff6sd54g, 69, 192.168.128.76, 1315181134
• c4cs546sd654sdf654df, 42, 192.168.128.85, 1315181101
• if465fsdf465sd46z65, 24, 192.168.128.65, 1315203144

• dasd645as46d5a46465, 69, 192.168.128.12, 1315181134

  1. Example 1 and 3 should only be logged once (as one guest online), as they have the same user id, and have came from the same computer/ip address.

  2. Example 2 and 6 should only be logged once (as one member online), as they have the same user id, and have came from the same computer/ip address.

  3. Even though example 4 and 7 have came from a different computer, user 69 should only be logged as once (one member online), as they have the same user id, and have came from the same computer/ip address.

Cheers! ;)

---

Make table

ID | SessID    | Guest
1  | someRand  | 1
2  | someRand2 | 0

And select it like

SELECT (SELECT COUNT(1) FROM sessions WHERE Guest = 1) as guests, (SELECT COUNT(1) FROM sessions WHERE Guest = 0) as users

and show it like

<?php
$result = mysql_query("SELECT (SELECT COUNT(1) FROM sessions WHERE Guest = 1) as guests, (SELECT COUNT(1) FROM sessions WHERE Guest = 0) as users");
$row = mysql_fetch_assoc($row);
echo "There are ".($row['users'] + $row['guests'])." users online, ".$row['users']." registered and ".$row['guests']." are guests";

---

SELECT COUNT(*) AS online,IFNULL(SUM(user_id=0),0) AS guests,IFNULL(SUM(user_id>0),0) AS registered FROM (
    SELECT DISTINCT ip_address,user_id FROM sessions
) x

---

Create a table (recommended) or in user table where to store a last action timestamp column.

user_id | action_timestamp    |
------------------------------
      1 | 2011-08-15 13:02:00 |
      2 | 2011-08-15 12:34:00 |
      3 | 2011-08-15 11:05:00 |

Then to pull last online users for interval you make following select:

SELECT COUNT(*) FROM table AS t WHERE TIMEDIFF(NOW(), action_timestamp) < '00:15:00' /* fifteen minutes */

Now you have a count of online users in the last 15 minutes.

This will decrease overall performance (depending on the request and records), because every time the user makes a request you have to set two queries, one to insert/update record, and one for displaying the stats bellow.

You may combine the two queries into one multi-query to compensate a little bit.