Mutliple Insert Query Error In Php not working [duplicate]

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 and should really use parameterized prepared statements instead of manually building your queries. They are provided by PDO or by MySQLi. 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. — Dharman
– Dharman ♦, Commented May 2, 2019 at 22:00

Barmar · Accepted Answer · 2019-05-02 21:33:57Z

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();
}

Collectives™ on Stack Overflow

Mutliple Insert Query Error In Php not working [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related