I want to INSERT multiple array values into the database [duplicate]

Question

Array
(
    [subject] => Mathematics
    [admissionno] => Array
        (
            [0] => MS3389
            [1] => MS3387
            [2] => MS3384
        )

    [name] => Array
        (
            [0] => Abdulbasit Alaka-Yusuf
            [1] => Abdulbasit Alaka-Yusuf
            [2] => Abdulbasit Alaka-Yusuf
        )

    [ca] => Array
        (
            [0] => 11
            [1] => 14
            [2] => 17
        )

    [assignment] => Array
        (
            [0] => 12
            [1] => 15
            [2] => 18
        )

    [exam] => Array
        (
            [0] => 13
            [1] => 16
            [2] => 19
        )

    [comment] => Array
        (
            [0] => qwerty
            [1] => asdfghj
            [2] => fghjcfb 
        )

    [save] => 
)

I'm trying to insert this array into the database this is the code:

if(isset($_POST['save'])){
        $id      = isset($_POST['id'])   && $_POST['id'] != ''   ? $_POST['id']  :   "";
        $subject = isset($_POST['subject'])    && $_POST['subject']    != "" ? $_POST['subject']        : "";
        $admissionno = isset($_POST['admissionno'])    && $_POST['admissionno']    != "" ? $_POST['admissionno']        : "";
        $name = isset($_POST['name'])    && $_POST['name']    != "" ? $_POST['name']        : "";
        $ca = isset($_POST['ca'])    && $_POST['ca']    != "" ? $_POST['ca']        : "";
        $assignment = isset($_POST['assignment'])    && $_POST['assignment']    != "" ? $_POST['assignment']        : "";
        $exam = isset($_POST['exam'])    && $_POST['exam']    != "" ? $_POST['exam']        : "";
        $comment = isset($_POST['comment'])    && $_POST['comment']    != "" ? $_POST['comment']        : "";

        echo '<pre>';
        print_r ($_POST);//die();

        $admissionno = implode("','",$_POST['admissionno']);
        $name = implode("','",$_POST['name']);
        $ca = implode("','",$_POST['ca']);
        $assignment = implode("','",$_POST['assignment']);
        $exam = implode("','",$_POST['exam']);
        $comment = implode("','",$_POST['comment']);
        //echo $admissionno. $name. $ca. $assignment. $exam. $comment;

        $insert_qry = "INSERT INTO result (id,subject, admissionno, name, ca, assignment, exam, comment)
                       VALUES('$id', '$subject', '$admissionno', '$name', '$ca', '$assignment', '$exam', '$comment')";
        echo $insert_qry;
        $result = mysqli_query($connect, $insert_qry);
        die('e don do oo');

        if($result > 0){
            
        }
    }

but I'm getting this error:

Fatal error:  Uncaught mysqli_sql_exception: Column count doesn't match value count at row 1 in C:\xampp\htdocs\Malizzay\admin\exam\exam-grade.php:37
Stack trace:
#0 C:\xampp\htdocs\Malizzay\admin\exam\exam-grade.php(37): mysqli_query(Object(mysqli), 'INSERT INTO res...')
#1 {main}
  thrown in C:\xampp\htdocs\Malizzay\admin\exam\exam-grade.php on line 37

You can't insert all the rows in a single VALUES list. You need VALUES (values for row 1), (values for row 2), (values for row3). — Barmar
– Barmar, Commented Sep 9, 2022 at 1:44
Output the query and the issue should be clear. If you want multiple rows the syntax is values (row1), (row2),(row3) not values(row1, row2,row3) as it appears you currently have. If you are attempting to store data as CSV in column you also should not do that. — user3783243
– user3783243, Commented Sep 9, 2022 at 1:44
And your code isn't even generating values (row1, row2, row3). It's generating values (all ids, all subjects, all admissionno, ...) — Barmar
– Barmar, Commented Sep 9, 2022 at 1:45
Your script is vulnerable to SQL Injection Attack. Even if you are escaping variables, its not safe! You should always use prepared statements and parameterized queries in either MYSQLI or PDO instead of concatenating user provided values into the query. — Barmar
– Barmar, Commented Sep 9, 2022 at 1:46

Barmar · Accepted Answer · 2022-09-09 02:31:48Z

0

Instead of trying to insert all the values at once, use a loop. Prepare the INSERT statement with parameters before the loop, then reassign the variables in the loop to the current iteration in all the POST parameters.

There's no id in the form, so you don't need to use $id. That's presumably an AUTO_INCREMENT column, so you leave it out of the INSERT and it will be inserted automatically.

$insert_stmt = mysqli_prepare($conn, "
    INSERT INTO result (subject, admissionno, name, ca, assignment, exam, comment)
    VALUES(?, ?, ?, ?, ?, ?, ?)");
mysqli_stmt_bind_param($insert_stmt, "sssssss", $subject, $admissionno, $name, $ca, $assignment, $exam, $comment);
$subject = $_POST['subject'];
foreach ($_POST['admissionno'] as $i => $admissionno) {
    $name = $_POST['name'][$i];
    $ca = $_POST['ca'][$i];
    $assignment = $_POST['assignment'][$i];
    $exam = $_POST['exam'][$i];
    $comment = $_POST['comment'][$i];
    mysqli_stmt_execute($insert_stmt);
}

edited Sep 9, 2022 at 2:31

answered Sep 9, 2022 at 2:19

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

olumide Over a year ago

Please can you write it in a simple code?

Barmar Over a year ago

How can I make this any simpler?

olumide Over a year ago

i got this error

olumide Over a year ago

Parse error: syntax error, unexpected token "as", expecting ";"

Barmar Over a year ago

Sorry, for should be foreach

|

Collectives™ on Stack Overflow

I want to INSERT multiple array values into the database [duplicate]

1 Answer 1

13 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Linked

Related