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:
1Aakriti
2Prakash
3John
4Walton

Now, to get the next auto increment number, we can simply use this code:
<?php
         $q=mysql_query("SELECT * FROM `test`");
         $next_auto_num=mysql_num_rows($q)+1;
         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:
<?php
        $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.

2 Responses to “Next Auto Increment Number in MySQL using PHP”

Anonymous said...

November 14, 2013

kjlk


Syafiq Mustafa said...
December 03, 2014

Prakash, may I ask you a question. How can I make the auto increment number be like "001" instead of "1"?


Post a Comment

Please drop a comment on the above post :)

Search This Blog

Loading...

Followers

Email Subscription

Enter your email address:

Find more