In this tutorial I am going to teach you how to make a User Stats menu in PHP and MySQL which displays: Unique hits, Unique hits today, Total hits, Total hits today. If you don't have MySQL you can get one for free here. Once you registered a MySQL database we can start coding. But first I want to point out what this tutorial covers. Here's a brief scheme of the tutorial:
1. Build a MySQL table
2. Make a PHP file that connects to the database
3. Make a PHP file that defines the user stats and fills in the database
4. Make a PHP file that defines the hit stats
5. Make a PHP file that shows the stats
Before you begin this tutorial I should recommend you to not copy the code but just type it yourself… but of course you can choose for yourself.
1. Building the MySQL table:
Login into your MySQL in PHPMyAdmin and start a new database. Call it whatever you like. Now we have to think which rows we need to have.
- ID, which counts the number of rows.
- IP, which defines the users' IP
- Referer, which defines the users' referal page.
- Time, which defines the time the user was online
- Date, which defines the date of the users' visit
Now go to the SQL tab and type this code:
|
Create TABLE 'stats' (
'ID' tinyint(11) NOT NULL AUTO_INCREMENT, 'IP' varchar(18) NOT NULL, 'referer' varchar(255) NOT NULL, 'time' varchar(30) NOT NULL, 'date' varchar(30) NOT NULL, PRIMARY KEY('ID')) TYPE = MyISAM; |
2: The connect file:
Your SQL table is now completed. Now we have to make some files where the information comes from. First we need a connect.php file!
|
//Connect.php
<?php $connect = mysql_connect("hostname","username","password") or die("Failed to connect to database"); $db = mysql_select_db("database") or die("Failed to connect to database"); ?> |
Now that wasn't too hard. The $connect variable basically connects to the database, with your hostname, username and password. If there is found an error in your host, username, or password it will return "Failed to connect to database". The $db variable selects the database and else display the error message.
The reason why we make this file separate is because we need it in the two other files we are going to make, and else we have to type them 3 times instead of one!
3. The stats and hit define file:
Now we need to make a file that defines the users' stats and add them to the SQL table we just created.
|
//add_hits.php
<?php Include("connect.php"); $IP = $_SERVER['REMOTE_ADDR']; $referer = $_SERVER['HTTP_REFERER']; $time = time(); $date = date("M, d, Y"); $add_stats = mysql_query("INSERT INTO stats(IP,referer,time,date) VALUES ('$ip', '$referer', '$time', '$date')"); ?> |
Now for the explanation. The first line includes the file we just created, connect.php. The variables are now assigned the users' stats. This is done automatically. The last line is a MySQL query which inserts the variables into the correct columns.
Note: The date display is in format M, d, Y so e.g: Jul, 16,2005
4: The PHP file that sets the hit variables
Now we have to create the third part of the tutorial, the defining of the hit stats.
|
//set_hits.php
<?php Include("connect.php"); $date = date("M, d, Y"); $unique_hits_select = mysql_query("SELECT DISTINCT ip FROM stats"); $unique_hits_nr = mysql_num_rows($unique_hits_select); $unique_hits_today_select = mysql_query("SELECT DISTINCT ip FROM stats WHERE date = '$date'"); $unique_hits_today_nr = mysql_num_rows($unique_hits_today_select); $total_hits_select = mysql_query("SELECT * FROM stats"); $total_hits_nr = mysql_num_rows($total_hits_select); $total_hits_today_select = mysql_query("SELECT * FROM stats WHERE date = '$date'"); $total_hits_today_nr = mysql_num_rows($total_hits_today_select); ?> |
Well this was quite a bunch of code, but it's actually repetitive. The second line selects unique ip's from the stats table in the database. The third line counts the results of that selection. If there are 4 unique ip's in the table, there are 4 rows. The rest is actually the same, except for the different selections.
5: The show hits file:
Now for the last part of our tutorial the show_hits.php script. A very simple script!
|
//show_hits.php
<?php Echo "Total Unique hits: $unique_hits_nr; Echo "Today's Unique hits: " $unique_hits_today_nr; Echo "Total hits: " $total_hits_nr; Echo "Total hits today: " $total_hits_today_nr; ?> |
Don't forget to add a breakline of some sort after each line!
Well this was it! I hope you all liked it!
More Web Programming: 
