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, Sep 18 '20 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](https://www.php.net/manual/en/book.mysqli.php) or [PDO_MySQL](https://www.php.net/manual/en/book.pdo.php) extension should be used. See also the [MySQL API Overview](https://www.php.net/manual/en/mysqlinfo.api.choosing.php) for further help while choosing a MySQL API. — Dharman, Sep 18 '20 at 15:00
Does this answer your question? [Why shouldn't I use mysql\_\* functions in PHP?](https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — Dharman, Sep 18 '20 at 15:00
Please take a look here: https://www.mysqltutorial.org/mysql-delete-duplicate-rows/ and here also: https://phoenixnap.com/kb/mysql-remove-duplicate-rows You can do that with sql query even without php — Tikky, Sep 18 '20 at 18:24

score 2 · Answer 1 · answered Sep 18 '20 at 14:44

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.

Delete duplicate rows in MySQL using PHP script

1 Answers1