www.webdeveloper.com
Results 1 to 5 of 5

Thread: Auto insert a new record if it does not exist.

  1. #1
    Join Date
    Jan 2010
    Location
    Great Britian
    Posts
    21

    Auto insert a new record if it does not exist.

    I have the following (which is not working)

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

    Code:
      $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?

  2. #2
    Join Date
    Mar 2010
    Posts
    2,803
    I would do something like this:

    Code:
     
    <?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.
    Last edited by tirna; 04-04-2010 at 04:06 AM.

  3. #3
    Join Date
    Jan 2010
    Location
    Great Britian
    Posts
    21
    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.

  4. #4
    Join Date
    Jan 2010
    Location
    Great Britian
    Posts
    21
    Well I tried using it, works a treat, thanks.
    Code:
     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?

  5. #5
    Join Date
    Mar 2010
    Posts
    2,803
    Maybe try parsing the user agent stored in $_SERVER['HTTP_USER_AGENT'] which will contain something like
    Code:
     
    Mozilla/5.0 (compatible; Yahoo! Slurp/3.0; http://help.yahoo.com/help/us/ysearch/slurp)
    to weed out the bots.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles