Question: Correcting MySQL"s ID colomn when removing an entry

------=_NextPart_000_02CF_01CA39EA.F2E0CDF0
Content-Type: text/plain;
charset="windows-1256"
Content-Transfer-Encoding: quoted-printable

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!

--=20
---
Contact info:
Skype: parham-d
MSN: fire_lizard16 at hotmail dot com
GoogleTalk: parham90@gmail.com
Twitter: PD90
email: parham90 at GMail dot com
------=_NextPart_000_02CF_01CA39EA.F2E0CDF0--

Re: Question: Correcting MySQL"s ID colomn when removing anentry

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

Re: Question: Correcting MySQL"s ID colomn when removing anentry

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: 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: parham90@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: Question: Correcting MySQL"s ID colomn when removing anentry

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: Question: Correcting MySQL"s ID colomn when removing an

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