I am making an application from data import from excel file. In application user may be import several time or different file may be contains same data. But I always need unique data row from other calculation. So I badly need delete Duplicated row from table after importing data from excel file.
For Solve this problem I found couple of solution on web.
Solution:1 If table contain a primary key
Table :
Table :
For Solve this problem I found couple of solution on web.
Solution:1 If table contain a primary key
Table :
create table TableName1 (
ID int(11) NOT NULL AUTO_INCREMENT ,
CODE varchar(20) ,
NAME varchar(100) ,
PRIMARY KEY (ID)
)
Data
ID | CODE | NAME |
1 | A1001 | Mahfuz |
2 | A1002 | Lemon |
3 | A1001 | Mahfuz |
4 | A1002 | Lemon |
5 | A1003 | Babu |
<?php
$conn = mysql_connect("localhost", "mysql_user", "mysql_password");
if (!$conn) {
echo "Unable to connect to DB: " . mysql_error();
exit;
}
if (!mysql_select_db("mydbname")) {
echo "Unable to select mydbname: " . mysql_error();
exit;
}
$sql = "DELETE FROM TableName1
USING TableName1, TableName1 AS vtable
WHERE vtable.id > TableName1.id
AND TableName1.CODE = vtable.CODE
And TableName1.NAME = vtable.NAME";
mysql_query($sql);
$sql_check = "SELECT NAME,
COUNT(NAME) AS NumOccurrences
FROM TableName1
GROUP BY NAME
HAVING ( COUNT(NAME) > 1 )";
$result = mysql_query($sql_check);
if (mysql_num_rows($result) == 0) {
echo "No Duplicate rows found";
}
?>
Solution 2: If table have no primary key Table :
create table TableName1 (
CODE varchar(20) ,
NAME varchar(100)
)
Data
CODE | NAME |
A1001 | Mahfuz |
A1002 | Lemon |
A1001 | Mahfuz |
A1002 | Lemon |
A1003 | Babu |
<?php
$conn = mysql_connect("localhost", "mysql_user", "mysql_password");
if (!$conn) {
echo "Unable to connect to DB: " . mysql_error();
exit;
}
if (!mysql_select_db("mydbname")) {
echo "Unable to select mydbname: " . mysql_error();
exit;
}
$sql1 = " CREATE TEMPORARY TABLE temp_TableName1 as select * from TableName1 group By code,name";
mysql_query($sql1);
$sql2 = " TRUNCATE TABLE TableName1 ";
mysql_query($sql2);
$sql3 = " INSERT INTO TableName1 SELECT * FROM temp_TableName1 ";
mysql_query($sql3);
$sql_check = "SELECT NAME,
COUNT(NAME) AS NumOccurrences
FROM TableName1
GROUP BY NAME
HAVING ( COUNT(NAME) > 1 )";
$result = mysql_query($sql_check);
if (mysql_num_rows($result) == 0) {
echo "No Duplicate rows found";
}
?>