0

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: test@domain.com

user: zeus
code: olympus
email: k@domain.com

user:test
code:123132
email: test@domain.com

user:test
code:987654331
email: test@domain.com

 user: usa
    code:1233333
    email: usa@domain.com
  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: k@domain.com

     user: usa
        code:1233333
        email: usa@domain.com

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.

  1. 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 !

BenTyler
  • 43
  • 1
  • 9

1 Answers1

0

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;
Nishanth Matha
  • 5,993
  • 2
  • 19
  • 28
  • for first answer: "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use." For second answer, when I try to make 'user' a primary key: " #1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key" – BenTyler Feb 22 '15 at 03:08
  • first answer the query works but returns an error, probably something with my sql: "#1093 - You can't specify target table 'logs' for update in FROM clause " – BenTyler Feb 22 '15 at 03:28
  • SQL query: SELECT * FROM `logs` PROCEDURE ANALYSE(); Rows: 4 Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available. Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype tonym_du.logs.id 5 547 1 3 0 0 276.0000 156.7503 SMALLINT(3) UNSIGNED NOT NULL tonym_du.logs.users 517155931 100009168514412 9 15 0 0 79192217133460.1700 89633137.9156 BIGINT(15) UNSIGNED NOT NULL tonym_du.logs.code 2963072786 5504464356 9 10 0 0 217.6501 NULL VARCHAR(228) NOT NULL – BenTyler Feb 22 '15 at 03:37
  • thanks alot, it works :) Now, how I make sure that no more duplicated rows will be in the future? – BenTyler Feb 22 '15 at 11:58
  • Why can't I set that row as a primary key? I think it's because I have the "id" row which is auto increment.. but I can't just delete that row, it's the id row.. – BenTyler Feb 22 '15 at 12:05
  • yeah, as I said (and as you can see in the schema), there is an "id" row which is primary key, but I can't just cancel it or cancel the auto increment since it's the id row.. adds a unique id to each user by his register date, like in every site. " Keyname Type Unique Packed Column Cardinality Collation Null Comment PRIMARY BTREE Yes No id 292 A No" – BenTyler Feb 22 '15 at 12:10
  • try my edited ans I've even included the explanation – Nishanth Matha Feb 22 '15 at 12:29
  • Thanks alot. But there are 2 querys, so I need to make some php if statement, don't I? Or just run those 2 querys together with mysql_query and that's it? I mean, how should insert it into my php code if there are 2 querys.. the system needs to know which query to use – BenTyler Feb 22 '15 at 13:42
  • $query = mysql_query("SELECT * FROM users WHERE user='$user'"); if(mysql_num_rows($query) != 0) { echo "Username already exists"; } else { //proceed with code here } – BenTyler Feb 22 '15 at 14:21
  • Well i'v managed to do it with ON DUPLICATE KEY, thanks alot for all your help :) – BenTyler Feb 22 '15 at 14:49