Welcome to WebmasterWorld Guest from 54.144.231.243

Forum Moderators: coopster & jatar k

Figuring next auto incremented ID to be

knowing that an unknown bunch of rows were deleted

   
12:21 pm on Aug 19, 2008 (gmt 0)

WebmasterWorld Senior Member henry0 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



How will you figure ahead of time what will be the next incremented id when a bunch of previous ID were deleted

example (in a perfect world!)
existing ID is 1, so we know that the next will be 2

eample (in my scenario)
existing ID could be 5, then a few more rows were added, say 6 to 10
but then later 6 to 10 are deleted

so one may think that the next ID could be 6 since the last row reads 5
but as we know the very next will start above the last deleted row
and the new ID will become 11

Question:
Is there a way to figure it out before that last newer row will be added?

1:29 pm on Aug 19, 2008 (gmt 0)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



>> the very next will start above the last deleted row

the very next will start after the last added row

I'm not sure it matters, I know people often ask about how to backfill dead IDs but I have never really unstood the point of the exercise. It's a ton of work for little, or no, value.

1:42 pm on Aug 19, 2008 (gmt 0)

5+ Year Member



With MySQL, you can use the "SHOW TABLE STATUS" query to get a bunch of info about a table, including the next Auto_increment value that it will assign.
1:49 pm on Aug 19, 2008 (gmt 0)

WebmasterWorld Senior Member henry0 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Yes, good idea using : mysql_insert_id
mysql_insert_id [us.php.net]

I do not want to fillback, just need to fwrite the next id before its creation
Thanks

1:54 pm on Aug 19, 2008 (gmt 0)

WebmasterWorld Senior Member henry0 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



that is also a good suggestion thanks!
<?php

mysql_connect("localhost","root","");
$result = mysql_query("SHOW TABLE STATUS FROM test;");
while($array = mysql_fetch_array($result)) {
$total = $array[Data_length]+$array[Index_length];
echo '
Table: '.$array[Name].'<br />
Next ID: '.$array[Auto_increment].'<br />
Index Size: '.$array[Index_length].'<br />
Total Size: '.$total.'<br />
Total Rows: '.$array[Rows].'<br />
Average Size Per Row: '.$array[Avg_row_length].'<br /><br />
';
}
// etc....
?>

 

Featured Threads

Hot Threads This Week

Hot Threads This Month