I want to INSERT multiple array values into the database

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, Sep 09 '22 at 01: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, Sep 09 '22 at 01:44
And your code isn't even generating `values (row1, row2, row3)`. It's generating `values (all ids, all subjects, all admissionno, ...)` — Barmar, Sep 09 '22 at 01:45
Your script is vulnerable to [SQL Injection Attack](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Even if [you are escaping variables, its not safe](https://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string%5D)! You should always use [prepared statements and parameterized queries](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either MYSQLI or PDO instead of concatenating user provided values into the query. — Barmar, Sep 09 '22 at 01:46
Do you mean I should do a for loop when I want to INSERT @user3783243 — olumide, Sep 09 '22 at 02:21
`isset($_POST['id']) && $_POST['id'] != ''` is `!empty($_POST['id'])` — mickmackusa, Sep 11 '22 at 21:54
Related (but answers are not necessarily advisable): https://stackoverflow.com/q/23526198/2943403 and https://stackoverflow.com/q/7320512/2943403 and https://stackoverflow.com/q/11720981/2943403 and https://stackoverflow.com/q/53968999/2943403 and https://stackoverflow.com/q/11301871/2943403 and https://stackoverflow.com/q/45871981/2943403 — mickmackusa, Sep 12 '22 at 03:37

Barmar · Answer 1 · 2022-09-09T02:31:48.943

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 09 '22 at 02:31

answered Sep 09 '22 at 02:19

Barmar

741,623
53
500
612

Please can you write it in a simple code? – olumide Sep 09 '22 at 02:24
How can I make this any simpler? – Barmar Sep 09 '22 at 02:25
i got this error – olumide Sep 09 '22 at 02:27
Parse error: syntax error, unexpected token "as", expecting ";" – olumide Sep 09 '22 at 02:27
Sorry, `for` should be `foreach` – Barmar Sep 09 '22 at 02:29
i mean to write it in procedural way – olumide Sep 09 '22 at 02:29
OK, I rewrote it. But the the OO way is simpler IMHO. The documentation shows the correspondence. – Barmar Sep 09 '22 at 02:31
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:33 Stack trace: #0 C:\xampp\htdocs\Malizzay\admin\exam\exam-grade.php(33): mysqli->prepare('\r\n INSER...') #1 {main} thrown in C:\xampp\htdocs\Malizzay\admin\exam\exam-grade.php on line 33 – olumide Sep 09 '22 at 02:31
Sorry. WHen I removed `id` from the column list I forgot to remove its `?` from the `VALUES` list. – Barmar Sep 09 '22 at 02:32
I feel like in 2022, we probably have tens of Stack Overflow pages that demonstrate the necessary approach. Does this need to be answered to be resolved? – mickmackusa Sep 11 '22 at 21:53
@mickmackusa I'm really getting tired of you criticizing me for not finding a dup when you don't provide one, either. – Barmar Sep 12 '22 at 02:55
I am fully busy consolidating the last decade of redundant php page. This is my excuse for not voting to close THIS page. I only came upon this page today, because it was in the search results while I was trying to close another page. It is not solely my job to find duplicates. I have a full time job and family and I need to sleep. I cannot possibly close every new duplicate. I am hoping that the veteran PHP contributors here will have enough good will to close new basic duplicates when they are asked. I am targeting a specific topic. My current progress: https://3v4l.org/CtE2W – mickmackusa Sep 12 '22 at 03:13
Okay I've added links to 7 other related pages and hammered this page closed, but my point is that Stack Overflow needs your persistent help to help consolidate the redundant content so that this place can be the researchers' paradise that it was designed to be. – mickmackusa Sep 12 '22 at 03:39

I want to INSERT multiple array values into the database

1 Answers1