Pragmatically changing a "Record Number"

[Jason Pruim]

Hi Everyone,

I think after I get this question answered, I can stop asking for
awhile since my project will be done, at least until the users say
"What happened to XYZ????" then I'll ask again :)

I asked on a MySQL list about "Resetting a auto increment filed" so
that there arn't any gaps in the record number.

So to say it another way, I have a table that has 900 records in it,
I've added 3 records, but then deleted 2 of those which puts the
actual record count at 901 but my auto increment field starts at 904
on the next insert.

Is there away with PHP that I can pragmatically change that value to
the total records in the database more so then a representation of
the actual record number?


--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
***@raoset.com

[Jay Blanchard]

[snip]
Is there away with PHP that I can pragmatically change that value to
the total records in the database more so then a representation of
the actual record number?
[/snip]

1. Changing the values in an auto-increment column is just Bad[tm],
especially if you are using it as a unique index.

2. You can get a row count using mysql_num_rows()

3. Ask yourself, "Is it important to keep the auto-increment field
contiguous?"

4. You can always display a row number with PHP that is contiguous, but
not representative of the actual column.

[Jason Pruim]

Post by Jay Blanchard
[snip]
Is there away with PHP that I can pragmatically change that value to
the total records in the database more so then a representation of
the actual record number?
[/snip]
1. Changing the values in an auto-increment column is just Bad[tm],
especially if you are using it as a unique index.

It's not yet, and I don't plan to have it be part of a unique index,
unless I absolutely need to onceI look into being able to search...

Post by Jay Blanchard
2. You can get a row count using mysql_num_rows()
3. Ask yourself, "Is it important to keep the auto-increment field
contiguous?"

The main reason for changing it is I do currently have an option to
sort by record number, although, if I add a Record number in php, but
still have it sort based off of the record number stored in the
database, it would still sort right, but also look right...

Post by Jay Blanchard
4. You can always display a row number with PHP that is contiguous, but
not representative of the actual column.

I think you may have made me go a different way in my thinking. Thank
you :)


--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
***@raoset.com

[Jim Lucas]

Post by Jay Blanchard
3. Ask yourself, "Is it important to keep the auto-increment field
contiguous?"

The main reason for changing it is I do currently have an option to sort
by record number, although, if I add a Record number in php, but still
have it sort based off of the record number stored in the database, it
would still sort right, but also look right...

Are you thinking mysql reuses auto-increment number? If so, your mistaken.

MySQL will not reuse an auto-increment unique value.

say you have an empty table with an auto-increment column.

You add 5 row

your next id to be used is 6.

if you delete id # 2 and 3

the next id to be used will still be 6 not 2

it does not go back and fill in the holes/gaps

[Jason Pruim]

Post by Jim Lucas

Post by Jason Pruim

Post by Jay Blanchard
3. Ask yourself, "Is it important to keep the auto-increment field
contiguous?"

The main reason for changing it is I do currently have an option
to sort by record number, although, if I add a Record number in
php, but still have it sort based off of the record number stored
in the database, it would still sort right, but also look right...

Are you thinking mysql reuses auto-increment number? If so, your mistaken.

No, I am aware that it doesn't reuse the auto increment numbers.

Post by Jim Lucas
MySQL will not reuse an auto-increment unique value.
say you have an empty table with an auto-increment column.
You add 5 row
your next id to be used is 6.
if you delete id # 2 and 3
the next id to be used will still be 6 not 2

And what I'm looking for is away to take rows 4 and 5 and move them
to rows 2 and 3 so the next record inserted would be row 4 :)

Post by Jim Lucas
it does not go back and fill in the holes/gaps
--
Jim Lucas
"Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them."
Twelfth Night, Act II, Scene V
by William Shakespeare

--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
***@raoset.com

[Bill Guion]

I don't think you really want to do that, either. You don't need a
field in the database to accomplish what you are trying to do.
Execute your query to select the records you want, sorting them into
what ever order you want. When you display the records on your web
page, add a PHP variable, say $row_number, which starts at 1, and
increments by one for each row you display.

-----===== Bill =====-----

Post by Jason Pruim
And what I'm looking for is away to take rows 4 and 5 and move them
to rows 2 and 3 so the next record inserted would be row 4 :)

Post by Jim Lucas
it does not go back and fill in the holes/gaps
--
Jim Lucas
"Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them."
Twelfth Night, Act II, Scene V
by William Shakespeare

--
Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com

[Graham Cossey]

If I've understood correctly your record/Id number relates to a message/post.

Let's say the PHP list has message numbers and this post is number
456. If I have a list of posts and sort them by user and provide the
user with a numbered list of posts from 1 to N in user sequence
there's VERY little chance that if I was to quote message number 47
(or whatever number it had been assigned in the sorted list) that
someone else would be able to reliably find the post using number 47,
but give then number 456 that is always linked to this post and this
post only then they'll always be able to find it, until it's deleted
;-)

Help?

Graham

Post by Bill Guion
I don't think you really want to do that, either. You don't need a
field in the database to accomplish what you are trying to do.
Execute your query to select the records you want, sorting them into
what ever order you want. When you display the records on your web
page, add a PHP variable, say $row_number, which starts at 1, and
increments by one for each row you display.
-----===== Bill =====-----

Post by Jason Pruim
And what I'm looking for is away to take rows 4 and 5 and move them
to rows 2 and 3 so the next record inserted would be row 4 :)

Post by Jim Lucas
it does not go back and fill in the holes/gaps
--
Jim Lucas
"Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them."
Twelfth Night, Act II, Scene V
by William Shakespeare

--
Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com

--
Reality is the leading cause of stress, for those in touch with it.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[tedd]

Hi to the original poster:

Snip -- a lot of discussion

Use the following code at your own peril.

