Visitor Log With MySQL
An easy and simple way of recording the visits of your site.
Step 1: MySQL Dump
CREATE TABLE `Log` ( `ID` int(8) NOT NULL auto_increment, `Date` datetime NOT NULL default '0000-00-00 00:00:00', `IP` varchar(20) NOT NULL default '', `Referer` varchar(255) NOT NULL default '', `Browser` varchar(255) NOT NULL default '', PRIMARY KEY (`ID`) )
Above is the code for your MySQL dump. It creates a table in your database which is called "Log". This table has 5 columns: ID (for the number of visitors of your site), Date (Date And Time your visitor came to your site), IP (IP address of your visitor), Referer (site your visitor came from), and Browser (your visitor's browser).
Note the capitalization of the name of the table and the columns. Remember that they're case sensitive.
Step 2: Getting Your Visitor's Information
Now that we have a table to store your visitor's information, let's create the script for getting that information. We will call this file index.php.
<?php // A: DATABASE INFORMATION $user="username"; $host="localhost"; $password="password"; $database="database_name"; // B: CONNECTING TO YOUR DATABASE $connection = mysql_connect($host,$user,$password) or die ("Couldn't connect to server"); $db = mysql_select_db($database,$connection) or die ("Couldn't select database"); // C: GETTING VISITOR INFO $ip = $_SERVER['REMOTE_ADDR']; $referer = $_SERVER['HTTP_REFERER']; $browser = $_SERVER['HTTP_USER_AGENT']; // D: STORES VISITOR INFO IN A ROW TO YOUR TABLE $query = mysql_query("INSERT INTO Log VALUES ('',now(),'$ip','$referer','$browser')"); // E: DIRECT VISITORS TO YOUR MAIN PAGE header('Location: http://yoursite.com/index2.php'); ?>
Replace the underlined items with your own information. Sections A, B and C don't need to be explained because their comments say it all.
In section D, it inserts your visitor's information (taken from part C of the code) in the Log table. The values are stored in the same order as the columns.
For the ID column, we didn't place any value inside the quotes because it is set to atuo_increment (means that as you add a row in the table, it automatically increases by 1 integer).
now() is for the Date And Time. Its format would be YYYY-MM-DD HH:MM:SS.
$ip is the IP address of visitor. $referer is the site where your visitor came from. $browser is the browser your visitor is using.
Section E, redirects the visitor to your main page.
Step 3: Displaying The Log
We have our table and inserted the visitor's information. The final step is displaying the visitor log. We will call this file log.php.
<html> <head> <title>Visitor Log</title> </head> <body> <?php // A: DATABASE INFORMATION $user="username"; $host="localhost"; $password="password"; $database="database_name"; // B: CONNECTING TO YOUR DATABASE $connection = mysql_connect($host,$user,$password) or die ("Couldn't connect to server"); $db = mysql_select_db($database,$connection) or die ("Couldn't select database"); // C: GETTING THE VISITOR INFORMATION FROM YOUR TABLE $query = mysql_query("SELECT * FROM Log ORDER BY ID DESC"); // D: GETS AND DISPLAYS THE ROWS IN YOUR TABLE while ( $row = mysql_fetch_array($query) ) { $id = $row['ID']; $date = $row['Date']; $ip = $row['IP']; $referer = $row['Referer']; $browser = $row['Browser']; ?> <p> <b>Visitor:</b> <?php echo "$id"; ?><br> <b>Date:</b> <?php echo "$date"; ?><br> <b>IP Address:</b> <?php echo "$ip"; ?><br> <b>Referer:</b> <?php echo "$referer"; ?><br> <b>Browser:</b> <?php echo "$browser"; ?><br> </p> <?php } ?> </body> </html>
Section C selects the rows in your Log table and orders them in descending order (last visitor first).
In section D of the script, it creates a loop that repeats until all the rows has been displayed. This is the format if you open your log.php file...
Visitor: 2 Date: 2003-12-14 12:04:37 IP Address: 000.00.0.000 Referer: http://www.blinding-light.com Browser: Opera 6 Visitor: 1 Date: 2003-12-14 11:53:04 IP Address: 000.00.0.000 Referer: http://blinding-light.com Browser: Opera 6
You can change the format to a less boring one. This could be a very long page so you might want to add a next and previous script to it.
Conclusion
This is just a simple way of recording your visitors. Note that this will record all the visits (including reloads) so it may take up a lot of your database space. Enjoy!
If you have any questions or if you want to discuss this tutorial, you may post at Blinding Light MB.