Last insert id producing 0 in few enteries

I am using prepared statement to get last insert id and later using it in two child table. The issue is value is stored sucessfuly in parent tablwe, but last insert id showing 0 value some time and some times its showing correct last insert id, here is pic of database and my processing code, can any one guide whats wrong with my code

<?php
error_reporting(E_ERROR | E_WARNING | E_PARSE);
if($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['submit']))
{
// getting all values from the HTML form
   
         $mrno = $_POST['mrno'];
	 $name1 = $_POST['name1'];
         $age1 = $_POST['age1'];
         $mobno = $_POST['mobno'];
         $gender1 = $_POST['gender1'];       
         $bilno = $_POST['bilno'];
         $bildate = $_POST['bildate'];
        $totbil = $_POST['totbil'];
        $adv11 = $_POST['adv11'];
	$netpayable1 = $_POST['netpayable1'];		
	$payrecved = $_POST['payrecved'];
	$netpay1 = $_POST['netpay1'];
	$abc = $_POST['abc'];
  
	$country = $_POST['country'];
	$state = $_POST['state'];
	$city = $_POST['city'];        
    	$docname= $_POST['docname'];
        $qty = $_POST['qty'];
	$price1 = $_POST['price1'];
	$discunt = $_POST['discunt'];        
    	$tot4= $_POST['tot4'];
        $tech1 = $_POST['tech1'];
        $remarks3 = $_POST['remarks3'];
	$zzz1 = $_POST['zzz1'];
        $zzz2 = $_POST['zzz2'];
	$zzz3 = $_POST['zzz3'];
        $zzz4 = $_POST['zzz4'];
	$zzz5=  $_POST['zzz5'];
	        $scode1 = $_POST['scode1'];
        	$machno1 = $_POST['machno1'];
}


 // database details
    $host = "localhost";
    $username = "thehospi_root";
    $password = "u1m1a1r1";
    $dbname = "thehospi_hmis2";

    // creating a connection
    $con = mysqli_connect($host, $username, $password, $dbname);

    // to ensure that the connection is made
    if (!$con)
    {
        die("Connection failed!" . mysqli_connect_error());
    }

        

    // using sql to create a data entry query

// using sql to create a data entry query
$sqlInsert ="INSERT INTO iap3 (mrno, name1, age1, mobno, gender1,   bilno, bildate, totbil, adv11, netpayable1,  payrecved, netpay1, uname1) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";

$queryiap3 = $con->prepare($sqlInsert);
$queryiap3->bind_param('sssssssssssss', $mrno, $name1, $age1, $mobno, $gender1,  $bilno, $bildate,   $totbil, $adv11, $netpayable1,  $payrecved, $netpay1, $abc);			
$queryiap3->execute();
$last_id = $queryiap3->insert_id;  

   
  
  	
  
  $sqliap4 = "INSERT INTO iap4 (pid, country, state, city, docname, qty, price1, discunt, tot4, tech1, remarks3, zzz1, zzz2, zzz3, zzz4, zzz5) 
			VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			$queryiap4 = $con->prepare($sqliap4);

for ($i = 0; $i < count($city); $i++) { 
	$queryiap4->bind_param('ssssssssssssssss', $last_id,  $country[$i], $state[$i], $city[$i], $docname[$i], $qty[$i], $price1[$i], $discunt[$i], $tot4[$i], $tech1[$i], $remarks3[$i], $zzz1[$i], $zzz2[$i], $zzz3[$i], $zzz4[$i], $zzz5[$i]);
	$queryiap4->execute();
}     	



  $sqliap5 = "INSERT INTO iap5 (pid1, scode1, machno1) 
			VALUES (?,?,?)";
			$queryiap5 = $con->prepare($sqliap5);

for ($j = 0; $j < count($city); $j++) { 
	$queryiap5->bind_param('sss', $last_id,  $scode1[$j], $machno1[$j]);
	$queryiap5->execute();
}

?>

<?php
foreach($_POST['country'] as $k => $v){
		$country = $_POST['country'][$k];
	 
		
		$sql = "SELECT `name` FROM `country` WHERE `id` = '$country'";
		$result = $con->query($sql); 
		if ($result->num_rows > 0) {
			while ($row = $result->fetch_assoc()) {			
				$uuu[] = $row['name'];	 
                              	
                           
				
}} }

	foreach($_POST['city'] as $k => $v){
		$city = $_POST['city'][$k];
	 
		
		$sql = "SELECT `city` FROM `city` WHERE `id` = '$city'";
		$result = $con->query($sql); 
		if ($result->num_rows > 0) {
			while ($row = $result->fetch_assoc()) {			
				$uuu1[] = $row['city'];	                               	
                           
				
}} 
}

?>
![000|690x237](upload://rQia4bz3VtiZE612VEvVNK5DO5z.jpeg)

As @mabismad mentioned in the other thread, you’re not checking to see whether your first query has executed, getting an insert-id of zero suggests that it did not.

of course its executed in all cases, data inserted successfully in first querry

OK, so what varies between a successful insert-id and an unsuccessful one?

in child tables, when its successfully insert then its showing auto increment id of first querry and when its un sucessfull, its insert 0 instead of actual value of parent id

i think first querry failed some time, but how can i check it ???

Have you enabled server logs for your database? That might give you some clue. Are you testing this with the same data, or with different data each time? Is there something in the data causing the issue?

You were already told what to do -

What php version are you using?

If for some reason you are not using php8+, add the following line of code before the point where you make the database connection to enable exceptions for the mysqli extension -
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

You should also test the last insert id value in your code and display an error message, and halt execution if it is a zero (there’s a chance it is not, but it is being converted to a zero when used in the later queries.) There’s also a chance you have happened upon a bug in either php or the database server you are using.

It would help if you posted your database table definitions, because this could be occurring due to an out of range value combined with the database server not being set to strict mode and it is truncating the value instead of reporting an error for the value.

https://www.php.net/manual/en/mysqli-stmt.execute.php

Returns true on success or false on failure.

A simple check on the execute should do the trick. Though ultimately using exceptions will help out.

if ($queryiap3->execute() === false) {
    // Oopsie
1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.