$dbQuery = "ALTER TABLE $dbtable ";
$dbQuery .= "DROP id, ";
$dbQuery .= "ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,";
$dbQuery .= "AUTO_INCREMENT = 1";
$result = mysql_query($dbQuery) or die("Could not renumber dB
$dbQuery" . mysql_error());

The reason for not wanting to care about the auto_increment id is
that it is something that the database uses and really should not be
changed. If you want to have a sequential record number, then add
that field and alter it as you will, but leave the internal workings
of database alone.

However, if you wish not to understand how all that works, then use
the code above -- it will renumber your auto_increment id leaving no
gaps.

Cheers,

tedd

[brian]

Post by tedd
Snip -- a lot of discussion
Use the following code at your own peril.
$dbQuery = "ALTER TABLE $dbtable ";
$dbQuery .= "DROP id, ";
$dbQuery .= "ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,";
$dbQuery .= "AUTO_INCREMENT = 1";
$result = mysql_query($dbQuery) or die("Could not renumber dB $dbQuery"
. mysql_error());
The reason for not wanting to care about the auto_increment id is that
it is something that the database uses and really should not be changed.
If you want to have a sequential record number, then add that field and
alter it as you will, but leave the internal workings of database alone.
However, if you wish not to understand how all that works, then use the
code above -- it will renumber your auto_increment id leaving no gaps.

At the risk of turning this into a truly marathon thread ...

I don't think altering the id is necessary at all. The solution the OP
seems to be looking for is to (as Graham has said) count off the rows as
they are being printed using a variable. Using a table for clarity:

<?php
$sql = 'SELECT id, title FROM foo';
$result = mysql_query($sql);
$count = 0;
while (++$count && ($row = $result->fethchRow()))
{
?>
<tr>
<td>
row #<?= $count ?>
</td>
<td>
<a href="foo.php?id=<?= $row['id'] ?>"><?= $row['title'] ?></a>
</td>
</tr>
<?php
}

It seems that all this is about is displaying a rowcount. The ids can be
anything at all and it'll still look spiffy.

brian

[tedd]

Post by brian

Post by tedd
Snip -- a lot of discussion
Use the following code at your own peril.
$dbQuery = "ALTER TABLE $dbtable ";
$dbQuery .= "DROP id, ";
$dbQuery .= "ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,";
$dbQuery .= "AUTO_INCREMENT = 1";
$result = mysql_query($dbQuery) or die("Could not renumber dB
$dbQuery" . mysql_error());
The reason for not wanting to care about the auto_increment id is
that it is something that the database uses and really should not
be changed. If you want to have a sequential record number, then
add that field and alter it as you will, but leave the internal
workings of database alone.
However, if you wish not to understand how all that works, then use
the code above -- it will renumber your auto_increment id leaving
no gaps.

At the risk of turning this into a truly marathon thread ...
I don't think altering the id is necessary at all. The solution the
OP seems to be looking for is to (as Graham has said) count off the
rows as they are being printed using a variable. Using a table for
<?php
$sql = 'SELECT id, title FROM foo';
$result = mysql_query($sql);
$count = 0;
while (++$count && ($row = $result->fethchRow()))
{
?>
<tr>
<td>
row #<?= $count ?>
</td>
<td>
<a href="foo.php?id=<?= $row['id'] ?>"><?= $row['title'] ?></a>
</td>
</tr>
<?php
}
It seems that all this is about is displaying a rowcount. The ids
can be anything at all and it'll still look spiffy.
brian

How is that contrary to what I said?

Cheers,

tedd

[Chris]

Post by tedd

Post by brian

Post by tedd
Snip -- a lot of discussion
Use the following code at your own peril.
$dbQuery = "ALTER TABLE $dbtable ";
$dbQuery .= "DROP id, ";
$dbQuery .= "ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,";
$dbQuery .= "AUTO_INCREMENT = 1";
$result = mysql_query($dbQuery) or die("Could not renumber dB
$dbQuery" . mysql_error());
The reason for not wanting to care about the auto_increment id is
that it is something that the database uses and really should not be
changed. If you want to have a sequential record number, then add
that field and alter it as you will, but leave the internal workings
of database alone.
However, if you wish not to understand how all that works, then use
the code above -- it will renumber your auto_increment id leaving no
gaps.

At the risk of turning this into a truly marathon thread ...
I don't think altering the id is necessary at all. The solution the OP
seems to be looking for is to (as Graham has said) count off the rows
<?php
$sql = 'SELECT id, title FROM foo';
$result = mysql_query($sql);
$count = 0;
while (++$count && ($row = $result->fethchRow()))
{
?>
<tr>
<td>
row #<?= $count ?>
</td>
<td>
<a href="foo.php?id=<?= $row['id'] ?>"><?= $row['title'] ?></a>
</td>
</tr>
<?php
}
It seems that all this is about is displaying a rowcount. The ids can
be anything at all and it'll still look spiffy.
brian

How is that contrary to what I said?

You are actually changing the id's in the database.

Brian's example is making up an id to display (much like the excel row
numbers) rather than actually changing the id in the database.

[tedd]

Post by Chris

Post by tedd
Snip -- a lot of discussion
Use the following code at your own peril.
$dbQuery = "ALTER TABLE $dbtable ";
$dbQuery .= "DROP id, ";
$dbQuery .= "ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,";
$dbQuery .= "AUTO_INCREMENT = 1";
$result = mysql_query($dbQuery) or die("Could not renumber dB
$dbQuery" . mysql_error());
The reason for not wanting to care about the auto_increment id is
that it is something that the database uses and really should not
be changed. If you want to have a sequential record number, then
add that field and alter it as you will, but leave the internal
workings of database alone.
However, if you wish not to understand how all that works, then
use the code above -- it will renumber your auto_increment id
leaving no gaps.

-snip-
How is that contrary to what I said?

You are actually changing the id's in the database.
Brian's example is making up an id to display (much like the excel
row numbers) rather than actually changing the id in the database.

Please read what I said above, If you do, then you will see that I
advised against altering the index and recommend setting up another
field for displaying a record sequence.

Cheers,

tedd

[brian]

Post by tedd

Post by Chris

Post by tedd
How is that contrary to what I said?

You are actually changing the id's in the database.
Brian's example is making up an id to display (much like the excel row
numbers) rather than actually changing the id in the database.

Please read what I said above, If you do, then you will see that I
advised against altering the index and recommend setting up another
field for displaying a record sequence.

It seemed to me that what you were suggesting was to either alter the
existing id or to create a second auto_increment field. Isn't there
already an auto_increment field in the table? I thought that that was
what this was all about: an increment that has gaps.

In any case, i can't see how that would help considering that, should
any of the rows then be deleted, we're back to the same problem: the
auto_increment field will have a gap. That's why the simplest way to
deal with this is to increment a counter var in the presentational
logic, given that this counter is for presentational purposes only and
is unnecessary to relate to any specific row. That's what the existing
id column is for (however "out of order" it may be).

brian

[tedd]

Post by brian

Post by tedd

Post by Chris

Post by tedd
How is that contrary to what I said?

You are actually changing the id's in the database.
Brian's example is making up an id to display (much like the excel
row numbers) rather than actually changing the id in the database.

Please read what I said above, If you do, then you will see that I
advised against altering the index and recommend setting up another
field for displaying a record sequence.

It seemed to me that what you were suggesting was to either alter
the existing id or to create a second auto_increment field. Isn't
there already an auto_increment field in the table? I thought that
that was what this was all about: an increment that has gaps.
In any case, i can't see how that would help considering that,
should any of the rows then be deleted, we're back to the same
problem: the auto_increment field will have a gap. That's why the
simplest way to deal with this is to increment a counter var in the
presentational logic, given that this counter is for presentational
purposes only and is unnecessary to relate to any specific row.
That's what the existing id column is for (however "out of order" it
may be).
brian

Arrggg. No, I did not say create another auto_increment field -- I said:

Quote
The reason for not wanting to care about the auto_increment id is
that it is something that the database uses and really should not be
changed. If you want to have a sequential record number, then add
that field and alter it as you will, but leave the internal workings
of database alone.
Un-quote

