Hello Friends, Today I am going to share that how to get next increment id in mysql using php. Actually MySQL has AUTO_INCREMENT keyword to perform auto increment on a column value. AUTO_INCREMENT by default starts from 1 and It is incremented by 1 for each new record. To get next auto increment value in MySQL we can use LAST_INSERT_ID() function or AUTO_INCREMENT with SELECT but in PHP we can call insert_id on connection object or mysqli_insert_id(connection). But both PHP method is good when you have just executed insert query and it gives last insert id which we can use after incrementing it by 1 to get next increment id. But what if we have not done any insert query and still want next increment id of specific table. Then we can use AUTO_INCREMENT with SELECT.
The following is the SQL syntax to get the next id.
1 2 3 4 |
SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = "database_name" AND TABLE_NAME = "table_name" |
PHP Code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
<?php $sql_db_host = "localhost";// MySQL Hostname $sql_db_user = "username";// MySQL Database User $sql_db_pass = "password";// MySQL Database Password $sql_db_name = "databasename";// MySQL Database Name $conn = new mysqli($sql_db_host, $sql_db_user, $sql_db_pass, $sql_db_name); if (mysqli_connect_errno()) { die(mysqli_connect_error()); } function getNextIncrement($table) { global $conn, $sql_db_name; $next_increment = 1; $table = mysqli_escape_string($conn, $table); $sql = "SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = '$sql_db_name' AND TABLE_NAME = '$table'"; $results = $conn->query($sql); while($row = $results->fetch_assoc()) { $next_increment = $row['AUTO_INCREMENT']; } return $next_increment; } echo "Next increment id for table <b>test</b> will be: " . getNextIncrement('test'); ?> |
 Data of Table “test”
id | name |
1 | Vivek |
2 | Suresh |
Output
Next increment id for table test will be: 3
Thanks
Please share if you like it
Comments