[SOLVED] Php SQL IF help..

[E3pO]

Hello! Thanks for the fast reply on my last question.. Very helpful!

 

What I need help on now is how to add an if statement to my insert code so that it only inserts if the ip is nowhere in the database..

 

For instance, in a super omfg easy form.. if ip is != anything in database then insert, otherwise do nothing at all.

<?php
   function newview($ip){
      $q = "INSERT INTO iplist (ip) VALUES ('$ip')";
  $result = mysql_query($q, $this->connection);
      return $result;
   }
?>

[Maq]

You can try to select that ip and if the query returns and results (> 0) then it exists and return false, if not, proceed with the insertion:

 

function newview($ip){
   $q2 = "SELECT ip FROM iplist WHERE ip = '$ip'";
   $result = mysql_query($q2);
   if(mysql_num_rows($result) > 0)
   {
      return false;
   }
   else
   {
      $q = "INSERT INTO iplist (ip) VALUES ('$ip')";
      $result = mysql_query($q, $this->connection);
      return $result;
   }
}
?>

 

BYW, what datatype are you storing IP's in the database as?

[E3pO]

BYW, what datatype are you storing IP's in the database as?

 

char(15)

utf8_bin

 

Also, not sure if it made a difference but i added the , $this->connection to the end of the query.

<?php
$q2 = "SELECT ip FROM iplist WHERE ip = '$ip'";
$result = mysql_query($q2, $this->connection);
?>

 

Final working code, thank you very much

 

<?php
function newview($ip){
//Code help from PHPFreaks.com
//Special Thanks to Maq for super fast help and reply!
   $q2 = "SELECT ip FROM iplist WHERE ip = '$ip'";
   $result = mysql_query($q2, $this->connection);
   if(mysql_num_rows($result) > 0)
   {
      return false;
   }
   else
   {
      $q = "INSERT INTO iplist (ip) VALUES ('$ip')";
      $result = mysql_query($q, $this->connection);
      return $result;
   }
}
      function views(){
      if($this->views3 < 0){
         $q = "SELECT id FROM iplist";
         $result = mysql_query($q, $this->connection);
         $this->views3 = mysql_numrows($result);
      }
      return($this->views3);
   }
?>

 

SQL CODE:

 

CREATE TABLE IF NOT EXISTS `iplist` (
  `id` int(11) NOT NULL auto_increment,
  `ip` char(15) character set utf8 collate utf8_bin NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

[Maq]

FYI: You can store IPs as unsigned INTs so they can be a little more dynamic (ie - search for a range).  You have to use these functions though to convert them:

 

INET_ATON and INET_NTOA.

 

Please mark as solved if so, thanks