Re: [PHP] Question: Correcting MySQL's ID colomn when removing an entry
On Sun, Sep 20, 2009 at 12:07:39PM +0430, Parham Doustdar wrote: > Hello there, > I'm guessing that when a row in a MySQL table is removed, the ID colomns of > the rows which come after that row are not changed. For example: > 1 > 2 > 3 > 4 > > Now, if I want to remove the third rows, the ID colomn would be something > like: > 1 > 2 > 4 > > I was wondering if there was a way to fix it through a query, so I wouldn't > have to use a for statement in PHP to fix it? > There's nothing to fix here. I assume you're talking about an ID column which is serialized or "autoincrement". Such columns are not designed to necessarily be "continuously" numbered. If you remove a row, there is simply a gap in the sequence. Your application shouldn't care whether the numbers are continuous or not, though it may care that they are "in order". To process such a table, you'd do your query like: SELECT * FROM mytable ORDER BY id And then process the results with a foreach or for loop. I will say, though, that if your application cares about the ordering of the records, I'd use a different field to implement that, such as "date_added" or "last_name" or something similar. It's best to assume, when dealing with autoincrement fields, that the numbers placed in them are random. They are only designed to be unique within that table, to allow for easy fetching of a single unique record within the table. Autoincrement fields are not designed to be used to order the records in a table. Paul -- Paul M. Foster -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Question: Correcting MySQL's ID colomn when removing an entry
At 12:07 PM +0430 9/20/09, Parham Doustdar wrote: Hello there, I'm guessing that when a row in a MySQL table is removed, the ID colomns of the rows which come after that row are not changed. For example: 1 2 3 4 Now, if I want to remove the third rows, the ID colomn would be something like: 1 2 4 I was wondering if there was a way to fix it through a query, so I wouldn't have to use a for statement in PHP to fix it? Thanks! Parham: This is a common problem with people who are new to databases. The sequence of the index of tables has nothing to do with anything important -- there is no reason to be fearful of, or concerned with, gaps. Gaps are normal and a customary part of the database operation. I often hear managers/clients say "How do we correct that gap?" -- the simple answer is "We don't" and there is no reason to look upon a gap in a sequence as if it needs correcting. Now, if you want to display a sequence of records (like a product listing), there is no problem with you reading all the products in your database, sorting them, and listing them in numerical order -- however, that listing has nothing to do with the index of the table. Similarly, you may even want to list your products in alphabetical order and you can do that too, but again that listing has nothing to do with the index of the table. So in the end, the index in a table has nothing to do with how you store or list your data -- it simply is an internal thing that the database needs to keep track of unique records. My advice, allow the database to use the index as it needs and you use the data as you need. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Question: Correcting MySQL's ID colomn when removing an entry
On Sep 20, 2009, at 3:37 AM, "Parham Doustdar" wrote: Hello there, I'm guessing that when a row in a MySQL table is removed, the ID colomns of the rows which come after that row are not changed. For example: 1 2 3 4 Now, if I want to remove the third rows, the ID colomn would be something like: 1 2 4 I was wondering if there was a way to fix it through a query, so I wouldn't have to use a for statement in PHP to fix it? Thanks! -- --- Contact info: Skype: parham-d MSN: fire_lizard16 at hotmail dot com GoogleTalk: parha...@gmail.com Twitter: PD90 email: parham90 at GMail dot com The quick answer is you don't. it will cause you far more headaches than it's worth. the id is only for uniqueness and linking should have nothing to do with havin any meaning in the data. Bastien Sent from my iPod -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Question: Correcting MySQL's ID colomn when removing an entry
On Sun, 2009-09-20 at 01:06 -0700, Lars Torben Wilson wrote: > On Sun, 20 Sep 2009 12:07:39 +0430 > "Parham Doustdar" wrote: > > > Hello there, > > I'm guessing that when a row in a MySQL table is removed, the ID > > colomns of the rows which come after that row are not changed. For > > example: 1 2 > > 3 > > 4 > > > > Now, if I want to remove the third rows, the ID colomn would be > > something like: 1 > > 2 > > 4 > > > > I was wondering if there was a way to fix it through a query, so I > > wouldn't have to use a for statement in PHP to fix it? > > > > Thanks! > > > > I'm not sure why you would want to do that--it would make things very > hard to keep track of. Once an ID has been assigned to a set of data, > that ID should not change. > > Perhaps if you explained the actual problem you're having, instead of > how you're trying to solve it, it would be easier to offer a possible > solution. There is likely a way to solve it which does not involve > mangling the stored data. > > > Regards, > > Torben > Yeah, the idea of using relational databases like MySQL is so that you can use multiple tables to store information, and link them by a common field, often an id. If you go aorund altering your id's so that they run in order, then you lose any links you had on them that use that field. There is no reason to have them all run in order anyway, unless you're writing some really bad queries. Thanks, Ash http://www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Question: Correcting MySQL's ID colomn when removing an entry
On Sun, 20 Sep 2009 12:07:39 +0430 "Parham Doustdar" wrote: > Hello there, > I'm guessing that when a row in a MySQL table is removed, the ID > colomns of the rows which come after that row are not changed. For > example: 1 2 > 3 > 4 > > Now, if I want to remove the third rows, the ID colomn would be > something like: 1 > 2 > 4 > > I was wondering if there was a way to fix it through a query, so I > wouldn't have to use a for statement in PHP to fix it? > > Thanks! > I'm not sure why you would want to do that--it would make things very hard to keep track of. Once an ID has been assigned to a set of data, that ID should not change. Perhaps if you explained the actual problem you're having, instead of how you're trying to solve it, it would be easier to offer a possible solution. There is likely a way to solve it which does not involve mangling the stored data. Regards, Torben -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
