How to insert multiple rows for comma seperated values in mysqli prepared statement?-CodePudding

I have a comma seperated values to be inserted as seperate rows? below is the code tried to insert comma seperated values. How to insert the values in database as expected output.

$arrRequestData['id'] = 1;
$arrRequestData['docid'] = '2,3';

$dbcon = new db();
$ins_trans    = $dbcon->query('
    INSERT INTO exxx_ddd_ccc (
        eshareq_id,
        document_id
    ) 
    VALUES (?,?)', 
    $arrRequestData['id'],
    $arrRequestData['docid']
);

output:
id docid
12   2,3


expected output
id docid
12   2
13   3

CodePudding user response：

I would use the PHP explode() function and then loop over the data to insert them individually. You can do this on the database side but depends on the size of the dataset which method you would use.

Looks like you are using a custom class for the db() but if you are constructing your class with a connection, then passing your queries, you can put this line $dbcon = new db(); outside of the foreach loop

$arrRequestData['docid'] = '2,3';
$splitData = explode(",", $arrRequestData['docid']);

$dbcon = new db();

foreach($splitData as $key=>$value) {
 $ins_trans    = $dbcon->query('
    INSERT INTO exxx_ddd_ccc (
        eshareq_id,
        document_id
    ) 
    VALUES (?,?)', 
    $arrRequestData['id'],
    $value
 );
}

CodePudding user response：

I don't think there is a way to do it in the way you proposed. $arrRequestData['docid'] is a string in your example. If you wish to make it an array you should do it with explode function for example:

$arrRequestData['docid'] = explode(',', $arrRequestData['docid']); // now $arrRequestData['docid'] is an array

And then build a multiple insert with foreach:

$insert = [];

foreach ($arrRequestData['docid'] as $docid) {
    $insert[] = "({$arrRequestData['id']}, $docid)";
}

$insert = implode(',', $insert);
// now $insert looks like (1,1), (1,2)

$ins_trans = $dbcon->query("
    INSERT INTO exxx_ddd_ccc 
    (
        eshareq_id,
        document_id
    )
    VALUES $insert
     
");