check duplicate value based on category selected and avoid inserting into mysql

Question

I have a select option of categories fetched from mysql and text box to enter value for that category and submit button to enter that value in database as follows.

HTML Page

Now after selecting category i enter into text box some value for that particular category only and hit submit button. But before i insert, i want to check for duplicate value for that category, so when i hit submit button it should check database for values in db for category selected and if value is already there than show error else insert into db.

My code

index.php

       <form action="action.php" enctype="multipart/form-data" method="post">


       <div class="row">

        <div >

         <label>Category</label>
          <br>


        <select name="cat" id="cat" style="width:100%;height:0.27in" required >
      <option  value="0000" selected="selected">Select cat</option><?php
       include ('connection.php');
        $qry_sel_dept="select * from general_master where name_cd = 'Ptype' ";
        $qry_sel_dept_exe=mysql_query($qry_sel_dept);

        while($fetch=mysql_fetch_array($qry_sel_dept_exe)){
        ?>
                <option  value="<?php echo $fetch['c_id'];?>" ><?php echo $fetch['category'];?></option>

              <?php }

              ?></select>
       <input type="text" name="te" id="te" />
        <input type="submit" name="submit" id="submit" />

action.php

      <?php
     include('./../connection.php');
     if(isset($_POST['submit'])){
     $cat=$_REQUEST['cat'];
     $te=$_REQUEST['te'];

      $sqlc= mysql_query("INSERT INTO general_master set category='".$cat."' , name= '".$te."' " );

      }
      ?>

mysql Table contains

c_id
category
name

Can it be done using javascript and ajax? How?

Any help will be greatly appreciated. Thanks in advance

Keep it simple, start by adding the check to your action.php — RiggsFolly
Write a "SELECT COUNT(*) as knt WHERE category='$cat' AND name= '$te'" query using those 2 columns in the search criteria. If you get a count of 1 it already exists, if 0 returned it does not exist. SQL 102 — RiggsFolly
Recommendation: use the constraint UNIQUE to the field you don't want to have duplicated values. You don't need to check if there are duplicated values as long as the key is ready to avoid duplications by system: dev.mysql.com/doc/refman/5.7/en/constraint-primary-key.html — Marcos Pérez Gude
insert into table_name set ...??, is it any valid insertion query? — Murad Hasan

Murad Hasan Murad Hasan · Accepted Answer · 2016-02-15T10:10:13

You may do this using a compound query, a query where it check the category is in database or not, if not then insert.

See the example:

include('./../connection.php');
if(isset($_POST['submit'])){
    $cat = $_REQUEST['cat'];
    $te = $_REQUEST['te'];

    $sql = "INSERT INTO general_master
    (category, name)
     SELECT * FROM (SELECT '$cat', '$te') AS tmp
    WHERE NOT EXISTS (
        SELECT category FROM general_master WHERE category = '$cat'
    )";
    $sqlc = mysqli_query($con, $sql);
}

$con is the connection. This may help you. let me know it is work or not.

check duplicate value based on category selected and avoid inserting into mysql

1 Answers