On Sun, 19 Sep 2004 23:59:02 +0300, Mike <[EMAIL PROTECTED]> wrote:
> hi.
> I have a rather childish question on tables and auto increment fields.
> Scenario: I have a table with an <int auto increment primary key> field. The
> deal is that everything works fine (I'm talking about the auto
> incrementation part)
> until I choose to delete a row. This creates a gap in the primary key field.
> And my question is: I'd like to know if there is an easier way to keep track
> of these gaps, instead of specifically iterating through the table and
> stopping where you find one. To accomplish this, I use this function:
>
I would tackle this by ordering the result set by the auto increment
field, and then looping through the results comparing the current
field with the one that preceded it. If the difference is more than
1, you've got a gap, and you need to determine how big of a gap it is.
In PHP, this happens somewhat like this:
$res = mysql_query ("select id from table order by id");
while (list ($id) = mysql_fetch_row ($res)) {
if (isset ($prevId) && ($prevId < $id - 1)) {
// We've got a gap. Need some more code here to figure out
how big the gap is, and to store it.
}
$prevId = $id;
}
Hope this helps.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
