fabioisonfire Posted April 28, 2008 Share Posted April 28, 2008 Here's the problem... I deleted a few users from the database, and now, whenever a user registers, they are put into the 'users' table, but get a completely blank ID for the 'userstats' table, which causes all kinds of havoc to break loose. The 'users' section also makes their ID like there are, say, 60 members (the number before I deleted the users), when there is really only, say, 56. Is there any way to somehow... 're-align' the tables so they are both synced again? I'm willing to let someone into my MySQL database to help out. Quote Link to comment Share on other sites More sharing options...
realjumper Posted April 28, 2008 Share Posted April 28, 2008 Are you talking about the auto-incremented id field of the tables? Quote Link to comment Share on other sites More sharing options...
fabioisonfire Posted April 28, 2008 Author Share Posted April 28, 2008 I can't be sure (I'm a bit of a beginner), but that sounds right. Quote Link to comment Share on other sites More sharing options...
realjumper Posted April 28, 2008 Share Posted April 28, 2008 Well, when you enter data into the db, the auto-incremented 'id' field (if that's what you're talking about) will increment by 1. So, if you have (say) 60 entries and they have 'id' numbers 1-60 and you then delete 4 entries, you will be left with 56 entries, right? Okay, if you add a new entry (user), that entry will have an auto-incremented 'id' of 61. The 'id' of the deleted entries will NEVER be used again....so you might have (say) 1-56 showing, and the next entry will be 61 Quote Link to comment Share on other sites More sharing options...
fabioisonfire Posted April 28, 2008 Author Share Posted April 28, 2008 Well, when you enter data into the db, the auto-incremented 'id' field (if that's what you're talking about) will increment by 1. So, if you have (say) 60 entries and they have 'id' numbers 1-60 and you then delete 4 entries, you will be left with 56 entries, right? Okay, if you add a new entry (user), that entry will have an auto-incremented 'id' of 61. The 'id' of the deleted entries will NEVER be used again....so you might have (say) 1-56 showing, and the next entry will be 61 So there is NO way to fix this then? (Other than wiping the entire table.) And if it comes to wiping the table, is there a way to get my users' data back? Quote Link to comment Share on other sites More sharing options...
realjumper Posted April 28, 2008 Share Posted April 28, 2008 So there is NO way to fix this then? (Other than wiping the entire table.) And if it comes to wiping the table, is there a way to get my users' data back? Nope, can't fix that. If the id field is the auto-incremented primary key, then you can't go back and 're-use' deleted id numbers. If you delete the table entirely and re-create it, you will have this same problem again the next time you delete an entry.....so....you need to re-think your table. The best way to save the data if you choose to delete the table is to make a dump of the table data. If you are using phpMyAdmin, then it's easy to do Quote Link to comment Share on other sites More sharing options...
fabioisonfire Posted April 29, 2008 Author Share Posted April 29, 2008 I made a dump, and I unchecked the thing that transfers the auto-increment data, and then renamed it so it'd work with my code, and it still didn't work. What am I doing wrong? Quote Link to comment Share on other sites More sharing options...
realjumper Posted April 29, 2008 Share Posted April 29, 2008 I'm not exactly sure of what you are trying to do here....it seems to me that you have 2 tables , 'users' and 'userstats'. Let's say you have 'John' in your 'users' table, and in the 'userstats' table you have Johns weight of 76kg. How are you matching the John with his weight??? Are you expecting ) John to have an id of (say) 54 (in the users table, and for that to correspond with the id of 54 in the 'userstats' table which contains 76kg?? Quote Link to comment Share on other sites More sharing options...
redarrow Posted April 29, 2008 Share Posted April 29, 2008 all you need to do is when you delete a database entry delete both ids from the two database fields that it.... The database id points to a user, so if you delete one user at id 67 then you need to also delete user id 67 from any other table..... Quote Link to comment Share on other sites More sharing options...
fabioisonfire Posted April 29, 2008 Author Share Posted April 29, 2008 Yeah, realjumper. Would you be willing to go into my MySQL admin and check this out for me? redarrow, it's too late for that. Quote Link to comment Share on other sites More sharing options...
realjumper Posted April 29, 2008 Share Posted April 29, 2008 Yeah, realjumper. Would you be willing to go into my MySQL admin and check this out for me? redarrow, it's too late for that. Sorry, I'm at work at the moment....but redarrow is correct. The only way you're going to fix this is to dump both tables data and then delete the tables. Re-create the tables and insert the data so that it matches. But...this problem will occur again if you're not careful!! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.