Delete duplicated rows from my database + Make sure no more duplications will be added

Question

I know alot of people asked this question before but I didn't find an absloute answer and I saw alot of different answers so i'm confused.

I'v got the following data for example in my sql database:

user: test
code: blablah
email: [email protected]

user: zeus
code: olympus
email: [email protected]

user:test
code:123132
email: [email protected]

user:test
code:987654331
email: [email protected]

 user: usa
    code:1233333
    email: [email protected]

1. I want to delete all the rows which contain the same username. I want to run a query that will check if there are more than one row for each username. if there are 2 or more rows for a certain username, ALL the rows which contain this username will be deleted.

Which means I want the database, in this example, to be in the end

   user: zeus
    code: olympus
    email: [email protected]

     user: usa
        code:1233333
        email: [email protected]

In the end, I want to make sure that there are no duplicated rows / data in my database. I don't want to leave even one row with a duplicated username because the "code" is actually a unique code which is generated with every login session and destroys the previous codes, and I can't know which code is the latest one because I didn't use any "created" row with date() function.

---

2. I want to make sure I won't be needed to clean more duplicated rows in the future, so i want to make sure that with every login/register to my site, the system will run a check if the username is already in the database. If it does.. ONLY his "code" row will be affected and the new code [which will be generated with the login] will replace the old code in the same row which containts the username. If the username isn't in the database, the system will insert all his details to a row. Which means: user, code & email. Next time he'll login, the system will update just his code row in the current row and won't add a new row with the same user & the same email with a new code.

Right now I use the following code:

"INSERT INTO logs (user, code, email) VALUES('$user', '$code', '$email')";  

I'm thinking about changing it to

INSERT INTO logs(user, code, email)VALUES($user,$code,$email)ON DUPLICATE KEY UPDATE    $code=VALUES(code)  

in order to get what i'm aiming for. I need to change the "user" row in my database to a unique row for this.

Someone has suggested me to use the following code:

 "UPDATE `logs` SET `code` = '$code' WHERE `user` = '$user' AND `email` =  '$email'";  

But I don't think it's the right way, because if i'll just use the UPDATE option, what will happend to new users who will want to register? Their data isn't in the database, so there is nothing to update yet, so there will be an error for new users, right?

Thanks alot !

Answer 1

for first question you can use this query:

delete from logs where user IN 
(SELECT * from (select user FROM logs GROUP BY user HAVING COUNT(user) > 1) tempTable);

for second question since you already have primary key you cannot use on duplicate key update on user column. But you can use two queries back to back first on Update and second insert try:

    //this Update query is executed if the user is already there. If the user is not there this query will fail and php will go to next query
    $query1 = update logs set code = $code where user = $user;

   //This query will insert the data only if `user` is not found

    $query2 = insert into logs ($user,$code,$email)
    select user,code,email
    from logs
    where not exists (select * from user where user = $user)
    limit 1;