IP Referrer and Hits not recorded in mysql

ip referer

[chrissim]

hi,

Can someone tell why my script below don't recorded site url and ip? I have just tested them and initiallly it just only recorded only 1 site url together with my own IP but if i tested again with different PC with other ip it don't recorded anything and never increment to 2

 $ip = $_SERVER['REMOTE_ADDR'];

$sel = mysql_query("SELECT website, hits, date, ip FROM refstats WHERE website = '$url' && date = '$time' AND '".$ip."'");

  if (mysql_num_rows($sel) > 0) {

   $site = mysql_fetch_array($result, MYSQL_ASSOC);

   if ($site['website'] != 'www.') {

    if ($site['website'] != 'www.mysite.com') {

     $hits = $site['hits'] + 1;

     $website = $site['website'];

     $query = "UPDATE refstats SET hits = $hits WHERE website = '$website' && date = '$time' AND '".$ip."'";

     $result = mysql_query($query);

    }

Please help

Thanks

Chris

[Matthew1980]

Hi there chrissim,

Try this:-

$query = "UPDATE `refstats` SET `hits` = '".$hits."' WHERE `website` = '".$website."' AND `date` = '".$time."' AND '".$ip."'";

Though I am not too convinced as the query is correct syntax..

and:-

$sel = mysql_query("SELECT `website`, `hits`, `date`, `ip` FROM `refstats` WHERE `website` = '".$url."' AND `date` = '".$time."' AND '".$ip."'");

Again not too sure on the sql syntax here..

Thats all I can see on a quick glance at the code, but I think as you may need to put the mysql_fetch_array() in a while loop if you are expecting to loop through each instance returned ie:-

while($site = mysql_fetch_array($result, MYSQL_ASSOC)){
//process data
}

Always use a while when looping through data.

Cheers,
MRb

[jatar_k]

I'm with Matthew1980 not sure it's right

this bit
WHERE `website` = '".$url."' AND `date` = '".$time."' AND '".$ip."'"

might work as
WHERE `website` = '".$url."' AND `date` = '".$time."' AND ip='".$ip."'"

though ip might not be the name of your column, the same goes for both the select and update queries

you can echo the query, that helps you know if it is getting constructed properly, you can also add an 'or die' to the queries while in development to help debug

$query = "UPDATE `refstats` SET `hits` = '".$hits."' WHERE `website` = '".$website."' AND `date` = '".$time."' AND '".$ip."'";
echo $query;
$result = mysql_query($query) or die('<p>update error: ' . mysql_error());

[Matthew1980]

Hi there chrissim,

it don't recorded anything and never increment to 2

It won't increment to 2 because you are UPDATING the row(s) that would be affected, to get an increment you would need an INSERT statement, with something like 'id' set as auto_increment when setting up the table.

UPDATE literally updates the specified fields that you name after the SET instruction.

Hope I understood you correctly there ;)

Jatar_k: Thanks, I should have read that post better! I knew that something wasn't right.

Cheers,
MRb

[chrissim]

Thanks guys

But the results is still the same the script unable to record new site url or ip with the new different REMOTE_ADDR when assessing my site.

[Matthew1980]

Hi there chrissim,

What are you trying to achieve? I'm guessing that you are trying to add IP addresses to your db if they don't already exist in your db... am I anywhere close?

I am assuming that there is a working open DB connection call here, this is why I haven't declared one in the functions...


This is something similar to what you need to do:-

$sel = mysql_query("SELECT * FROM `refstats` WHERE `ip_address` = '".$ip."'");

if (mysql_num_rows($sel) > 0){

while($site = mysql_fetch_array($sel)){

if ($site['website'] != 'www.') {

if ($site['website'] != 'www.mysite.com') {

$hits = $site['hits'] + 1;

$website = $site['website'];

$query = "UPDATE `refstats` SET `hits` = '".$hits."' WHERE `website` = '".$website."' AND `date` = '".$time."' AND `ip_address` = '".$ip."'";

$result = mysql_query($query) or die(mysql_error());//use this to error check the query

}

if($result){
echo "Update was successful";
}else{
echo "There was a problem updating";
exit;
}

}
else{
echo "No IP address matches";
exit;
}

Bearing in mind that this is typed On-The-Fly and is only a guide really, I'm still not too sure on the syntax and have guessed the name of the column

Hope you see how it should work from this.

Cheers,
MRb

[rocknbil]

WHERE `website` = '".$url."' AND `date` = '".$time."' AND ip='".$ip."'"

Have you echoed this, is $time actually a date?

Does the $time variable generate precisely the same and match the DB value?

Is the `date` field a datetime format?

If either of the first two is false or the third is true, the condition will only match if you post from the exact same IP at the exact same time, which, all things considered, is nearly impossible.

Even so, try removing the date query from the where, see if it works, something may be hinky with that. If not, echo your statements and copy them into the command line (or phpMyAdmin, whatever.)