PHP MyAdmin --> Next Autoindex row statistic

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
crimius
Forum Commoner
Posts: 28
Joined: Sun Oct 13, 2002 6:02 pm
Location: Austin, Texas
Contact:

PHP MyAdmin --> Next Autoindex row statistic

Post by crimius »

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:

Code: Select all

SELECT MAX(image_id) AS max FROM image
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.

Does anyone have any insight?

Thanks in advance,
-Craig
nincha
Forum Contributor
Posts: 191
Joined: Fri Mar 28, 2003 12:30 pm
Location: CA, USA

Post by nincha »

well, i dont know the simple syntax to find the next auto increment integer, but u can always create your own function, example:

Code: Select all

//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";
well hope this helps yah...
crimius
Forum Commoner
Posts: 28
Joined: Sun Oct 13, 2002 6:02 pm
Location: Austin, Texas
Contact:

Post by crimius »

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.

Thanks for the response though :)
nincha
Forum Contributor
Posts: 191
Joined: Fri Mar 28, 2003 12:30 pm
Location: CA, USA

Post by nincha »

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.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

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 :roll: .
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.

Mac
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post by Bill H »

Try this:

Code: Select all

<?php
          $Query = "SELECT LAST_INSERT_ID()";
          $Result = mysql_query($Query, $Link);
          $row = mysql_fetch_array($Result);
          $Lastone = $row[0]; 
?>
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.
crimius
Forum Commoner
Posts: 28
Joined: Sun Oct 13, 2002 6:02 pm
Location: Austin, Texas
Contact:

Post by crimius »

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.

thanks again for your time,
-craig
crimius
Forum Commoner
Posts: 28
Joined: Sun Oct 13, 2002 6:02 pm
Location: Austin, Texas
Contact:

Post by crimius »

thanks for checking into it Bill :)
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

try

Code: Select all

SHOW TABLE STATUS FROM dbname LIKE 'tblname'
http://www.mysql.com/doc/en/SHOW_STATUS.html
crimius
Forum Commoner
Posts: 28
Joined: Sun Oct 13, 2002 6:02 pm
Location: Austin, Texas
Contact:

Post by crimius »

volka wrote:try

Code: Select all

SHOW TABLE STATUS FROM dbname LIKE 'tblname'
thanks volka! that works perfectly :)

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 :D
Create_time
Update_time
Check_time
Create_options
Comment

Thanks again for everyone's time on this :)
Post Reply