using php to make a ip visit counter in mysql

Question

I am a very novice programmer and am just beginning to use php. I am using php to get the user ip store it in mysql. try inserting the ip to the mysql db. if it is already there then update the visit count. But none of my queries are working. It is returning that I am connected to my db This is what i have so far:

 $ipaddress = $_SERVER["REMOTE_ADDR"];
 print "$ipaddress <br>";

 $rv = mysqli_real_query("INSERT INTO visit ( ipaddress, count) VALUES (  '$ipaddress', 1)");
if ($rv === false){
 mysqli_real_query($con,"UPDATE visit SET count=count+1
 WHERE ipaddress = '$ipaddress'");
 mysqli_close($con);
 }
 mysqli_close($rv);
$count = mysqli_real_query("SELECT count from visit where ipaddress = '$ipaddress'");
print "You have visited this site $count time(s)";

"But none of my queries are working" - we need a proper problem description. — Karoly Horvath, Jan 26 '14 at 01:48
this is what the website returns: Hello World! You are connected. 97.121.20.115 You have visited this site time(s) — jshill103, Jan 26 '14 at 01:50
when i check the mysql workbench the update doesnt go through and its not displaying the count. — jshill103, Jan 26 '14 at 01:51
well one thing i notice is that you named your column `count`, which is a reserved word in mysql. You shouldn't name your columns reserved words. However, if you insist, you need to wrap them in backtics (that thing above your tab key) in your query: \`count\` — CrayonViolent, Jan 26 '14 at 01:52
Also beware that $_SERVER["REMOTE_ADDR"] will not always be populated. Here's a great code example to check for other possibilities: http://stackoverflow.com/questions/1634782/what-is-the-most-accurate-way-to-retrieve-a-users-correct-ip-address-in-php/2031935#2031935 — John McMahon, Jan 26 '14 at 03:18
You can reduce the `INSERT` & `UPDATE` actions to just 1 query using `ON DUPLICATE KEY` http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html — WebChemist, Jan 26 '14 at 07:17

tgun926 · Accepted Answer · 2014-01-26T04:37:50.627

1.) Where's your $con for your first query in $rv?

2.) Your queries need backticks for reserved words such as count:

$rv = mysqli_real_query($con, "INSERT INTO `visit`(`ipaddress`, `count`) VALUES('$ipaddress', 1)");

3.) Your inside if statement will only fire if there is an error in your first line. This means you'll never actually UPDATE the count, unless something went wrong.

Suggestion 1: - Open up your table in phpmyadmin and insert a row in manually with your IP address and some random count number. Then work on being able to display the line You have visited this site x time(s). Once that is working, then work on inserting/updating your table.

Suggestion 2: You should do it the other way around. That is, check to see if the ip address exists in the table. If it does - update it. If it doesn't exist, then add a new row.

your comments were very helpful. I got it working thanks to you helping me rethink this. — jshill103, Jan 26 '14 at 06:41

score 0 · Answer 2 · edited Apr 13 '17 at 12:33

This is fine for practicing simple database connections, but a better way to do this would be to write a simple file to a /tmp/ folder and append 1 character. A single ASCII character is 1 byte, so the number of visits is simply the filesize of that ip file in bytes. Appending a single character and running a filesize check should have a lot less overhead than running database queries.

Example:

//Logging a Visit
$log_file   = '/tmp/ip_'.$_SERVER['REMOTE_ADDR'];
$log = fopen($log_file, "a");
fwrite($log,'0');
fclose($log);

//Displaying Count
if(file_exists($log_file)){
    $visits = filesize($log_file);
    echo $log_file.' has visited the site '.$visits.' time'.($visits>1 ? 's':'');
}
else{
    echo $log_file.' has not previously visited the site';
}

See here for an example of how I suggested this method to another user to block a bot that visited a site more than x number of allowed times: Number of page requests by any Bot in 5 secs

Also, just remember that with routers and NAT, an ip address does not guarantee a unique visitor, so IP address should not be relied on to identify a unique user in most situations.

score 0 · Answer 3 · answered Jan 26 '14 at 04:52

Some problems:

You aren't escaping your SQL statement.
You close your connection (twice), and then never open it again when you are executing the SELECT statement.
You are not escaping your query arguments.

Fixing these issues, results in the following:

<?php

  $con = mysqli_connect();
  $ip = mysqli_real_escape_string($_SERVER["REMOTE_ADDR"]);

  $q  = 'INSERT INTO `visit` (`ipaddress`, `count`)';
  $q .= 'VALUES ("'.$ip.'",1) ON DUPLICATE KEY UPDATE `count` = `count` + 1';

  $result = mysqli_real_query($q);
  if (!$result) {
      echo mysqli_error();
  }

  $q = 'SELECT `count` FROM `visit` WHERE `ipaddress` = "'.$ip.'"';
  $result = mysqli_real_query($q);

  if (!$result) {
     echo mysqli_error();
  } else {
     print "You have visited this site ".$result[0]."time(s)";
  }
?>

using php to make a ip visit counter in mysql

3 Answers3