How to avoid duplicate data insertion using php and MySql

58.3k How to avoid duplicate data in

35.6k How to Make Forgot Password in

34.2k Check Email Already Exists wit

33.8k What should be Valid OAuth red

29.9k Deleting Caches in Codeigniter

26.9k Best 5 Lightweight PHP Framewo

prev 1 2 3 … 361 next

How to avoid duplicate data insertion using php and MySql

@pushpendra.rawat

Follow Recommendations Offline Message

over 8 years ago
- 0
- 1
  Positive Vote
- 0
  Negative Vote
- 0
  Save Favourite
- 1
- 0
- 1
- 0
- 58.3k
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
Tags
Avoid duplicate value Avoid insertion of duplicate value in php and MySql
Comment on it