Mutliple Insert Query Error In Php not working

Question

I have an issue in multiple data insertion code that it is not working i dont know why can anyone help me I will provide you code

if(isset($_POST['save']))
    {
        $attendanceStudentName = $_POST['name'];
        $attendanceStudentRollNo = $_POST['rollNumber'];
        $attendanceClass = $_POST['className'];
        $attendanceStatus = $_POST['attendance'];

        $query = "";
        for($count = 0; $count<count($attendanceStudentName); $count++)
        {
            $attendanceStudentName_clean = mysqli_real_escape_string($conn, $attendanceStudentName[$count]);
            $attendanceStudentRollNo_clean = mysqli_real_escape_string($conn, $attendanceStudentRollNo[$count]);
            $attendanceClass_clean = mysqli_real_escape_string($conn, $attendanceClass[$count]);
            $attendanceStatus_clean = mysqli_real_escape_string($conn, $attendanceStatus[$count]);

            $query .= "insert into attendance(id, attendanceStudentName, attendanceStudentRollNo, attendanceClass, attendanceStatus)
            VALUES(NULL, '$attendanceStudentName_clean', '$attendanceStudentRollNo_clean', '$attendanceClass_clean', '$attendanceStatus_clean')";
        }

**Warning:** You might be open to [SQL Injections](http://php.net/manual/en/security.database.sql-injection.php) and should really use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](http://php.net/manual/en/pdo.prepared-statements.php) or by [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Never trust any kind of input, especially that which comes from the client side. Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). — Dharman, May 02 '19 at 22:00

Barmar · Accepted Answer · 2019-05-02T21:33:57.983

You can't concatenate multiple queries together like that (even if you use mysqli_multi_query(), you need to separate the queries with ;, which you didn't do).

You can write one INSERT query that has multiple lists of values after VALUES:

INSERT INTO tablename (col, col, ...) VALUES (val, val, ...), (val, val, ...), ...

So write it like this:

$query = "insert into attendance(id, attendanceStudentName, attendanceStudentRollNo, attendanceClass, attendanceStatus) VALUES ";
$values_array = [];
for($count = 0; $count<count($attendanceStudentName); $count++)
{
    $attendanceStudentName_clean = mysqli_real_escape_string($conn, $attendanceStudentName[$count]);
    $attendanceStudentRollNo_clean = mysqli_real_escape_string($conn, $attendanceStudentRollNo[$count]);
    $attendanceClass_clean = mysqli_real_escape_string($conn, $attendanceClass[$count]);
    $attendanceStatus_clean = mysqli_real_escape_string($conn, $attendanceStatus[$count]);

    $values_array[] = "(NULL, '$attendanceStudentName_clean', '$attendanceStudentRollNo_clean', '$attendanceClass_clean', '$attendanceStatus_clean')";
}
$query .= implode(', ', $values_array);

Another way, which avoids SQL injection better, is to use a prepared statement, and then execute it in the loop.

$query = "insert into attendance(id, attendanceStudentName, attendanceStudentRollNo, attendanceClass, attendanceStatus) 
        VALUES (NULL, ?, ?, ?, ?)";
$stmt = $conn->prepare($query);
$stmt->bind_param("ssss", $name, $rollno, $class, $status);
for($count = 0; $count<count($attendanceStudentName); $count++)
{
    $name = $attendanceStudentName[$count];
    $rollno = $attendanceStudentRollNo[$count];
    $class = $attendanceClass[$count];
    $status = $attendanceStatus[$count];

    $stmt->execute();
}

@Bamar Thanks Alot.. you save my day – Faizan Bhatti May 02 '19 at 21:33 — Faizan Bhatti, May 02 '19 at 21:33

Mutliple Insert Query Error In Php not working

1 Answers1