Problem with multiple data entry in php mysql form

when data is entered from multiple computers to my database at same time, then last insert id is not working , can expert guide me by viewing at my processing file and tell whats wrong with it in php mysql. three table are used, iap3, iap4 and iap5 respectively.

<?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
$sqlInsert = "INSERT INTO iap3 (mrno, name1, age1, mobno, gender1,   bilno, bildate, totbil, adv11, netpayable1,  payrecved, netpay1, uname1) 
			 VALUES ('$mrno', '$name1', '$age1', '$mobno', '$gender1',  '$bilno', '$bildate',   '$totbil', '$adv11', '$netpayable1',  '$payrecved', '$netpay1', '$abc')";		
		   $rs = mysqli_query($con, $sqlInsert);

   
  
  	 $last_id = mysqli_insert_id($con);
  
   	 for ($i = 0; $i < count($city); $i++) {
			$sqlInsertItem = "
			INSERT INTO iap4 (pid, country, state, city, docname, qty, price1, discunt, tot4, tech1, remarks3, zzz1, zzz2, zzz3, zzz4, zzz5) 
			VALUES (' $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]')";			
			$rs1 = mysqli_query($con, $sqlInsertItem);
		}       	

   for ($i = 0; $i < count($city); $i++) {
			$sqlInsertItem1 = "
			INSERT INTO iap5 (pid1, scode1, machno1) 
			VALUES ('$last_id', '$scode1[$i]',  '$machno1[$i]')";

What result are you getting and what is wrong with that result?

Don’t do this. The point of testing the request_method is because there are cases where the submit button won’t be set. If there is just one form/form processing on a page, just test the request_method. If you have more than one from/form processing on a page, use a hidden field with a unique value in it to control which form processing to execute.

Don’t copy variables to other variables for nothing. This is just a complete waste of your time typing. Just keep the form data in a php array variable, then operated on elements in this array variable throughout the rest of the code.

You need to trim all the input data before using it, mainly so that you can detect if a value was all white-space characters.

You must validate all data before using it.

ALL the form processing code must be inside the conditional statement testing if the form was submitted. Currently, all rest of the code, using the values, is executed every time the page is requested,

Don’t unconditionally output raw database statement errors onto a web page, as this gives hackers useful information when they intentionally do things that trigger errors. You should instead be using exceptions for database statement errors (this is the default setting now in php8+) and in most cases simply let php handle any database statement errors. The exception to this rule is when inserting/updating user submitted data that could result in duplicate values. In this case, you code should catch the exception, test for a duplicate error number, and setup a message letting the user know what was wrong with the data. For all other error numbers, just rethrow the exception and let php handle it.

You need to use prepared queries to prevent any sql special characters in a value from being able to break the sql query syntax, which is how sql injection is accomplished. If it seems like using a prepared query with the mysqli extension is overly complicated and inconsistent, it is. This would be a good time to switch to the much simpler and more modern PDO extension.

2 Likes

What is in $last_id when you display it as the first part of your debugging process? What is actually happening that is “wrong”? Does the table have an auto-incrementing id column?

its shows 0 in lastinsert id column instead of giving id of previous table. yes its auto increment id,

when single person doing entry, then all worked fine, lastinsertid successfully works, problem start when more than 2 person enter data in software form

0 in lastinsert id column of iap4,

You are probably getting a query error for the iap3 INSERT query, either due to duplicate data or data containing sql special characters, but because there’s no error handling for any of the queries, you don’t know what is occurring. Your code always needs validation logic and error handling so that it will either work or it will tell you why it doesn’t work.

Here’s a couple of more points for the posted code -

  1. This set of queries should be part of a transaction, so that if any fail with an error, they can all be rolled back.
  2. You have a leading space character as part of the ' $last_id' value. This problem will go away when you switch to using prepared queries, since you will no longer be putting the values directly into the sql query statements.
2 Likes

@ Drummin suggested me that I should use querry name instead of connection for last insert id , it will solve with issue with prepared statement, i think its very good idea but will it handle mutliple entries at same time ??? that what i want to knew after doing that, hopefully it will

The last insert id is maintained per individual connection (sql session) and it requires the connection variable.

The reasons it is a zero is because the INSERT query failed due to an error. You don’t know which error because you don’t have any error handling for the queries.

but other fields values are submitted sucessfully, in both parent and child table. in parent table auto increment key is also entered, but it only giving last insert id inside two child table iap4 and iap5 as 0

There’s ya problem then.

1 Like

i solved it, i am using con instead conn

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