Next Increment Id in MySQL using PHP Php by Rajesh Kumar Sahanee - June 6, 20190 Post Views: 9,800 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. sample-sql-syntax MySQL SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = "database_name" AND TABLE_NAME = "table_name" 1234 SELECT AUTO_INCREMENTFROM information_schema.TABLESWHERE TABLE_SCHEMA = "database_name"AND TABLE_NAME = "table_name" PHP Code index.php PHP <?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'); ?> 123456789101112131415161718192021222324 <?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
