kashthealien Posted December 5, 2008 Share Posted December 5, 2008 I have a MySQL table which looks like this 1 asd 2 sd 3 sad 4 sdg when I delete the third element it looks like this 1 asd 2 sd 4 sdg How can I reorder this and make it look like 1 asd 2 sd 3 sdg 3 instead of 4? Is there any inbuilt MySQL command? SHould I use PHP ? If so how? Please explain. Thank you! Quote Link to comment https://forums.phpfreaks.com/topic/135704-reorder-mysql-table/ Share on other sites More sharing options...
gevans Posted December 5, 2008 Share Posted December 5, 2008 Why do you want to do this? Quote Link to comment https://forums.phpfreaks.com/topic/135704-reorder-mysql-table/#findComment-707048 Share on other sites More sharing options...
Brian W Posted December 5, 2008 Share Posted December 5, 2008 lol, asking why is funny. "UPDATE `table_name` `key` = 3 WHERE `key` = 4" table_name is your table name and key is the field that has those numbers Quote Link to comment https://forums.phpfreaks.com/topic/135704-reorder-mysql-table/#findComment-707050 Share on other sites More sharing options...
Maq Posted December 5, 2008 Share Posted December 5, 2008 lol, asking why is funny. "UPDATE `table_name` `key` = 3 WHERE `key` = 4" table_name is your table name and key is the field that has those numbers Lol, I think he wants something a little more dynamic... The only way I can think of doing this is to dump your data to a temporary table empty the current one and repopulate the original table from the temp one. It will auto-increment everything back in order. But there's no way to INSERT a record and have it "fill in the gaps" of the auto-increment field, at least not to my knowledge. Quote Link to comment https://forums.phpfreaks.com/topic/135704-reorder-mysql-table/#findComment-707052 Share on other sites More sharing options...
premiso Posted December 5, 2008 Share Posted December 5, 2008 In order to do that I believe you have to delete the table then recreate it and re-populate the data. The id should not matter really, if you are a person who likes it to be in order, I would suggest you get used to it, cause it is not probable if you have 1,000 records and one gets deleted to go through and re-order every single record just to make it 1-1000. Imagine if you had 10,000 records and you deleted 100 records a day, wow thats a load on the server. Quote Link to comment https://forums.phpfreaks.com/topic/135704-reorder-mysql-table/#findComment-707054 Share on other sites More sharing options...
gevans Posted December 5, 2008 Share Posted December 5, 2008 lol, asking why is funny. Haha, I see what you're saying, but if there's no good reason I'd just tell him not to do it Quote Link to comment https://forums.phpfreaks.com/topic/135704-reorder-mysql-table/#findComment-707059 Share on other sites More sharing options...
Maq Posted December 5, 2008 Share Posted December 5, 2008 The id should not matter really, if you are a person who likes it to be in order, I would suggest you get used to it, cause it is not probable if you have 1,000 records and one gets deleted to go through and re-order every single record just to make it 1-1000. Imagine if you had 10,000 records and you deleted 100 records a day, wow thats a load on the server. It sure would be, but, you could always run a cron job late at night to dump and repopulate the tables. kashthealien, what is the purpose of this, if you don't mind? Quote Link to comment https://forums.phpfreaks.com/topic/135704-reorder-mysql-table/#findComment-707062 Share on other sites More sharing options...
Brian W Posted December 5, 2008 Share Posted December 5, 2008 I've thought about this before... there isn't any easy way to do so as there isn't a SQL command to do so and when you start looking at even as little as 100+ records even a server side process of some sort gets ridiculous. "why" is funny to me because whether they be crazy, dumb, or just OCD... it doesn't matter what they will do with it, the challenge of "how" is more important. Quote Link to comment https://forums.phpfreaks.com/topic/135704-reorder-mysql-table/#findComment-707082 Share on other sites More sharing options...
Maq Posted December 5, 2008 Share Posted December 5, 2008 I can't think of a logical scenario to re-order auto-incremented id's... well, crazy, dumb, or just OCD... Quote Link to comment https://forums.phpfreaks.com/topic/135704-reorder-mysql-table/#findComment-707084 Share on other sites More sharing options...
PFMaBiSmAd Posted December 5, 2008 Share Posted December 5, 2008 The reason we always ask why someone wants to do something that is normally not done, is because what they are trying to accomplish might be legitimate for their application, but the actual way of doing it would be to use a different method than what they are asking about. Quote Link to comment https://forums.phpfreaks.com/topic/135704-reorder-mysql-table/#findComment-707090 Share on other sites More sharing options...
Maq Posted December 5, 2008 Share Posted December 5, 2008 The reason we always ask why someone wants to do something that is normally not done, is because what they are trying to accomplish might be legitimate for their application, but the actual way of doing it would be to use a different method than what they are asking about. Hmmm, I see. kashthealien please describe your situation in a detailed manner so we can help you figure out a good solution. Quote Link to comment https://forums.phpfreaks.com/topic/135704-reorder-mysql-table/#findComment-707093 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.