Saturday, 28 May 2016

PHP MySQL Insert record if not exists in table


In this PHP web development tutorial we will get knowledge on how to use mysql insert query for checking data already inserted or not. For this things we have use insert query with sub query with where condition and not exits. We have make simple insert query with select sub query with where not exists to check data already inserted or not in insert query. In old days, if you want to enter only unique data in particular column, then at that time before executing insert data query, you have first write select query for checking this data is present or not, but now we use WHERE NOT EXISTS and write sub query for this data is available in table or not. By this things we have to write only one query for checking data is already inserted or not. For more details, you can see video of this post.

Souce Code

Database


 --  
 -- Table structure for table `brand`  
 --  
 CREATE TABLE IF NOT EXISTS `brand` (  
  `brand_id` int(11) NOT NULL AUTO_INCREMENT,  
  `brand_name` varchar(250) NOT NULL,  
  PRIMARY KEY (`brand_id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;  
 --  
 -- Dumping data for table `brand`  
 --  

data_already_inserted.php


 <?php  
 $connect = mysqli_connect("localhost", "root", "", "zzz");  
 $messsage = '';  
 if(isset($_POST["add"]))  
 {  
      if(!empty($_POST["brand"]))  
      {  
           $sql = "  
                INSERT INTO brand (brand_name)  
                SELECT '".$_POST["brand"]."' FROM brand  
                WHERE NOT EXIST(  
                 SELECT brand_name FROM brand WHERE brand_name = '".$_POST["brand"]."'  
                ) LIMIT 1  
           ";  
           if(mysqli_query($connect, $sql))  
           {  
                $insert_id = mysqli_insert_id($connect);  
                if($insert_id != '')  
                {  
                     header("location:data_already_inserted.php?inserted=1");  
                }  
                else  
                {  
                     header("location:data_already_inserted.php?already=1");  
                }  
           }  
      }  
      else  
      {  
           header("location:data_already_inserted.php?required=1");  
      }  
 }  
 if(isset($_GET["inserted"]))  
 {  
      $message = "Brand inserted";  
 }  
 if(isset($_GET["already"]))  
 {  
      $message = "Brand Already inserted";  
 }  
 if(isset($_GET["required"]))  
 {  
      $message = "Brand Name Required";  
 }  
 ?>  
 <!DOCTYPE html>  
 <html>  
      <head>  
           <title>Webslesson Tutorial | MySQL Insert record if not exists in table</title>  
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>  
           <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
           <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>  
      </head>  
      <body>  
           <br />  
           <div class="container" style="width:500px;">  
                <label class="text-danger">  
                <?php  
                if($message!= '')  
                {  
                     echo $message;  
                }  
                ?>  
                </label>  
                <h3 align="">Insert Data</h3><br />                 
                <form method="post">  
                     <label>Enter Brand Name</label>  
                     <input type="text" name="brand" class="form-control" />  
                     <br />  
                     <input type="submit" name="add" class="btn btn-info" value="Add" />  
                </form>  
           </div>  
           <br />  
      </body>  
 </html>  

1 comment:

  1. How to do same thing when inserting multiple values ?

    ReplyDelete