${茶男與藍牌}

** It is not recommended to use this method for generating foreign keys for lookups. In a multiuser environment with simultaneous requests the results will be utterly useless and unreliable. OK? **

So I was working on the preview feature of the home grown content management system for this site, and I came across an interesting problem for which my friend Google had no answers.

The way the preview feature works is that when editing an entry, you hit the PREVIEW button, and the script posts the edited data unto itself and then writes it to a temp file named {section}_{entry ID}.php. When the posted page loads, it opens a new window with the template for the given section, and the POST data is processed in the content well, just as any other entry would be.

The problem is that if you want to create a NEW entry, you don’t have an entry ID to assign to the tempfile name, and therefore the preview feature does not work. So I needed a way of finding out what MySQL would assign as the next auto_increment entry ID if I were to actually insert a record. My first thought was to simply get the highest existing ID from the table and add 1, but that is not a perfect solution. For one thing, there is the possibility that another author might insert a new record during the time that I’m previewing, which will cause some confusion — we might end up with two instances of the temp file overwriting one another. No good. Also, the highest existing ID + 1 is NOT necessarily the next auto increment ID, because when entries are deleted, the entry ID is not reused. So it’s conceivable that if your highest ID is 500, the next ID might be 501, or it might be 5001 for all we know.

I was on a mission.

So, I looked all over the web for a solution, and found several similar scenarios described, but no answers. In fact, I came across several forums and help sites where it was actually stated as fact that you CANNOT get the next auto_increment. I knew this to be false, because this information is displayed in the table structure view of phpMyAdmin. So I scoured the source code of phpMyAdmin, and after many desperate combinations of GREPs, FINDs, and XARGSs, I finally came up with this: (assumes we’re already connected to the dB)

<? $tablename = “tablename”; $next_increment = 0; $qShowStatus = “SHOW TABLE STATUS LIKE ‘$tablename’”; $qShowStatusResult = mysql_query($qShowStatus) or die ( “Query failed: ” . mysql_error() . “<br/>” . $qShowStatus ); $row = mysql_fetch_assoc($qShowStatusResult); $next_increment = $row['Auto_increment']; echo “next increment number: [$next_increment]“; ?>

And the rundown:

“SHOW TABLE STATUS” produces a two-dimensional array with one row for each table, and a slew of columns: Name, Type, Row_format, Rows, Avg_row_length, Data_length, Max_data_length, Index_length, Data_free, Auto_increment, etc.

So what I’m doing here is using mysql_fetch_assoc() to get the “Auto_increment” value.

And there you have it. Our next likely entry ID, without inserting anything.

edited by JD on 2/14/06

Oh the drama