Now, I do not know why what I said seemed to mean something different
to you, but my guess is that you didn't read it thoroughly.

As for the "gap" problem, there is no gap problem if you create
another field for record number and alter it to your liking (NOTE: I
did not say AUTO_INCREMENT). If you add/delete a record, then adjust
the "field for record number" accordingly. You don't have to adjust
the database's index to handle gaps.

I don't see the problem here other than misunderstanding what's been said.

Cheers,

tedd

[brian]

Post by tedd
Quote
The reason for not wanting to care about the auto_increment id is that
it is something that the database uses and really should not be changed.
If you want to have a sequential record number, then add that field and
alter it as you will, but leave the internal workings of database alone.
Un-quote
Now, I do not know why what I said seemed to mean something different to
you, but my guess is that you didn't read it thoroughly.

My bad. I see, now, that what you meant was, "This is how to re-set your
EXISTING auto_increment, AND, here's something else you can do." Yes, i
was confused about your intention.

Post by tedd
As for the "gap" problem, there is no gap problem if you create another
field for record number and alter it to your liking (NOTE: I did not say
AUTO_INCREMENT). If you add/delete a record, then adjust the "field for
record number" accordingly. You don't have to adjust the database's
index to handle gaps.

Well, yes, that would work also but is horribly inefficient because the
*entire table* must be altered any time a row is deleted.

Post by tedd
I don't see the problem here other than misunderstanding what's been said.

Well, not seperating data and presentation would be another problem,
IMO. What if the client decides that Roman notation is cooler? You have
to ask yourself what this 'counting column' is doing in your database.
Let PHP/Python/XSL/whatever take care of numbering the rows.

brian

[tedd]

Post by brian

Post by tedd
As for the "gap" problem, there is no gap problem if you create
I did not say AUTO_INCREMENT). If you add/delete a record, then
adjust the "field for record number" accordingly. You don't have to
adjust the database's index to handle gaps.

Well, yes, that would work also but is horribly inefficient because
the *entire table* must be altered any time a row is deleted.

"horribly inefficient"?

Renumbering anything is pretty quick these days. To me, things that
are "horribly inefficient" are also slow. So, I don't agree. If I
remember correctly, I can even renumber a 100K item dB auto_increment
index in less than one second -- but I wouldn't recommend it. I think
that's pretty quick.

Post by brian

Post by tedd
I don't see the problem here other than misunderstanding what's been said.

Well, not seperating data and presentation would be another problem,
IMO. What if the client decides that Roman notation is cooler? You
have to ask yourself what this 'counting column' is doing in your
database. Let PHP/Python/XSL/whatever take care of numbering the
rows.
brian

No one said that what was in the dB had to be presented in any form,
but I do have clients who have items in their dBs and want to see a
record number as they pursue their records -- it's only natural.

As for maintaining a record number in a dB I don't see any conflict
whatsoever in separating data and presentation. After all, what's in
your dB should be data and not presentation (however -- I have found
exceptions to this in practical situations when dealing with clients
-- they like adding <b> tags to their presentation via their CMS).

Presentation is made during presentation, obviously, by using css and
proper html and pulling data from your dB to assemble presentation.
If the client wants Roman notation, it's a simple process to provide
that. There is no "separating data and presentation" in this issue.

Cheers,

tedd

[brian]

Post by tedd

Post by brian
Well, yes, that would work also but is horribly inefficient because
the *entire table* must be altered any time a row is deleted.

"horribly inefficient"?
Renumbering anything is pretty quick these days. To me, things that are
"horribly inefficient" are also slow. So, I don't agree. If I remember
correctly, I can even renumber a 100K item dB auto_increment index in
less than one second -- but I wouldn't recommend it. I think that's
pretty quick.

It may be just fine in your case, but from a DB design standpoint it
most certainly is not efficient. Why re-order the entire table for
something like this? Altering an entire table because one row has been
deleted suggests to me that the schema needs to revisited.

Post by tedd

Post by brian
Well, not seperating data and presentation would be another problem,
IMO. What if the client decides that Roman notation is cooler? You
have to ask yourself what this 'counting column' is doing in your
database. Let PHP/Python/XSL/whatever take care of numbering the rows.

No one said that what was in the dB had to be presented in any form, but
I do have clients who have items in their dBs and want to see a record
number as they pursue their records -- it's only natural.

But this column you're suggesting isn't a "record number" in the
ProductId, PLU, serial # sense, though, is it? One would certainly never
select for it, would they? A particular value would be only *very
tenuously* related to its row, and only until the next time some row is
deleted. So why store it in the row at all?

Post by tedd
As for maintaining a record number in a dB I don't see any conflict
whatsoever in separating data and presentation. After all, what's in
your dB should be data and not presentation (however -- I have found
exceptions to this in practical situations when dealing with clients --
they like adding <b> tags to their presentation via their CMS).

It's not about confilcting anything and it's not a presentational issue
in the same sense as having HTML markup in some TEXT field. The issue is
that you want to store a column that is completely unnecessary save for
displaying it. It's a value that should be left to whatever mechanism it
is that's doing the displaying.

Post by tedd
Presentation is made during presentation, obviously, by using css and
proper html and pulling data from your dB to assemble presentation. If
the client wants Roman notation, it's a simple process to provide that.

So now you want to rewrite your triggers to use Roman notation? I wasn't
aware that MySQL had such terrific Roman numeral math support.

Post by tedd
There is no "separating data and presentation" in this issue.

I don't buy that. Doing it that way is attaching unnecessary
presentation-specific baggage to your data. The column is only as
relational as it was the last time it changed. That is, it was pointing
to a completely different record then. This isn't a "separating data and
presentation" issue?

brian

[tedd]

Post by brian

Post by tedd
Renumbering anything is pretty quick these days. To me, things that
are "horribly inefficient" are also slow. So, I don't agree. If I
remember correctly, I can even renumber a 100K item dB
auto_increment index in less than one second -- but I wouldn't
recommend it. I think that's pretty quick.

It may be just fine in your case, but from a DB design standpoint it
most certainly is not efficient. Why re-order the entire table for
something like this? Altering an entire table because one row has
been deleted suggests to me that the schema needs to revisited.

From a technical standpoint, I agree with you. It's far better to let
the records fall as they may according to the sort, whatever that may
be.

However, we (or at least I) often work with clients who don't see
that. Instead they see records that they place into their database
that hold information about their widgets and they like to see a
record number associated with their widget. And, when they add a new
widget record, they want to see that count increased and when they
delete a widget record they want to see it gone and a gap, where they
can renumber at their will.

This makes no difference to me, I can do anything they want. But the
point is that customers usually not don't know the finer points to
what "should" or "should-not" be done, but rather they way they think
things should be done. Understand? After all, it's their business --
not ours.

Our charge is to provide them with as much correct information as
they can absorb and then do just what they want beyond that.

Believe me, arguing with clients about how things should be done has
it's limits. At some point, you just have to listen and do what they
want in spite of what is optimal.

Post by brian

