Delete duplicate rows in MySQL using PHP script

Question

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

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?

i cant do this, why not? What happens? You are open to SQL injections and shouldn't be using mysql_ anymore. Also it would be best to have a constraint on user that it must be unique after you fix this. — user3783243
– user3783243, Commented Sep 18, 2020 at 14:45
Warning: mysql_* extension is deprecated as of PHP 5.5.0, and has been removed as of PHP 7.0.0. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API. — Dharman
– Dharman ♦, Commented Sep 18, 2020 at 15:00
Does this answer your question? Why shouldn't I use mysql_* functions in PHP? — Dharman
– Dharman ♦, Commented Sep 18, 2020 at 15:00
Please take a look here: mysqltutorial.org/mysql-delete-duplicate-rows and here also: phoenixnap.com/kb/mysql-remove-duplicate-rows You can do that with sql query even without php — Tikky
– Tikky, Commented Sep 18, 2020 at 18:24

GMB · Accepted Answer · 2020-09-18 14:44:52Z

2

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.

answered Sep 18, 2020 at 14:44

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Delete duplicate rows in MySQL using PHP script

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related