Keeping track of visitors using their ip addresses PHP/MYSQL

[wtfsmd]

I am trying to keep track of visitors by storing their IP and and the last active time that they visited.

 

my script works ... but sometimes i get duplicates when all i want is a single record for them and it just updates the last time they visited (the reason i have it update instead of inserting a new row is because i have the $_SESSION['last_active_date'] update every time they reload or navigate to a new page so i can see if they are active or not.)

 

anyways .. here is the code:

 

$query = "SELECT * FROM guest"; 
 	$result = mysql_query($query) or die(mysql_error());
	while($row = mysql_fetch_array($result)){
	$stored_ip = 	$row['ip'];
	}
	$ip = $_SERVER['REMOTE_ADDR'];
	if($stored_ip == $ip){
	mysql_query("UPDATE guest SET username='$gname', last_active_date='$date', last_active_time='$thetime', browser='$browser' WHERE ip='$ip'") 
	or die(mysql_error());	
	}else{
	mysql_query ("INSERT INTO guest (username, ip, last_active_date, last_active_time, browser) VALUES ('$gname', '$ip', '$date', '$thetime', '$browser')")
	or die(mysql_error());	
	}

[redarrow]

wast off time use the users id....

 

sorry but ip address change to much...........

[wtfsmd]

this is to track visitors, if they logged in it wouldn't be a problem just wanted to track the people who visit the site. you cant track their user name if they don't have one.

[redarrow]

agree sorry.............

 

my fault............

[redarrow]

example only do your thing .......

<?php

//database connection

$ip=$_SERVER['REMOTE_ADDR'];

$sql="select from sec where ip='$ip'";

$res=mysql_query($sql)or die(mysql_error());

if(mysql_num_rowa(res)==1){

$sql2="update sec set ip='$ip' where ip='$ip'";
    
$res2=mysql_query($sql)or die(mysql_error());

}else{

$sql3="inset into sec(ip)VALUES($ip)";

$res3=mysql_query($sql)or die(mysql_error());

}

?>

[F1Fan]

This would be much more efficient:

 

<?php
$ip = $_SERVER['REMOTE_ADDR'];
$result = mysql_query("UPDATE guest SET username='$gname', last_active_date='$date', last_active_time='$thetime', browser='$browser' WHERE ip='$ip'")
	or die(mysql_error());
if (mysql_affected_rows($result)==0){
mysql_query ("INSERT INTO guest (username, ip, last_active_date, last_active_time, browser) VALUES ('$gname', '$ip', '$date', '$thetime', '$browser')")
or die(mysql_error());
}
?>

 

That would try to update first. Then, if it affected zero rows, it would insert. Otherwise it wouldn't have to do anything else. Best yet, you wouldn't have to run you (potentially) huge SELECT query.

[wtfsmd]

This would be much more efficient:

 

<?php
$ip = $_SERVER['REMOTE_ADDR'];
$result = mysql_query("UPDATE guest SET username='$gname', last_active_date='$date', last_active_time='$thetime', browser='$browser' WHERE ip='$ip'")
	or die(mysql_error());
if (mysql_affected_rows($result)==0){
mysql_query ("INSERT INTO guest (username, ip, last_active_date, last_active_time, browser) VALUES ('$gname', '$ip', '$date', '$thetime', '$browser')")
or die(mysql_error());
}
?>

 

That would try to update first. Then, if it affected zero rows, it would insert. Otherwise it wouldn't have to do anything else. Best yet, you wouldn't have to run you (potentially) huge SELECT query.

 

This gives off an error:

Warning: mysql_affected_rows(): supplied argument is not a valid MySQL-Link resource in /home/content/t/i/m/timeba/html/globals.php on line 57

[F1Fan]

Sorry, I thought the syntax of that function was the same as another I use. Try this:

 

<?php
$ip = $_SERVER['REMOTE_ADDR'];
mysql_query("UPDATE guest SET username='$gname', last_active_date='$date', last_active_time='$thetime', browser='$browser' WHERE ip='$ip'")
	or die(mysql_error());
if (mysql_affected_rows()==0){
mysql_query ("INSERT INTO guest (username, ip, last_active_date, last_active_time, browser) VALUES ('$gname', '$ip', '$date', '$thetime', '$browser')")
or die(mysql_error());
}
?>

[redarrow]

what about this way mate!

<?php

//database connection

$ip=$_SERVER['REMOTE_ADDR'];

$sql="select from sec where ip='$ip'";

$res=mysql_query($sql)or die(mysql_error());

if(mysql_num_rowa(res)==1){

$sql2="update sec set ip='$ip' where ip='$ip'";
    
$res2=mysql_query($sql)or die(mysql_error());

}else{

$sql3="inset into sec(ip)VALUES($ip)";

$res3=mysql_query($sql)or die(mysql_error());

}

?>

[wtfsmd]

i used:

 

<?php
	$ip=$_SERVER['REMOTE_ADDR'];
	$sql="SELECT * FROM guest WHERE ip='$ip'";
	$res=mysql_query($sql)or die(mysql_error());

	if(mysql_num_rows(res)==1){
		$sql2="UPDATE guest SET ip='$ip' WHERE ip='$ip'";
    		$res2=mysql_query($sql)or die(mysql_error());
	}else{
		$sql3="INSERT INTO guest (ip)VALUES($ip)";
		$res3=mysql_query($sql)or die(mysql_error());
	}
?>

 

And i get the error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/content/t/i/m/timeba/html/globals.php on line 58

[redarrow]

<?php
	$ip=$_SERVER['REMOTE_ADDR'];
	$sql="SELECT * FROM guest WHERE ip='$ip'";
	$res=mysql_query($sql)or die(mysql_error());

	if(mysql_num_rows($res)==1){
		$sql2="UPDATE guest SET ip='$ip' WHERE ip='$ip'";
    		$res2=mysql_query($sql2)or die(mysql_error());
	}else{
		$sql3="INSERT INTO guest (ip)VALUES($ip)";
		$res3=mysql_query($sql3)or die(mysql_error());

[F1Fan]

You're missing the '$' in front of 'res'

[redarrow]

i no sorry lol.....

 

you might want NULL here i think

 

$sql2="UPDATE guest SET ip=NULL WHERE ip='$ip'";

 

<?php
	$ip=$_SERVER['REMOTE_ADDR'];

	$sql="SELECT * FROM guest WHERE ip='$ip'";
	$res=mysql_query($sql)or die(mysql_error());

	if(mysql_num_rows($res)==1){

		$sql2="UPDATE guest SET ip='$ip' WHERE ip='$ip'";
    		       $res2=mysql_query($sql2)or die(mysql_error());

}else{

		$sql3="INSERT INTO guest (ip)VALUES($ip)";
		$res3=mysql_query($sql3)or die(mysql_error());

?>