Post by tedd
Presentation is made during presentation, obviously, by using css
and proper html and pulling data from your dB to assemble
presentation. If the client wants Roman notation, it's a simple
process to provide that.

So now you want to rewrite your triggers to use Roman notation? I
wasn't aware that MySQL had such terrific Roman numeral math support.

I rewrite stuff all the time to make the presentation exactly what
the client expects. Roman numeral math support is really a no brainer
to create -- that's comp 101 sruff.

Post by brian

Post by tedd
There is no "separating data and presentation" in this issue.

I don't buy that. Doing it that way is attaching unnecessary
presentation-specific baggage to your data. The column is only as
relational as it was the last time it changed. That is, it was
pointing to a completely different record then. This isn't a
"separating data and presentation" issue?

We are disagreeing if having a record number is necessary or not?
From my perspective, if it is left up to me -- it's unnecessary and
my dB's don't have any. However, if the client wants it, then the
client get's it and it then becomes necessary.

As for relational, I'm not talking about a relational dB field, but
rather a simple record that the client can imagine. Relational fields
are used to present the record, they certainly would not be involved
in any renumbering schemes -- don't you see what I am talking about?

Cheers,

tedd

[brian]

Post by brian
It may be just fine in your case, but from a DB design standpoint it
most certainly is not efficient. Why re-order the entire table for
something like this? Altering an entire table because one row has been
deleted suggests to me that the schema needs to revisited.

...
However, we (or at least I) often work with clients who don't see that.
Instead they see records that they place into their database that hold
information about their widgets and they like to see a record number
associated with their widget. And, when they add a new widget record,
they want to see that count increased and when they delete a widget
record they want to see it gone and a gap, where they can renumber at
their will.

You are confusing a product ID with this index number. They are very
much not the same thing. A product ID (PLU, serial #, whatever) should
not change. This index does change, any time a row is removed from the
database. How can you suggest that this index can be "associated with
their widget" if the value can change at any time? There's no one-to-one
relationship between the widget and this index. At least, not over the
life of the widget's record in the database.

If your client wants a widget number, then, by all means, provide one.
But if you're going to rely on a figure that is liable to change at the
drop of a hat your database career will soon be over.

Again: the only relation this index contributes is destroyed just as
soon as row is removed. So why bother? An entire table should not need
to be re-ordered every time a row is removed in order to satisfy an
ill-conceived schema.

This makes no difference to me, I can do anything they want. But the
point is that customers usually not don't know the finer points to what
"should" or "should-not" be done, but rather they way they think things
should be done. Understand? After all, it's their business -- not ours.
Our charge is to provide them with as much correct information as they
can absorb and then do just what they want beyond that.
Believe me, arguing with clients about how things should be done has
it's limits. At some point, you just have to listen and do what they
want in spite of what is optimal.

This is utterly beside the point. So, give your widgets a product ID.
That's obvious. But, why would you want all of your widgets to assume a
new product ID just because one widget has been taken out of production?
Try to explain that to the client when absolutely nothing operates
correctly because none of the widgets has the correct ID.

Again: this index you are proposing cannot be a product ID because it
changes all the time.

Post by brian
I don't buy that. Doing it that way is attaching unnecessary
presentation-specific baggage to your data. The column is only as
relational as it was the last time it changed. That is, it was
pointing to a completely different record then. This isn't a
"separating data and presentation" issue?

We are disagreeing if having a record number is necessary or not? From
my perspective, if it is left up to me -- it's unnecessary and my dB's
don't have any. However, if the client wants it, then the client get's
it and it then becomes necessary.

I think we are disagreeing with fundamental tenets of database schema
design. But whatever ...

As for relational, I'm not talking about a relational dB field, but
rather a simple record that the client can imagine. Relational fields
are used to present the record, they certainly would not be involved in
any renumbering schemes -- don't you see what I am talking about?

No

brian

[tedd]

Post by brian

Post by tedd

Post by brian
It may be just fine in your case, but from a DB design standpoint
it most certainly is not efficient. Why re-order the entire table
for something like this? Altering an entire table because one row
has been deleted suggests to me that the schema needs to revisited.

...
However, we (or at least I) often work with clients who don't see
that. Instead they see records that they place into their database
that hold information about their widgets and they like to see a
record number associated with their widget. And, when they add a
new widget record, they want to see that count increased and when
they delete a widget record they want to see it gone and a gap,
where they can renumber at their will.

You are confusing a product ID with this index number. They are very
much not the same thing. A product ID (PLU, serial #, whatever)
should not change. This index does change, any time a row is removed
from the database. How can you suggest that this index can be
"associated with their widget" if the value can change at any time?
There's no one-to-one relationship between the widget and this
index. At least, not over the life of the widget's record in the
database.

No, the problem is not that I am confusing widget numbers, but rather
your definition of what widget numbers should be doesn't meet the
needs of the client. You have one note in your songbook.

The problem here is that you're being argumentative instead of trying
to understand that not everyone is limited to your perspective. I've
understood everything that you've said, and agree with most of it,
but you fail to acknowledge a single statement I've made. This is
very similar to how we started this thread in the first place when
you completely misread something I wrote and then turned it all
around to your liking. Apparently, you are doing it again.

The client I have, who wants his record numbers to work the way I
described, is a photographer. When he works with his db of pictures
he wants to see record numbers as he steps through his listing. If he
see's something he wants to edit in his database, then he wants write
down that record number so that he can go back and find it again
during that specific editing session. Likewise, if he wants to remove
a picture from his from his database, then it he wants it to be gone
now.

He understands, and doesn't care, that the next time he travels
through his database that the numbers above his last deletion have
changed. In fact, I don't even have to keep the numbers in a field in
his database -- instead I could easily use the creation data-stamp
for a sort and present his pictures to him that way. But, he wants
that field number there, so that he can continue his editing later,
if he decides to quit -- he wants his current editing session to
remain "as-is" until he changes it -- so the field is there for him.

The point is -- this is an example of a reference number that the
client wants and uses successfully. Who am I to argue with him. In
fact, who is anyone to argue with something that works for him? You?
You want to impose your view of how he should work? Keep in mind,
this is the client and they say, and pay for, what they want.

I think you'll find in life that not all aspects of dealing with
clients fall nicely into the way you think things should be done.
It's probably best for you to get used to it now than later.

Enough said on this subject, besides I don't think you're reading
what I write correctly anyway.

Cheers,

tedd

[brian]

Post by tedd

Post by brian
You are confusing a product ID with this index number. They are
very much not the same thing. A product ID (PLU, serial #,
whatever) should not change. This index does change, any time a row
is removed from the database. How can you suggest that this index
can be "associated with their widget" if the value can change at
any time? There's no one-to-one relationship between the widget and
this index. At least, not over the life of the widget's record in
the database.

No, the problem is not that I am confusing widget numbers, but rather
your definition of what widget numbers should be doesn't meet the
needs of the client. You have one note in your songbook.

I have not once "defined" what a widget number, product code, whatever
should be. You're imagining things now. There LARGE_NUMBER of formats
that a "widget number" can have. I couldn't care less how you (or your
client) chooses to do so.

Post by tedd
The problem here is that you're being argumentative instead of trying
to understand that not everyone is limited to your perspective. I've
understood everything that you've said, and agree with most of it,
but you fail to acknowledge a single statement I've made. This is
very similar to how we started this thread in the first place when
you completely misread something I wrote and then turned it all
around to your liking. Apparently, you are doing it again.

Why are you getting all pissy now? I certainly haven't been. Have i not
been including enough smileys or something?

Post by tedd
The client I have, who wants his record numbers to work the way I
described, is a photographer. When he works with his db of pictures
he wants to see record numbers as he steps through his listing. If he
see's something he wants to edit in his database, then he wants write
down that record number so that he can go back and find it again
during that specific editing session. Likewise, if he wants to remove
a picture from his from his database, then it he wants it to be gone
now.

Well, if your client needs to print out something that relates back to
the record, print out the primary key. Print out the PLU.. Print out the
[insert UNIQUE_VALUE here].

Better yet: design the app so your client doesn't have to get out a
pencil. But i digress ...

Post by tedd
He understands, and doesn't care, that the next time he travels
through his database that the numbers above his last deletion have
changed. In fact, I don't even have to keep the numbers in a field in
his database

So why all the fuss about having to keep this problem-child "index"
field in the table, then? If it doesn't have to be in there then why are
you insisting on including it?

Post by tedd
-- instead I could easily use the creation data-stamp for a sort and
present his pictures to him that way. But, he wants that field
number there, so that he can continue his editing later, if he
decides to quit -- he wants his current editing session to remain
"as-is" until he changes it -- so the field is there for him.
The point is -- this is an example of a reference number that the
client wants and uses successfully. Who am I to argue with him. In
fact, who is anyone to argue with something that works for him? You?
You want to impose your view of how he should work? Keep in mind,
this is the client and they say, and pay for, what they want.

Not one thing that you've described here comes anywhere close to making
a case for designing a database schema in such a manner. All of the
above can be accomplished without having to re-order your table each
time a record is removed.

Post by tedd
I think you'll find in life that not all aspects of dealing with
clients fall nicely into the way you think things should be done.
It's probably best for you to get used to it now than later.

You're being pissy again. It really reflects poorly on you and, i
suspect, it's keeping you from truly understanding what it is that i'm
trying to help you with.

Post by tedd
Enough said on this subject, besides I don't think you're reading
what I write correctly anyway.

I read it twice ("correctly", even!). Mostly because your stubborness
fascinates me. Like a moth to a flame, really.

brian :-)

