Auto insert a new record if it does not exist.

[JunkDrawStuff]

I have the following (which is not working)

I have opened the database, that works fine... its handle is $d

  $ok=mysql_query( "SELECT * FROM `{$dbn}`.`ip_table` WHERE `ip`='{$ip}';" , $d) ;

  if( !$ok ) { // INSERT THE ITEM!
    $log = mysql_query("INSERT INTO `{$dbn}`.`ip_table` ( `ip` , `visits` , `last_visit` , `whois` ) VALUES ( '{$ip}', '0', CURRENT_TIMESTAMP , '{$user}' );" , $d );

  }
    // WE NOW UPDATE because by now we should have made one!
    $log = mysql_query("UPDATE `{$dbn}`.`ip_table` SET `visits` = `visits`+1 , `last_visit` = NOW() WHERE `ip` = '{$ip}';", $d );

I want to add an IP address reference to a table so I can count the number of visits. I am trying to weed out web crawlers and web bots, I am reviewing a friends site totals and the site gets crawled allot, so its hard to get a true visitor count. So I thought about logging all traffic and weeding out the web bots and then removing those totals from the site traffic to see whats really going on.

The problem I am having is that the initializing query does not insert a new record when the item does not exist.

If the ip address is not found in ip_table then the result should be a new record is inserted and then that new record is updated. The thing works when I manually put in an ip address of a BOT, it will count it.

What am I missing or doing wrong?

[tirna]

I would do something like this:

 
<?php
 
//check if the ip address exists in the db
$query = 'select * from tblIPaddresses where fldIPaddress = 'some IP address';
//run the query
$rs = mysql_query($query, $conn);
//now either insert or update depending on how many rows were returned in $rs
if(mysql_num_rows($rs) == 1 {  //ip address exists
    $query = 'update tblIPaddresses set fldNumVisits = fldNumVisits+1 where fldIPaddress = 'some ip address';
} else {  //ip address does not exist
    $query = insert into tblIPaddresses (fldIPaddress,fldNumVisits) values ('some ip address',1);
}
//run the query
$rs = mysql_query($query, $conn);
 
?>

Make sure you validate the ip address format to protect against sql injection.

[JunkDrawStuff]

OK, I will have a look at that, as for the IP address, it is not something that is an input but a value from the $_SERVER array.

I was hoping for some kind of UPDATE OR INSERT command that would negate the need for PHP to be involved in anything other than the originator of the query.

[JunkDrawStuff]

Well I tried using it, works a treat, thanks.

 if( mysql_num_rows( $ok ) < 1) { // INSERT THE ITEM!
    $log = mysql_query("INSERT INTO `{$dbn}`.`ip_table` ( `ip` , `visits` , `last_visit` , `whois` ) VALUES ( '{$ip}', '0', CURRENT_TIMESTAMP , '{$user}' );" , $d );

  }

It now puts every address in but thats not an issue at present. I now need to look at weeding out the "user agents" that have slurp, bot, search in the name and only inserting those in to the table...

Any suggestions?

[tirna]

Maybe try parsing the user agent stored in $_SERVER['HTTP_USER_AGENT'] which will contain something like

 
Mozilla/5.0 (compatible; Yahoo! Slurp/3.0; http://help.yahoo.com/help/us/ysearch/slurp)

to weed out the bots.