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>  

6 comments:

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

    ReplyDelete
  2. How to enter multiple value? I have one primary key which I want user unable to enter duplicate value for primary key. Is there a way that can I achieve it? I am beginner using PHP language.

    ReplyDelete
  3. I have one primary key that i dont want user to enter duplicate value for primary data. My form consist of multiple values.

    ReplyDelete
  4. you can share source code??
    i try its not work.

    ReplyDelete
  5. I have two table in database one is employees & second is calltracker.

    When i am click on post button so data is inserted into calltacker table.

    I want ignore if data is already available into calltracker table and change header location if data already exits in employees table.


    please send your email id i am unable to post message with my code

    ReplyDelete
  6. great video thanks
    how to do if we have multiple inputs (table multi rows )
    ( using datatables for example)

    ReplyDelete