[tedd]

Post by brian
You're being pissy again. It really reflects poorly on you and, i
suspect, it's keeping you from truly understanding what it is that
i'm trying to help you with.

Post by tedd
Enough said on this subject, besides I don't think you're reading
what I write correctly anyway.

I read it twice ("correctly", even!). Mostly because your
stubborness fascinates me. Like a moth to a flame, really.

Well then, I apologize for my perceived "pissyness" -- it was not
intentional, but rather replying as I felt proper.

As for me understanding, I do.

As for help, I'm not asking for any.

As for this, thread, let's let it drop.

Cheers,

tedd

[Graham Cossey]

Surely we're into basic database design. If you have an auto_increment
record key that needs changing from time to time then you've designed
the database incorrectly. Use a 'normal' key field such as
"Product_Id" and have your application generate the number.

Simple.

No?

Post by tedd

Post by brian

Post by tedd
Renumbering anything is pretty quick these days. To me, things that
are "horribly inefficient" are also slow. So, I don't agree. If I
remember correctly, I can even renumber a 100K item dB
auto_increment index in less than one second -- but I wouldn't
recommend it. I think that's pretty quick.

It may be just fine in your case, but from a DB design standpoint it
most certainly is not efficient. Why re-order the entire table for
something like this? Altering an entire table because one row has
been deleted suggests to me that the schema needs to revisited.

From a technical standpoint, I agree with you. It's far better to let
the records fall as they may according to the sort, whatever that may
be.
However, we (or at least I) often work with clients who don't see
that. Instead they see records that they place into their database
that hold information about their widgets and they like to see a
record number associated with their widget. And, when they add a new
widget record, they want to see that count increased and when they
delete a widget record they want to see it gone and a gap, where they
can renumber at their will.
This makes no difference to me, I can do anything they want. But the
point is that customers usually not don't know the finer points to
what "should" or "should-not" be done, but rather they way they think
things should be done. Understand? After all, it's their business --
not ours.
Our charge is to provide them with as much correct information as
they can absorb and then do just what they want beyond that.
Believe me, arguing with clients about how things should be done has
it's limits. At some point, you just have to listen and do what they
want in spite of what is optimal.

Post by brian

Post by tedd
Presentation is made during presentation, obviously, by using css
and proper html and pulling data from your dB to assemble
presentation. If the client wants Roman notation, it's a simple
process to provide that.

So now you want to rewrite your triggers to use Roman notation? I
wasn't aware that MySQL had such terrific Roman numeral math support.

I rewrite stuff all the time to make the presentation exactly what
the client expects. Roman numeral math support is really a no brainer
to create -- that's comp 101 sruff.

Post by brian

Post by tedd
There is no "separating data and presentation" in this issue.

I don't buy that. Doing it that way is attaching unnecessary
presentation-specific baggage to your data. The column is only as
relational as it was the last time it changed. That is, it was
pointing to a completely different record then. This isn't a
"separating data and presentation" issue?

We are disagreeing if having a record number is necessary or not?
From my perspective, if it is left up to me -- it's unnecessary and
my dB's don't have any. However, if the client wants it, then the
client get's it and it then becomes necessary.
As for relational, I'm not talking about a relational dB field, but
rather a simple record that the client can imagine. Relational fields
are used to present the record, they certainly would not be involved
in any renumbering schemes -- don't you see what I am talking about?
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

[Stephen]

Post by Jason Pruim
So to say it another way, I have a table that has
900 records in it,
I've added 3 records, but then deleted 2 of those
which puts the
actual record count at 901 but my auto increment
field starts at 904
on the next insert.
Is there away with PHP that I can pragmatically
change that value to
the total records in the database more so then a
representation of
the actual record number?

Some database concepts:

The autoincrement feature is to provide a unique "key"
for the record. It does not provide an "order". Many
tables have more than one "order".

Usually a different field or field determines the
order(s). It usually has an index.

To provide a row number, based on some order, you need
a field for this. Whenever a field is deleted, you
would need to repopulate the fields in each record
after the deleted record in the database.

I saw a nested SQL query that did this once, but my
SQL is not good enough to try to illustrate.

Stephen

[James Ausmus]

Post by Stephen

