Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to avoid duplicate data insertion using php and MySql

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 1
    • 0
    • 14.6k
    Comment on it

    This blog will help user learn how to avoid the duplicate entries in the database. Storing duplicate values in the table must be avoided while inserting data in the database table. As these duplicate entries can create many problem in the application. Developers go with many data insertion practice to avoid duplicate entry which are discussed as below:

    1- Remove duplicate values using 'DISTINCT' key in mysql query, if it is present in table.

    2- Check whether the value is present in table or not using PHP and Mysql before inserting data into the table.

    3- We can also avoid duplicate values storing in mysql table while inserting by primary key.


    For Example: Let us take a database named with 'demo' and it consist of a table named with 'employee'. We insert names in employee table. Now we've to check if the values are already present or not in the employee table while we insert.

    Now we try to add name 'Rajeev' in the employee table. Now we will first check if the employee with the same name 'Rajeev' exists in table, which we can check using the below php script in the file Regst.php.

    <?php
    if(isset($_POST['Register']))
    {
    mysql_connect('localhost','root','');
    mysql_select_db('demo');
    $first_name = $_POST['emp_first_name'];
    $resultset_1 = mysql_query("select * from employee where emp_first_name='".$first_name."' ") or die(mysql_error());
    $count = mysql_num_rows($resultset_1);
       if($count == 0)
        {
           $ resultset_2 = mysql_query("INSERT INTO demo.employee VALUES ('".$first_name."')")  or
           die(mysql_error());
        }else{
           echo The user is already present in the employee table ;
        }
    }
    ?>

     

    Now we create the input form registration.html

    <html>
    <body>
    <form method='post' action='#'>
      First Name: <input type='text' name='emp_first_name'>
    <input type='submit' name='Register' value='Submit'>
    </form>
    </body>
    </html>

    Here,

    mysql_query() - execute the query and selects all the data with the same value from the employee table.

    mysql_num_rows() - This mysql function returns count of selected rows.

    If count is 0, it means that there exists no duplicate value in table.

    This way we can avoid duplicate entries in table using PHP and Mysql.


    Other way to avoid duplicate entries when we are inserting multiple values at a time in the table.

    First step is to set a unique key in the table:

    ALTER TABLE employee ADD UNIQUE INDEX(empid, emp_first_name);

     

    Now we decide what we should go for when we get a duplicate entry.

    Shall we:

    IGNORE?

    INSERT IGNORE INTO employee (empid, emp_first_name) VALUES (1, "Sagar"), (1, "Sagar");

     

    Overwrite previous duplicate record?

    INSERT INTO employee (empid, emp_first_name, emp_last_name)

    VALUES (1, "Sagar", "Kumar")

    ON DUPLICATE KEY UPDATE ( emp_last_name = 'Kumar')

    INSERT INTO employee (empid, emp_first_name, emp_last_name)

    VALUES (1, "Sagar", "Kant")

    ON DUPLICATE KEY UPDATE ( emp_last_name = 'Kant')

     

    Update some counter?

    INSERT INTO employee (empid, emp_first_name)

    VALUES (1, "Sagar"), (1, "Sagar")

    ON DUPLICATE KEY UPDATE (empidcount = empidcount + 1)


    Other way to avoid the insertion of duplicate entries

    Suppose db dance, that consists of tables videos and categories where table videos has columns video_title and dance_category . The combination of dance_category and video_title is set to be unique in database level. So basically even if php tries to insert duplicate entries database will not allow.


    We can also use REPLACE INTO instead of INSERT INTO

    REPLACE works the same way like INSERT, but if there is an entry with same value with for a UNIQUE INDEX OR a PRIMARY KEY than the old row is deleted before the new row is inserted.

    REPLACE is a MySQL extension to the SQL standard. REPLACE either inserts or first deletes and than inserts the entry.

    For more information click the link http://dev.mysql.com/doc/refman/5.7/en/replace.html

    How to avoid duplicate data insertion using php and MySql

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: