CODEWITHSUNDEEP
phpmysqlsqlduplicatessql-delete
Mohamed Elsobky
Mohamed Elsobky

Reputation: 71

Delete duplicate rows in MySQL using PHP script

I need a PHP function that deletes duplicate rows from MySQL database table. For example, I have a table like this:

table rows

I tried the following without success:

$find = mysql_query("SELECT * FROM users");
while ($row = mysql_fetch_assoc($find))
{
    $find_1 = mysql_query("SELECT * FROM users ");
    if (mysql_num_rows($find_1) > 0) {
        mysql_query("DELETE FROM users WHERE ID =$row[num]");
    }

I need to delete this duplicate rows and Keep only one of them only using PHP (and not my SQL database). Is there a way to do this?

Upvotes: 0

Views: 1287

Answers (1)

GMB
GMB

Reputation: 222682

You can do this with a single query, without a php loop (which, obviously, does not do what you want here):

delete t
from mytable t
inner join (select user, min(num) num from mytable group by user) t1
    on t.user = t1.user and t.num > t1.num

This deletes rows that have the same user, while retaining the row with the smallest num.

Upvotes: 2

Related Questions