Post by Jason Pruim
So to say it another way, I have a table that has
900 records in it,
I've added 3 records, but then deleted 2 of those
which puts the
actual record count at 901 but my auto increment
field starts at 904
on the next insert.
Is there away with PHP that I can pragmatically
change that value to
the total records in the database more so then a
representation of
the actual record number?

The autoincrement feature is to provide a unique "key"
for the record. It does not provide an "order". Many
tables have more than one "order".
Usually a different field or field determines the
order(s). It usually has an index.
To provide a row number, based on some order, you need
a field for this. Whenever a field is deleted, you
would need to repopulate the fields in each record
after the deleted record in the database.
I saw a nested SQL query that did this once, but my
SQL is not good enough to try to illustrate.

Fairly easy - depending on your version of MySQL - if you are 5.0+,
then the following will do it:

SELECT @rownum:=@rownum+1 rownum, t.* FROM (SELECT @rownum:=0) r, myTable t;

(Quoted from a post by Mark Malakanov on April 30 2006 1:42pm at
http://dev.mysql.com/doc/refman/5.0/en/user-variables.html)

-James

Post by Stephen
Stephen
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[Jason Pruim]

Post by Stephen

Post by Jason Pruim
So to say it another way, I have a table that has
900 records in it,
I've added 3 records, but then deleted 2 of those
which puts the
actual record count at 901 but my auto increment
field starts at 904
on the next insert.
Is there away with PHP that I can pragmatically
change that value to
the total records in the database more so then a
representation of
the actual record number?

The autoincrement feature is to provide a unique "key"
for the record. It does not provide an "order". Many
tables have more than one "order".
Usually a different field or field determines the
order(s). It usually has an index.
To provide a row number, based on some order, you need
a field for this. Whenever a field is deleted, you
would need to repopulate the fields in each record
after the deleted record in the database.

Which is exactly what I am trying to figure out :) in excel this
would be the same as clearing the info in a row and then sorting it
to take out the blank lines :)

Post by Stephen
I saw a nested SQL query that did this once, but my
SQL is not good enough to try to illustrate.

I'll keep looking, I'm sure there is away to do it, but playing with
the auto incrementing column may not be the best idea...

Post by Stephen
Stephen
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
***@raoset.com

[brian]

Post by Jason Pruim
Hi Everyone,
I think after I get this question answered, I can stop asking for
awhile since my project will be done, at least until the users say
"What happened to XYZ????" then I'll ask again :)
I asked on a MySQL list about "Resetting a auto increment filed" so
that there arn't any gaps in the record number.
So to say it another way, I have a table that has 900 records in it,
I've added 3 records, but then deleted 2 of those which puts the actual
record count at 901 but my auto increment field starts at 904 on the
next insert.
Is there away with PHP that I can pragmatically change that value to
the total records in the database more so then a representation of the
actual record number?

Do you mean *programmatically*? As the others have pointed out, what
you're suggesting would not be very pragmatic at all.

In any case, if what you're after is an accurate method to get the
number of records you have, do not rely on max(your_auto_inc_field).
Instead, use count(your_primary_key) or mysql_num_rows($result) or
$result->numRows() or whatever suits the method you're using to access
the DB.

[Jason Pruim]

Post by Jason Pruim
I think after I get this question answered, I can stop asking for
awhile since my project will be done, at least until the users say
"What happened to XYZ????" then I'll ask again :)
I asked on a MySQL list about "Resetting a auto increment filed" so
that there arn't any gaps in the record number.
So to say it another way, I have a table that has 900 records in it,
I've added 3 records, but then deleted 2 of those which puts the
actual record count at 901 but my auto increment field starts at 904
on the next insert.
Is there away with PHP that I can pragmatically change that value to
the total records in the database more so then a representation of
the actual record number?

Can you do this?
Sure.
update whatever set ID_field = 901 where ID_field = 904
Of course, if that 904 is in some OTHER table, then you MUST update
that other table at the same time, in an ACID transation, to be sure
you don't get your relationships all kerflummoxed.

Currently there is only the 1 table and it is going to stay that way...

SHOULD you do this?
NOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO!!!
The ID key field of a record should not have any explicit meaning.
If you try to re-number your records so it does, it will just cause
you grief and give you nothing useful in return.
If you actualy have a table where there *IS* an ordering of the
records which is meaningful, and it really SHOULD be 1 to N, then add
another field "rank" (or somesuch) and manipulate that independent of
the ID field, which you should never care about its actual number.

Which is what I have figured out from the people on this list, I have
decided to not play with the auto increment field, but the need I'm
looking for hasn't changed, just the method.

in excel this would be the same as clearing the info in a row and
then sorting it to take out the blank lines to get an accurate count
of the total records.

To explain WHY this is, would be way bigger than an email that's
already off-topic for PHP, but they should have told you on the MySQL
list.
Or not, as it's probably in their FAQ which you should have read.
If nothing else, the hassle of keeping all those IDs sorted out is a
TON of extra code that serves little purpose, and a single tiny
mistake can make a pig's breakfast of your entire database. Bad Idea.

No one has but it that plainly to me yet, and they are just
suggesting ways to do it more then a don't do it type approach :)


--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
***@raoset.com

[Richard Lynch]

Post by Jason Pruim
I think after I get this question answered, I can stop asking for
awhile since my project will be done, at least until the users say
"What happened to XYZ????" then I'll ask again :)
I asked on a MySQL list about "Resetting a auto increment filed" so
that there arn't any gaps in the record number.
So to say it another way, I have a table that has 900 records in it,
I've added 3 records, but then deleted 2 of those which puts the
actual record count at 901 but my auto increment field starts at 904
on the next insert.
Is there away with PHP that I can pragmatically change that value to
the total records in the database more so then a representation of
the actual record number?

Can you do this?

Sure.

update whatever set ID_field = 901 where ID_field = 904

Of course, if that 904 is in some OTHER table, then you MUST update
that other table at the same time, in an ACID transation, to be sure
you don't get your relationships all kerflummoxed.

SHOULD you do this?

NOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO!!!

The ID key field of a record should not have any explicit meaning.

If you try to re-number your records so it does, it will just cause
you grief and give you nothing useful in return.

If you actualy have a table where there *IS* an ordering of the
records which is meaningful, and it really SHOULD be 1 to N, then add
another field "rank" (or somesuch) and manipulate that independent of
the ID field, which you should never care about its actual number.

To explain WHY this is, would be way bigger than an email that's
already off-topic for PHP, but they should have told you on the MySQL
list.

Or not, as it's probably in their FAQ which you should have read.

If nothing else, the hassle of keeping all those IDs sorted out is a
TON of extra code that serves little purpose, and a single tiny
mistake can make a pig's breakfast of your entire database. Bad Idea.

[Bastien Koert]

Please don't do that...autonumber should not be relied for anything other than a unique row identifier. It should NOT matter to the application what that value is as long as it unique. If you need a count of the number of records, do a query (select count(*) from table...)

