CODEWITHSUNDEEP
phpmysqlsql
user3282833
user3282833

Reputation: 85

how to delete row in mysql when duplicated with other row?

how to delete row in mysql when duplicated with other row ?

i want to create page delete_duplicate.php

when i load pagr delete_duplicate.php

it's will be delete row id 3 and 4

because row id 3 duplicate row id 2 and row id 4 duplicate row id 1

How to write that code [php] ?

Before load page delete_duplicate.php------table : member

 ____________________________________
|__likeid__|__userid__|__product_id__|
|____1_____|__100261__|____aaaaaa____|
|____2_____|__100005__|____bbbbbb____|
|____3_____|__100005__|____bbbbbb____|
|____4_____|__100261__|____aaaaaa____|
|____5_____|__100261__|____cccccc____|

After load page delete_duplicate.php------table : member

 ____________________________________
|__likeid__|__userid__|__product_id__|
|____1_____|__100261__|____aaaaaa____|
|____2_____|__100005__|____bbbbbb____|
|____5_____|__100261__|____cccccc____|

Upvotes: 0

Views: 129

Answers (6)

Hovo
Hovo

Reputation: 790

You can delete all the duplicates by this mysql query, if you know how to execute it from the php.

DELETE FROM `member`
WHERE `member`.`likeid` NOT IN (
    SELECT `temp_member`.`min_likeid`
    FROM (
        SELECT MIN(`likeid`) AS `min_likeid`
        FROM `member`
        GROUP BY `member`.`userid`, `member`.`product_id`


    ) AS `temp_member`)

Upvotes: 1

Nayan Dabhi
Nayan Dabhi

Reputation: 1006

You should issue the following statement in order to delete the duplicate records from your table and achieve uniqueness of product_id:

DELETE e1 FROM member e1, member e2 WHERE e1.product_id = e2.product_id AND e1.likeid > e2.likeid;

Upvotes: 2

oBo
oBo

Reputation: 992

EDIT: This only works if you have a primary key of "id", swap id to "likeid"

You could do it with php

something like

$config['db'] = array(
    'host'          => 'localhost',
    'username'      => 'root',
    'password'      => 'root',
    'dbname'        => 'dbname'
    );

$db = new PDO("mysql:host={$config['db']['host']};dbname={$config['db']['dbname']}",
              $config['db']['username'], $config['db']['password']);

$query = "SELECT * FROM member";

$stmt = $db->prepare($query);
$members = $stmt->fetch(PDO::FETCH_ASSOC);

Now you have all the members in $members , so you can loop over them

$idsToDelete= [];
$array = [];

foreach($members as $member)
{

    if(in_array($member['product_id'], $array)){
        $idsToDelete[] = $member['id'];
    }

    $array[] = $member['product_id'];
}

To remove the duplicates

$removeQuery  = "DELETE FROM from members WHERE id in (:idsToDelete)";
$stmt = $db->prepare($removeQuery);
$stmt->bindParam(':idsToDelete', $idsToDelete);
$stmt->execute();

Upvotes: 0

Rahul
Rahul

Reputation: 77876

If you want to achieve it through SQL then below query can help

DELETE FROM member m1, member m2 WHERE m1.likeid > m2.likeid 
AND m1.userid = m2.userid 
AND m1.product_id = m2.product_id

Upvotes: 1

Wesley De Keirsmaeker
Wesley De Keirsmaeker

Reputation: 1025

You can do the check before creating the database entry (best pratice):

  • Make a unique constraint of the combination userid/product_id.

You can delete duplicate entries after they are entered in the database:

  • Load your table into your php code with the SQL command SELECT DISTINCT
  • Empty your table
  • Write the php entries back to your table.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can fix this problem permanently by creating a unique index on user_id and product_id:

create unique index member_userid_productid on member(userid, productid);

For your example, you can delete the duplicates by using delete with join:

delete m from members m join
              (select m.user_id, m.product_id, min(m.like_id) as minlike_id
               from members m
               group by m.user_id, m.product_id
              ) tokeep
              on m.user_id = tokeep.user_id and
                 m.product_id = tokeep.product_id and
                 m.like_id > tokeep.minlike_id;

Upvotes: 3

Related Questions