how to inserting multiple rows into mysql with one INSERT statement using PHP implode function?

Question

I'm trying to insert multiple rows with only one INSERT INTO statement using the implode function. I use "keluhanpg" and "mkpg" for multiple values. and "mkpg" uses implode because I use multiple selected data and I implode with comma. like this picture: enter image description here

<?php
    session_start();
    include_once('koneksi.php');

    if(isset($_POST['add'])){
    $kodepg         = $_POST['kodepg'];
    $noformpg       = $_POST['noformpg'];
    $waktupg        = $_POST['waktupg'];
    $deptpg         = $_POST['deptpg'];
    $lokasipg       = $_POST['lokasipg'];
    $merkpg         = $_POST['merkpg'];
    $tahunpg        = $_POST['tahunpg'];
    $op             = isset($_POST['oppg']) && is_array($_POST['oppg']) ? $_POST['oppg'] : [];
    $oppg           = implode(", ", $op);
    $keluhanpg      = isset($_POST['keluhanpg']) && is_array($_POST['keluhanpg']) ? $_POST['keluhanpg'] : [];
    $mk             = isset($_POST['mkpg']) && is_array($_POST['mkpg']) ? $_POST['mkpg'] : [];
    $mkpg           = implode(", ", $mk);
    $shiftpg        = $_POST['shiftpg'];
    $statuspg       = $_POST['statuspg'];
    
    foreach ($_POST['keluhanpg'] as $key => $value){
        $sql= "INSERT INTO tb_pg_cs (kode_pg,noform_pg,waktu_pg,dept_pg,lokasi_pg,merk_pg,tahun_pg,op_pg,keluhan_pg,mk_pg,shift_pg,status_pg) VALUES ('" . $kodepg . "','".$noformpg . "','".$waktupg . "','".$deptpg . "','".$lokasipg . "','".$merkpg . "','".$tahunpg . "','".$oppg . "','".$_POST['keluhanpg'][$key] . "','".$mkpg . "','".$shiftpg . "','".$statuspg . "')";
        $sql1 = mysqli_query($connect, $sql);
    }
    
    if($sql1){
        $_SESSION['success'] = 'Data added successfully';
    }else{
        $_SESSION['error'] = 'Something went wrong while adding';
    }
    
    }   

    header("location: ".$base_url."index.php?page=pengajuan");
?>

the input successfully exited but the "mkpg" data was merged into one. like this: enter image description here

how to write code to process input correctly?

https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Dharman, Jul 28 '21 at 10:45

score 0 · Answer 1 · answered Jul 29 '21 at 04:36

my MySQL precise version 8.0.3 – vivie

Your version allows to use recursive CTE in SELECT part of INSERT .. SELECT. So you can parse your CSV provided by PHP and store it as a lot of separate rows.

This can be done, for example, using

INSERT INTO test (single_arg, csv_arg)
WITH RECURSIVE
source_data AS ( SELECT @single_arg single_arg,
                        @csv_arg csv_arg ),
cte AS ( SELECT single_arg,
                SUBSTRING_INDEX(csv_arg, ',', 1) single_from_csv,
                TRIM(LEADING ',' FROM TRIM(LEADING SUBSTRING_INDEX(csv_arg, ',', 1) FROM csv_arg)) slack_from_csv
         FROM source_data
         UNION ALL
         SELECT single_arg,
                SUBSTRING_INDEX(slack_from_csv, ',', 1),
                TRIM(LEADING ',' FROM TRIM(LEADING SUBSTRING_INDEX(slack_from_csv, ',', 1) FROM slack_from_csv))
         FROM cte
         WHERE TRIM(slack_from_csv) <> '' )
SELECT single_arg, 
       single_from_csv
FROM cte;

See DEMO fiddle (contains some explanations).

how to inserting multiple rows into mysql with one INSERT statement using PHP implode function?

1 Answers1