When you look on the "Structure" tab for any given table, there is a "Next Autoindex" stat that tells you what integer MySQL is going to use to populate the primary key (for the auto_increment column) on the next INSERT.
Does anyone know how this is accomplished? I have tried:
but this method only looks at the max value for that column. If the last row is deleted then the MAX value is incorrect as MySQL doesn't re-use that row number that was deleted.
I'm curious how PHP MyAdmin determines that number. I have tried looking at the code behind that page, but am not able to figure it out.
//say your primary id thats set to autoincrement is "userID" and is in the first column//
// and the table name is "user"//
$result = mysql_query("SELECT * FROM user",$dbh);
$num=0;
while ($myrow= mysql_fetch_row($result))
{
if($myrowї0]>$num)
{
$num=$myrowї0];
}
}
echo "The next integer will be $num";
I'm not having any trouble determining what the next integer will be in the series. I can do that with the snippet I posted above.
The problem with that approach is that MySQL doesn't look at what the next integer in the series will be. MySQL somehow remembers what integer it used last and increments that by 1.
Example: If MySQL last used the integer 26 for the auto_increment, primary key column, then I subsequently delete row 26, the next integer MySQL will use is going to be 27....regardless that there is no longer a row 26.
Possibly I should have posted this in the MySQL section as there is more than likely a MySQL query that can give that info.
so you would like to have all your primary id in order cronologically? If so than just find the last primary id, increment it by one then insert it into your sql.
nincha wrote:so you would like to have all your primary id in order cronologically? If so than just find the last primary id, increment it by one then insert it into your sql.
That's not what he's looking for and he has stated that twice now .
crimius wrote:I'm not having any trouble determining what the next integer will be in the series. I can do that with the snippet I posted above.
crimius - I can't find a simple function for working this out either nothing seems to jump out of the manual... I'm kinda intrigued though so I guess it'll be a hunt through the phpMyAdmin code for me.
That will give you the last autoincrement id used for the database.
Not sure what will happen if you have autoincrements in more than one table.
Probably $row['tablename'] or something like that.
Probably $row['tablename'] or something like that.
A few minutes later -- nope: that MySQL function merely returns the last automatically generated id for the database.
I didn't see anything that was table-specific.
twigletmac wrote:
crimius - I can't find a simple function for working this out either nothing seems to jump out of the manual... I'm kinda intrigued though so I guess it'll be a hunt through the phpMyAdmin code for me.
Mac
thanks mac i'm going to poke around more with the source of phpMyAdmin as well. i got frustrated pretty quickly though as i'm a novice.
for my database/table, that query returned one row with information on the following fields:
Name
Type
Row_format
Rows
Avg_row_length
Data_length
Max_data_length
Index_length
Data_free Auto_increment <---what i was looking for
Create_time
Update_time
Check_time
Create_options
Comment