jQuery-Ajax Tutorials MYSQL PHP

How to Create Who Is Online” Widget With PHP, MySQL & jQuery

Step 1 – XHTML

As usual, we start off with the XHTML part. The code presented here might not look like much, but it is all that we need to show off all the work that has been done by the backend. The widget features a slick slide-out panel with all the geolocation data, shown on mouse over.

demo.html

As you may see from the markup above, the main container div – “onlineWidget” contains the slide-out panel (the div with class name “panel“), the total number of people online (the “count” div), the “online” label and the green arrow to the right.

i1.png

The panel div is dynamically filled by AJAX with the countries with the most visitors currently online. The default content of this div is a rotating gif preloader, which is replaced with the geo data once the AJAX request is complete (usually in less than a second). We will come back to this in a moment.

Step 2 – The Database

Unlike the usual routine, here we are going to take a look at how the database is structured, as it is fundamental to the rest of the script.

All of the widget data is stored into the tz_who_is_online table. It consists of six fields (or columns). The first one – ID, is a standard primary key / auto increment field. After this is the IP field which stores the visitor’s IP address (converted to a integer beforehand with the ip2long PHP function).

After this are three fields fetched by Hostip’s API – Country, CountryCode and City. The widget is not using the city field at this point, but it is good to have in case somebody wants to implement it. Last is the DT timestamp field, which is updated on every page load and enables us to track who is online (users without a page load in the last 10 minutes have probably left the site).

i3.png

Step 3 – CSS

The widget is (almost) image free, and is only styled with CSS. Lets take a look at the styling, as defined in styles.css. The code is divided in two parts, so it is easier to follow.

who-is-online/styles.css – Part 1

In the first step above, you can see that we style the widget and the slide-out panel at once. This is to ensure that they have consistent styling which is easy to change later on. Some rules are unique to the panel, however, so we include a individually targeted set of rules in the second part of the code.

We also define the hover state and style the label and count divs.

who-is-online/styles.css – Part 2

In the second part of the file, we style the how the geolocation data that is presented in the slide-out panel, after jQuery fetches it from the back-end. With this we can continue with the next step.

i2.png

Step 4 – PHP

This is where the magic happens. PHP has to keep the database of online users up to date and fetch IP-to-location data from Hostip’s API. This is later cached for future use in a cookie on the visitors PC.

who-is-online/online.php

This PHP script is initially called by jQuery in order to populate the count div with the current number of people online. Behind the scenes, however, this script writes the visitor’s IP to the database and resolves their IP-to-location data.

This is the best strategy in organizing the back-end, as we keep the calls to the API (which are quite time expensive) distributed to each user as they visit the site for the first time.

The other alternative would be to store only the IPs of the visitors and queue the geolocation data once the panel is shown. This would mean to resolve a huge number of IPs simultaneously, which would make the script unresponsive and get us black-listed from the API. Totally not cool.

You can queue the Hostip’s API by opening a connection to a URL similar to this: http://api.hostip.info/?ip=128.128.128.128. It returns a valid XML response, which contains all sorts of data, including a country and city name associated with the IP, country abbreviation and even absolute coordinates. We are fetching this data with the PHP file_get_contents() function and extracting the bits of information we need.

who-is-online/geodata.php

Geodata.php is fetched by jQuery to populate the slide-out panel with location data. This file basically queues the database with a GROUP BY query, which groups the individual users by country and orders the resulting rows in a descending order, with the most popular countries at the top.

For the flag icons, we are using the famfamfam flag icon set, which is released as public domain. A great thing about the Hostip API, is that it returns the country code in a standard two letter format, which is also shared by the famfamfam icon set. This means that in the while loop, it is really easy to find the appropriate flag to show, by just lowering the case of the country abbreviation stored in the database and adding a gif extension.

Step 5 – jQuery

JavaScript manages the AJAX requests and slides the panel. This would be a daunting task with pure JS alone, which is why we are using the newest version of the jQuery library.

Now lets take a look at what the code looks like.

who-is-online/widget.js

You might be a bit perplexed with the use of setTimeout in the menu. This is done, so we have a bit of delay between the hovering of the mouse and the actual opening of the slide-out panel. This way, unintentional movements of the mouse cursor over the widget won’t fire the open event, and once opened, will not close it immediately once the mouse leaves it.

With this our widget is ready!

Setting up the demo

At this point you probably want to grab the widget and put it on your site. To make it work, you need to execute the SQL code found in table.sql in the download archive. It will create the tz_who_is_online table in your database, which is used by the widget. Later you need to upload the files to your server and include widget.js to the head section of your page (along with the jQuery library). After this you have to fill your MySQL login details in connect.php and finally add the markup from demo.html to your web page.

Conclusion

Having access to real time data on your site userbase is a dream to any webmaster. Tools like Google Analytics give a great perspective on your site’s reach, but lack the real time feel a simple widget like this can provide.







 

 

Related Post

Share this

Leave a Reply

Your email address will not be published. Required fields are marked *