Part 1
In this tutorial we’ll be creating a users online system which shows the current users online on a certain page. This can be done either by using files to save the data in or database. We’ll be using the second method to save the user online data: a database ( -table ). So let’s start by creating the database and table that will contain the users online data. In this example we call the database ‘test’ and the table ‘online’ – this table will require the following fields:
* timestamp – the time in seconds – counted from UNIX timestamp – when the user viewed the page
* IP – the IP of the user that’s online
* username – the username of the user if logged in ( otherwise: guest )
* location – the page the user is viewing
* loc_title – the name of the page the user is viewing
You can create them manually or use this SQL (import):
CREATE TABLE IF NOT EXISTS `online` ( `timestamp` int(250) NOT NULL, `IP` varchar(50) NOT NULL, `username` varchar(15) NOT NULL, `location` varchar(250) NOT NULL, `loc_title` varchar(25) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Now what we basicly need to do, is add a row to this table each time a user views a page and delete rows that are old ( say from 5 minutes ago ). Then we only show the users that got atleast one row remaining in the ‘online’ table ( unique IP, so it only counts the same user ( same IP ) once ). Therefor we need to set a few variables:
<?php session_start(); //if needed to use sessions $time = timestamp(); //current timestmap $timeout = "3000"; //timeout in seconds, 5 min taken as example $out = $time-$timeout; //all user logs/views from earlier than this will be removed ?>
We set a variable $time containing the current timestamp in seconds using the function timestamp. Then we set the variable $timeout to an amount of seconds ( 3000 in this example = 5 min. ) and create a variable $out which contains the current timestamp ( $time ) minus the timeout in seconds ( $timeout ). So in this case it contains the timestamp of 5 minutes ago. We’ll use this to delete all user rows that were created earlier than 5 minutes ago. So we only will keep the users that have still been active in the last 5 minutes. We’ll use a simple mysql query to do this:
"DELETE online WHERE timestamp < '$out' "
We use the DELETE query type to delete the rows of the table 'online'. But only the ones ( WHERE ) with a timestamp smaller than the timestamp in $out ( timestamp < $out ). Which is equal to the timestamp 5 minutes ago. We'll need to make a host and database connection first using mysql_connect and mysql_select_db. Then we'll use mysql_query function to execute our query.
mysql_connect("localhost", "root"); //host, user(, password)
mysql_select_db("test");
mysql_query("DELETE online WHERE timestamp < '$out' ");
Ok so now we know all rows in the table 'online' are from IPs (users) that still have been active in the last 5 minutes ( the timestamp of the rows/userlogs is > timestamp 5 minutes ago ). Now we'll add a row for the user that is currently viewing this page. We'll use a simple INSERT query for this but first we'll need to set the user IP and username. Define it how you want to. We'll take for example that $_SESSION['username'] contains the username of a LOGGED IN user. So we check if it's there, if not: user default 'guest' username.
if($_SESSION['username'] AND !empty($_SESSION['username'])) {
$username = $_SESSION['username']; //username is the username of user logged in
}else{
$username = "guest"; //not logged in, so guest
}
The IP of the user is stored inside the SERVER array - variable 'REMOTE_ADDR':
$IP = $_SERVER['REMOTE_ADDR']; //= user IP
Now only last are the location url and name. The location we can simply set to $_SERVER['PHP_SELF'], as used in other tutorials as well. This contains a simple path to the current file been viewed. The location name you can set to anything like: "Homepage", depending on what page you insert this code.
So now we've defined all variables needed to add a new user online log:
if($_SESSION['username'] AND !empty($_SESSION['username'])) {
$username = $_SESSION['username']; //username is the username of user logged in
}else{
$username = "guest"; //not logged in, so guest
}
$IP = $_SERVER['REMOTE_ADDR']; //= user IP
$loc = $_SERVER['PHP_SELF'];
$loc_name = "Homepage";
Now we can use a simple INSERT query to make it add the user online log ( row ):
"INSERT INTO online(timestamp, IP, username, location, loc_title)VALUES('$time', '$username', '$IP', '$loc', '$loc_name') "
We simple insert the data: $timestamp for the field 'timestamp', $IP for 'IP', $username for 'username', $loc for 'location' and $loc_name for the field 'loc_title'. Again use the mysql_query function to execute our query:
mysql_query("INSERT INTO online(timestamp, IP, username, location, loc_title)VALUES('$time', '$username', '$IP', '$loc', '$loc_name') ");
Ok, so we're now ready to get all users online/active in the last 5 minutes. We'll use a SELECT query to do this. To select all users we'll need to select all IP addresses but only UNIQUE ones once, so instead of using "SELECT field_name" we use "SELECT DISTINCT(field_name)":
"SELECT DISTINCT(IP) FROM online"
No need to add a WHERE clause part as we are sure all these rows in the table are from users active in the last 5 minutes. W removed old ones and added one for the viewing user. This time we put the query into the mysql_query function but as well in a variable because we'll be using it to count the amount of users online found.
$select_users = mysql_query("SELECT DISTINCT(IP) FROM online");
$users_on = mysql_num_rows($select_users);
We use the mysql_num_rows function to count the rows found by the $select_users query.
So now we can already show how many users are online, great!
We simply display the number of $users_on.
Let's put it all together!
<?php
session_start(); //if needed to use sessions
$time = time(); //current timestmap
$timeout = "3000"; //timeout in seconds, 5 min taken as example
$out = $time-$timeout; //all user logs/views from earlier than this will be removed
if($_SESSION['username'] AND !empty($_SESSION['username'])) {
$username = $_SESSION['username']; //username is the username of user logged in
}else{
$username = "guest"; //not logged in, so guest
}
$IP = $_SERVER['REMOTE_ADDR']; //= user IP
$loc = $_SERVER['PHP_SELF'];
$loc_name = "Homepage";
mysql_connect("localhost", "root"); //host, user(, password)
mysql_select_db("test");
mysql_query("DELETE online WHERE timestamp < '$out' ");
mysql_query("INSERT INTO online(timestamp, IP, username, location, loc_title)VALUES('$time', '$username', '$IP', '$loc', '$loc_name') ");
$select_users = mysql_query("SELECT DISTINCT(IP) FROM online");
$users_on = mysql_num_rows($select_users);
if($users_on == 1) {
echo "1 user online";
}else{
echo $users_on." users online";
}
?>
Last but not least we use an if-loop to check if there's only 1 user online. Because if so: we need to use the word 'user' instead of 'users'
That's the end of the simple users online tutorial part 1 , in part 2 we'll be creating a list of the users that are online ( names and link location ).
Part 2
In the previous part we've made a system that shows the current amount of online users:
<?php
session_start(); //if needed to use sessions
$time = time(); //current timestmap
$timeout = "3000"; //timeout in seconds, 5 min taken as example
$out = $time-$timeout; //all user logs/views from earlier than this will be removed
if($_SESSION['username'] AND !empty($_SESSION['username'])) {
$username = $_SESSION['username']; //username is the username of user logged in
}else{
$username = "guest"; //not logged in, so guest
}
$IP = $_SERVER['REMOTE_ADDR']; //= user IP
$loc = $_SERVER['PHP_SELF'];
$loc_name = "Homepage";
mysql_connect("localhost", "root"); //host, user(, password)
mysql_select_db("test");
mysql_query("DELETE online WHERE timestamp < '$out' ");
mysql_query("INSERT INTO online(timestamp, IP, username, location, loc_title)VALUES('$time', '$username', '$IP', '$loc', '$loc_name') ");
$select_users = mysql_query("SELECT DISTINCT(IP) FROM online");
$users_on = mysql_num_rows($select_users);
if($users_on == 1) {
echo "1 user online";
}else{
echo $users_on." users online";
}
?>
Now beside showing how many total users are online, we'll also show how many members and how many guests are on. We'll aswel add a list of the members that are online. Let's start with the first thing: showing how many guests and members are online. Therefor we need to change this part of the code:
$select_users = mysql_query("SELECT DISTINCT(IP) FROM online");
$users_on = mysql_num_rows($select_users);
if($users_on == 1) {
echo "1 user online";
}else{
echo $users_on." users online";
}
Which selects all unique users by IP. We could make it 2 queries, where one selects all guests online ( WHERE username = 'guest' ) and the other one selects all members ( WHERE username != 'guest' ). But this way it could select one user double ( as both queries select unique IPs for THEIR query ). For example when a user just logged in, the old userlog row ( when he was 'guest' ) is selected in the first query and the new one ( where he is logged in ) is selected with the second query. So this way it could double select one user. So therefore we'll still use ONE query which can only select one user once ( because of the DISTINCT (IP) in only one query with same where clause (none) ). We'll need to get the data (username) from the query though and we need to select the username therefore as well:
$select_users = mysql_query("SELECT DISTINCT(IP), username FROM online");
$users_on = mysql_fetch_assoc($select_users); //all users
$guests = 0;
$users = 0;
while($user_on = mysql_fetch_assoc($select_users)) {
if($user_on['username'] == "guest") {
$guests += 1;
}else{
$members += 1;
}
}
Ok let's go through the code. We start with the variables $guest and $members. These will contain the amount of guests and the amount of members logged in. We use the query now to as well select the username of the logged in users. Then for each logged in user found, the username is stored in $user_on['username']. As we used mysql_fetch_assoc to put the found rows of the query into $users_on['field_name']. For each row. Then inside the while loop we made it check if the username is 'guest' of the found row ( user online ) or not. If it's a guest, the variable $guests ( containing the amount of guests online ) will be increased. Otherwise it's a member who's logged in/online, so the variable $members will be increased by one. Now we can show the amount of members and guests online:
//show total users on:
if($users_on == 1) {
echo "1 user online: ";
}else{
echo $users_on." users online: ";
}
//show guests on:
if($guests == 1) {
echo "1 guest online, ";
}else{
echo $guests." guests online, ";
}
//show members on:
if($members == 1) {
echo "1 member online";
}else{
echo $members.' members online";
}
We done it the same way as before ( when we shown all users online) but now for both the guests and members, and the total users online (which is same as $guests+$members, or just $users_on which we used in the previous script too).
Ok, now we're going to show the USERNAMES of the members online. Therefor we'll need to make another array variable for which we'll add the username into a sub-variable of the array, when a new MEMBER online was found. We need to do this inside the while loop, which handles the data ( IP and username ) of the rows ( users online) found:
$usernames = array(); //array will be containing all usernames online found
while($user_on = mysql_fetch_assoc($select_users)) {
if($user_on['username'] == "guest") {
$guests += 1;
}else{
$members += 1;
}
}
We created an array $usernames beforehand which we'll create a sub-variable for each time a new member online was found ( username will be stored ). This is the case when $username != "guest", which is the 'else' part of the loop. We know the data found is put in $user_on['field_name'], so the username would be set in $user_on['username']. So to add the username to our array we simply do this:
$usernames[] = $user_on['username'];
$usernames[] means it automaticly adds a new index ( new sub-variable ) to the array $usernames, and $user_on['username'] contains the username of the member found. As mentioned before we need to add this to the else part of the loop:
$usernames = array(); //array will be containing all usernames online found
while($user_on = mysql_fetch_assoc($select_users)) {
if($user_on['username'] == "guest") {
$guests += 1;
}else{
$members += 1;
$usernames[] = $user_on['username'];
}
}
Ok, so now we'll have an array $usernames containing all usernames of members online found. The usernames are stored like this:
$usernames[0] = "username of member #1 found";
$usernames[1] = "username of member #2 found";
... etc ...
This is because we used $usernames[] to automaticly number the sub-variable index ( if the previous one was 0, then the next one will be 1 ).
We'll now use a foreach loop to get all values of the sub-variables ( usernames ) in $usernames:
foreach($usernames as $name) {
echo "
".$name."
";
}
What it simply does is for each sub-variable of the array $usernames, it puts the value into $name and shows it. In other words: it shows all usernames found of members online.
Ok let's put it all together!
<?php
session_start(); //if needed to use sessions
$time = time(); //current timestmap
$timeout = "3000"; //timeout in seconds, 5 min taken as example
$out = $time-$timeout; //all user logs/views from earlier than this will be removed
if($_SESSION['username'] AND !empty($_SESSION['username'])) {
$username = $_SESSION['username']; //username is the username of user logged in
}else{
$username = "guest"; //not logged in, so guest
}
$IP = $_SERVER['REMOTE_ADDR']; //= user IP
$loc = $_SERVER['PHP_SELF'];
$loc_name = "Homepage";
mysql_connect("localhost", "root"); //host, user(, password)
mysql_select_db("test");
mysql_query("DELETE online WHERE timestamp < '$out' ");
mysql_query("INSERT INTO online(timestamp, IP, username, location, loc_title)VALUES('$time', '$username', '$IP', '$loc', '$loc_name') ");
$select_users = mysql_query("SELECT DISTINCT(IP), username FROM online");
$users_on = mysql_fetch_assoc($select_users); //all users
$guests = 0;
$users = 0;
while($user_on = mysql_fetch_assoc($select_users)) {
if($user_on['username'] == "guest") {
$guests += 1;
}else{
$members += 1;
$usernames[] = $user_on['username'];
}
}
foreach($usernames as $name) {
echo "
".$name."
";
}
?>
Cheers,
Admin.
Thu, Jan 21, 2010
PHP & MySql