bastien> To: php-***@lists.php.net> From: ***@raoset.com> Date: Wed, 29 Aug 2007 13:49:02 -0400> Subject: [PHP] Pragmatically changing a "Record Number"> > Hi Everyone,> > I think after I get this question answered, I can stop asking for > awhile since my project will be done, at least until the users say > "What happened to XYZ????" then I'll ask again :)> > I asked on a MySQL list about "Resetting a auto increment filed" so > that there arn't any gaps in the record number.> > So to say it another way, I have a table that has 900 records in it, > I've added 3 records, but then deleted 2 of those which puts the > actual record count at 901 but my auto increment field starts at 904 > on the next insert.> > Is there away with PHP that I can pragmatically change that value to > the total records in the database more so then a representation of > the actual record number?> > > --> > Jason Pruim> Raoset Inc.> Technology Manager> MQC Specialist> 3251 132nd ave> Holland, MI, 49424> www.raoset.com> ***@raoset.com> >
_________________________________________________________________
Explore the seven wonders of the world
http://search.msn.com/results.aspx?q=7+wonders+world&mkt=en-US&form=QBRE

[Stut]

Post by Jason Pruim
Hi Everyone,

Hi Dr Jason.

Post by Jason Pruim
I think after I get this question answered, I can stop asking for awhile
since my project will be done, at least until the users say "What
happened to XYZ????" then I'll ask again :)
I asked on a MySQL list about "Resetting a auto increment filed" so that
there arn't any gaps in the record number.
So to say it another way, I have a table that has 900 records in it,
I've added 3 records, but then deleted 2 of those which puts the actual
record count at 901 but my auto increment field starts at 904 on the
next insert.
Is there away with PHP that I can pragmatically change that value to the
total records in the database more so then a representation of the
actual record number?

What are you actually trying to achieve? Why do you need all records to
have a sequential number? Ignore how you're going to do it, just tell us
why you think you need this, because I've never come across a reason to
need this.

-Stut

[M. Sokolewicz]

Post by Stut

Post by Jason Pruim
Hi Everyone,

Hi Dr Jason.

Post by Jason Pruim
I think after I get this question answered, I can stop asking for
awhile since my project will be done, at least until the users say
"What happened to XYZ????" then I'll ask again :)
I asked on a MySQL list about "Resetting a auto increment filed" so
that there arn't any gaps in the record number.
So to say it another way, I have a table that has 900 records in it,
I've added 3 records, but then deleted 2 of those which puts the
actual record count at 901 but my auto increment field starts at 904
on the next insert.
Is there away with PHP that I can pragmatically change that value to
the total records in the database more so then a representation of the
actual record number?

What are you actually trying to achieve? Why do you need all records to
have a sequential number? Ignore how you're going to do it, just tell us
why you think you need this, because I've never come across a reason to
need this.
-Stut

I've seen many people who wished to "fix" sequences like this before,
usually because they believe there is something "wrong" with there being
gaps in there. However, from a database-point-of-view an auto_increment
value represents a unique row which _stays_ unique. As such because it
doesn't re-assign values used before you keep database integrity intact
because old possible links between rows/tables won't be reused and thus
won't form unintended links (ie. say you delete row [id=2] from table a,
which was linked via [id=2] to a row in table b. If it was reused, a
"fresh" row would suddenly inherit its predecessors links (which it
should not!).

The auto_increment value represents just that, an internal unique id for
a row in a specific table. It doesn't represent the location of a row in
respect to other rows (ie the 2nd, 3rd and 4th of the table), simply
because that's now what it's intended for. If that is what you wish,
you'll need to find a different way of achieving it.

[Stut]

Post by M. Sokolewicz

Post by Stut

Post by Jason Pruim
Hi Everyone,

Hi Dr Jason.

Post by Jason Pruim
I think after I get this question answered, I can stop asking for
awhile since my project will be done, at least until the users say
"What happened to XYZ????" then I'll ask again :)
I asked on a MySQL list about "Resetting a auto increment filed" so
that there arn't any gaps in the record number.
So to say it another way, I have a table that has 900 records in it,
I've added 3 records, but then deleted 2 of those which puts the
actual record count at 901 but my auto increment field starts at 904
on the next insert.
Is there away with PHP that I can pragmatically change that value to
the total records in the database more so then a representation of
the actual record number?

What are you actually trying to achieve? Why do you need all records
to have a sequential number? Ignore how you're going to do it, just
tell us why you think you need this, because I've never come across a
reason to need this.
-Stut

I've seen many people who wished to "fix" sequences like this before,
usually because they believe there is something "wrong" with there being
gaps in there. However, from a database-point-of-view an auto_increment
value represents a unique row which _stays_ unique. As such because it
doesn't re-assign values used before you keep database integrity intact
because old possible links between rows/tables won't be reused and thus
won't form unintended links (ie. say you delete row [id=2] from table a,
which was linked via [id=2] to a row in table b. If it was reused, a
"fresh" row would suddenly inherit its predecessors links (which it
should not!).
The auto_increment value represents just that, an internal unique id for
a row in a specific table. It doesn't represent the location of a row in
respect to other rows (ie the 2nd, 3rd and 4th of the table), simply
because that's now what it's intended for. If that is what you wish,
you'll need to find a different way of achieving it.

Again this is focusing on what autoincrement fields are used for. I want
Jason to tell us what he needs a sequential record number with no gaps
for, otherwise you lot are going to continue telling him the same thing
over and over again which is unlikely to help anyone.

-Stut

[Jason Pruim]

Post by Stut

Post by Jason Pruim
Hi Everyone,

Hi Dr Jason.

Post by Jason Pruim
I think after I get this question answered, I can stop asking for
awhile since my project will be done, at least until the users say
"What happened to XYZ????" then I'll ask again :)
I asked on a MySQL list about "Resetting a auto increment filed"
so that there arn't any gaps in the record number.
So to say it another way, I have a table that has 900 records in
it, I've added 3 records, but then deleted 2 of those which puts
the actual record count at 901 but my auto increment field starts
at 904 on the next insert.
Is there away with PHP that I can pragmatically change that value
to the total records in the database more so then a representation
of the actual record number?

What are you actually trying to achieve? Why do you need all
records to have a sequential number? Ignore how you're going to do
it, just tell us why you think you need this, because I've never
come across a reason to need this.

What I am trying to achieve, is something along the lines of excel...
You can clear out a row in excel, and then "sort" all the blank lines
out renumbering the records. Basically, the people I'm going to sell
this app too, are used to using excel, so I'm attempting to mimic as
much of it as I can.

Does that help clear it up?


--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
***@raoset.com

[Stut]

Post by Jason Pruim

Post by Stut

Post by Jason Pruim
Hi Everyone,

Hi Dr Jason.

Post by Jason Pruim
I think after I get this question answered, I can stop asking for
awhile since my project will be done, at least until the users say
"What happened to XYZ????" then I'll ask again :)
I asked on a MySQL list about "Resetting a auto increment filed" so
that there arn't any gaps in the record number.
So to say it another way, I have a table that has 900 records in it,
I've added 3 records, but then deleted 2 of those which puts the
actual record count at 901 but my auto increment field starts at 904
on the next insert.
Is there away with PHP that I can pragmatically change that value to
the total records in the database more so then a representation of
the actual record number?

