Timilsina Prakash's Blog
"Better to live one year as a tiger than 100 year as a sheep"

Next Auto Increment Number in MySQL using PHP

While doing live project, we face many obstacles with mysql queries or my be with PHP codes. We may have the concept how to do that, but, we are just not able to write the absolute code for what we want. I wanted the script from which we can know next auto increment number using php and mysql. I had the logic how to do that, but, i was searching for the simplest method to do this. To make you more clear what i wanted is:
Suppose there is a database table `test`
id (int) (auto increment)
name (varchar) (20)
So, the value may be:

Now, to get the next auto increment number, we can simply use this code:
         $q=mysql_query("SELECT * FROM `test`");
         echo "Next Auto increment number is :".$next_auto_num;
But this is not gonna work,
For example, what if the third row with name John is removed? Then, the next auto increment number is shown as : 4. Which actually is 5. Because, the total result are 3 therefore the increment is shown as 4. But, 5 gonna be the next auto increment number. If you understood what I mean, this code gonna work for this problem:
        $q = mysql_query("SHOW TABLE STATUS LIKE 'test'");
        $row = mysql_fetch_assoc($q);
        $next_increment = $row['Auto_increment'];
        echo "next increment number: [$next_increment]";?>
 This gonna work for this problem. Please comment if you have something more innovative idea.

Search This Blog



Email Subscription

Enter your email address: