DeathDisease Posted March 8, 2007 Share Posted March 8, 2007 Hello, i'm wondering how to keep a accurate auto increment in MYSQL. For example say I had an ID field "INT" thats auto increment and I have 5 rows: |ID 1| Row 1 |ID 2| Row 2 |ID 3| Row 3 |ID 4| Row 4 |ID 5| Row 5 Now say I delete "Row 4": |ID 1| Row 1 |ID 2| Row 2 |ID 3| Row 3 |ID 5| Row 4 So my question is how do I keep the ID# with the corresponding row#? Is it possible to do this, and how so? If not is their another way to accomplish this? Link to comment https://forums.phpfreaks.com/topic/41702-auto-increment-question/ Share on other sites More sharing options...
AndyB Posted March 8, 2007 Share Posted March 8, 2007 No, it's not possible. Treat auto-increment as if it didn't exist. Do not even think of messing with it. It's an essential part of making database tables relational. Messing with autoincremented values may render your database useless. OK, so what do you really want to do and why? Link to comment https://forums.phpfreaks.com/topic/41702-auto-increment-question/#findComment-202163 Share on other sites More sharing options...
artacus Posted March 8, 2007 Share Posted March 8, 2007 For the love of Ober! Why does every n00b have the insatiable desire to do this? I don't understand it. We get this question about once a week. Its still a bad idea this week. Feel free to check back next week and see if it makes sense then. Seriously though. This isn't what PK's are for. If you feel the need to store this info (it should be calculated on the fly), add another column like 'sort_order' and use that. Link to comment https://forums.phpfreaks.com/topic/41702-auto-increment-question/#findComment-202164 Share on other sites More sharing options...
trq Posted March 8, 2007 Share Posted March 8, 2007 You can't. Incrementing fields are not meant to be relied upon for such consistencies. If you need to rely upon consistent id's your more than likely going about something the wrong way. What are you attempting to achieve? Link to comment https://forums.phpfreaks.com/topic/41702-auto-increment-question/#findComment-202165 Share on other sites More sharing options...
rofl90 Posted March 8, 2007 Share Posted March 8, 2007 Hes attempting to get people to insult him? If you're using auto-increment and randomly deleting row theres no point only use auto-increment i you're not planning on deleting rows AND FGS EXPORT YOUR SQL! Link to comment https://forums.phpfreaks.com/topic/41702-auto-increment-question/#findComment-202170 Share on other sites More sharing options...
Barand Posted March 8, 2007 Share Posted March 8, 2007 how do I keep the ID# with the corresponding row#? You don't. The physical location of a record is irrelevant. Your application should work even if they are scattered randomly throughout the table ID 5 may stay at row 5 but when you add a new record it could take the place of a deleted one, so you then have |ID 1| Row 1 |ID 2| Row 2 |ID 3| Row 3 |ID 6| Row 4 |ID 5| Row 5 If you want the records in ID sequence, use "... ORDER BY ID" in your query. Link to comment https://forums.phpfreaks.com/topic/41702-auto-increment-question/#findComment-202174 Share on other sites More sharing options...
artacus Posted March 8, 2007 Share Posted March 8, 2007 LOL. So now that everyone has had a chance to weigh in on the subject, how do you change the darn field? Link to comment https://forums.phpfreaks.com/topic/41702-auto-increment-question/#findComment-202177 Share on other sites More sharing options...
Barand Posted March 8, 2007 Share Posted March 8, 2007 I don't think we're getting through, are we? You Don't Change It. You Leave It Alone. Once Allocated It Keeps its Value. There are only so many ways say this. Link to comment https://forums.phpfreaks.com/topic/41702-auto-increment-question/#findComment-202183 Share on other sites More sharing options...
jcbarr Posted March 8, 2007 Share Posted March 8, 2007 If you want to change that value, then undo the auto-increment setting on the column. If you want a number the corresponds to the row number then create a nother column in order to do that, one that will be static. Link to comment https://forums.phpfreaks.com/topic/41702-auto-increment-question/#findComment-202212 Share on other sites More sharing options...
DeathDisease Posted March 8, 2007 Author Share Posted March 8, 2007 LoL alright cool. Still new to this and not sure how to go about things quite yet. Link to comment https://forums.phpfreaks.com/topic/41702-auto-increment-question/#findComment-202258 Share on other sites More sharing options...
btherl Posted March 8, 2007 Share Posted March 8, 2007 Wow, 8 posts before you posted again Was your question answered? Link to comment https://forums.phpfreaks.com/topic/41702-auto-increment-question/#findComment-202272 Share on other sites More sharing options...
DeathDisease Posted March 8, 2007 Author Share Posted March 8, 2007 No. In a HTML table I want to keep each table row numbered in a column to the left of the table data, so that table row one would be #1 and table row 2 would be #2... and so on. So that when a row is deleted or added that it will still be at the right count. If that makes ne sense. Link to comment https://forums.phpfreaks.com/topic/41702-auto-increment-question/#findComment-202879 Share on other sites More sharing options...
AndyB Posted March 8, 2007 Share Posted March 8, 2007 Actually what you want is that the first row displayed shows as #1, the second shows as #2 etc. regardless of the record id (physical location in the database). Just add a loop counter and display its value as you display each record. Link to comment https://forums.phpfreaks.com/topic/41702-auto-increment-question/#findComment-202916 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.