What are you actually trying to achieve? Why do you need all records
to have a sequential number? Ignore how you're going to do it, just
tell us why you think you need this, because I've never come across a
reason to need this.

What I am trying to achieve, is something along the lines of excel...
You can clear out a row in excel, and then "sort" all the blank lines
out renumbering the records. Basically, the people I'm going to sell
this app too, are used to using excel, so I'm attempting to mimic as
much of it as I can.
Does that help clear it up?

Not really. You are in control of how the data gets displayed. This has
a very loose connection with how the data is actually stored in the
database.

How are you displaying the data to your users? Why do they need a row
number? If you're writing a spreadsheet application then an
autoincrement column for the row number is not what you want since the
row is something you need to control.

Some insight into what you are actually going to be doing with this
number would be helpful, rather than just the assertion that you need
it. We get that you think you need this sequential number, but why?

-Stut

[Jason Pruim]

Post by Stut

Post by Jason Pruim

Post by Stut

Post by Jason Pruim
Hi Everyone,

Hi Dr Jason.

Post by Jason Pruim
I think after I get this question answered, I can stop asking
for awhile since my project will be done, at least until the
users say "What happened to XYZ????" then I'll ask again :)
I asked on a MySQL list about "Resetting a auto increment filed"
so that there arn't any gaps in the record number.
So to say it another way, I have a table that has 900 records in
it, I've added 3 records, but then deleted 2 of those which puts
the actual record count at 901 but my auto increment field
starts at 904 on the next insert.
Is there away with PHP that I can pragmatically change that
value to the total records in the database more so then a
representation of the actual record number?

What are you actually trying to achieve? Why do you need all
records to have a sequential number? Ignore how you're going to
do it, just tell us why you think you need this, because I've
never come across a reason to need this.

What I am trying to achieve, is something along the lines of
excel... You can clear out a row in excel, and then "sort" all the
blank lines out renumbering the records. Basically, the people I'm
going to sell this app too, are used to using excel, so I'm
attempting to mimic as much of it as I can.
Does that help clear it up?

Not really. You are in control of how the data gets displayed. This
has a very loose connection with how the data is actually stored in
the database.
How are you displaying the data to your users? Why do they need a
row number? If you're writing a spreadsheet application then an
autoincrement column for the row number is not what you want since
the row is something you need to control.
Some insight into what you are actually going to be doing with this
number would be helpful, rather than just the assertion that you
need it. We get that you think you need this sequential number, but
why?

The information is being displayed in a table, and can be sorted by
any of the fields. The purpose of the application I am writing is
going to be a online database, giving my customers access to their
mailing list 24/7 from anywhere in the world.

Alot of the customers that my company deals with aren't the best when
it comes to computers, so it's a comfort level thing for them.

Also, I do have one customer that wants to be able to say to us "What
do you have for record #????" and have us be able say what it says
for that record. That customer is one of the people I want to switch
over to here ASAP and let her manage her mailing list.

But as I type that out, I think the reason I want a sequential
address number more then anything is to prevent the users from
asking, "I only have 900 records in my database, why do I have record
numbers over 1,000?". But, if I were to use something like
mysql_num_rows I could display a total record count and just tell
them to ignore the record number until there was an issue right? I
know that's on my end :) but I think I am talking my self out off
displaying sequential record numbers and finding other ways to
display the information :)



--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
***@raoset.com

[Stut]

The information is being displayed in a table, and can be sorted by any
of the fields. The purpose of the application I am writing is going to
be a online database, giving my customers access to their mailing list
24/7 from anywhere in the world.
Alot of the customers that my company deals with aren't the best when it
comes to computers, so it's a comfort level thing for them.
Also, I do have one customer that wants to be able to say to us "What do
you have for record #????" and have us be able say what it says for that
record. That customer is one of the people I want to switch over to here
ASAP and let her manage her mailing list.
But as I type that out, I think the reason I want a sequential address
number more then anything is to prevent the users from asking, "I only
have 900 records in my database, why do I have record numbers over
1,000?". But, if I were to use something like mysql_num_rows I could
display a total record count and just tell them to ignore the record
number until there was an issue right? I know that's on my end :) but I
think I am talking my self out off displaying sequential record numbers
and finding other ways to display the information :)

I think you're creating a problem where none exists. If your customers
can't understand that you give each record a unique ID and that when you
delete records you don't reuse those IDs then I think you need to get
new customers.

Here's a tip for free... don't call them record numbers, call them IDs.
The question your customer should be asking is "What do you have for
user #????".

If your customers do start asking about it, educate them rather than
trying to shield them from what is a really basic concept. As someone
has previously said in this thread, compare it to social security
numbers. The IDs are unique for life, so if a user gets deleted their ID
will never be reused. The IDs have no connection at all to the number of
users in the database.

It sounds like your getting it, but if you need any further
clarification on it I'll be happy to help.

-Stut

[Jason Pruim]

Post by Stut

Post by Jason Pruim
The information is being displayed in a table, and can be sorted
by any of the fields. The purpose of the application I am writing
is going to be a online database, giving my customers access to
their mailing list 24/7 from anywhere in the world.
Alot of the customers that my company deals with aren't the best
when it comes to computers, so it's a comfort level thing for them.
Also, I do have one customer that wants to be able to say to us
"What do you have for record #????" and have us be able say what
it says for that record. That customer is one of the people I want
to switch over to here ASAP and let her manage her mailing list.
But as I type that out, I think the reason I want a sequential
address number more then anything is to prevent the users from
asking, "I only have 900 records in my database, why do I have
record numbers over 1,000?". But, if I were to use something like
mysql_num_rows I could display a total record count and just tell
them to ignore the record number until there was an issue right? I
know that's on my end :) but I think I am talking my self out off
displaying sequential record numbers and finding other ways to
display the information :)

I think you're creating a problem where none exists. If your
customers can't understand that you give each record a unique ID
and that when you delete records you don't reuse those IDs then I
think you need to get new customers.
Here's a tip for free... don't call them record numbers, call them
IDs. The question your customer should be asking is "What do you
have for user #????".
If your customers do start asking about it, educate them rather
than trying to shield them from what is a really basic concept. As
someone has previously said in this thread, compare it to social
security numbers. The IDs are unique for life, so if a user gets
deleted their ID will never be reused. The IDs have no connection
at all to the number of users in the database.
It sounds like your getting it, but if you need any further
clarification on it I'll be happy to help.

I understand what you are saying, and I think I even understand why
what I was thinking was wrong... Now, it's just a matter of
displaying a "ID #" and then somewhere on the page, include a "Total
Records: $totalRecords" so they know how many are in there.

thank you for taking the time to help me understand why what I wanted
wasn't really what I wanted :)



--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
***@raoset.com