Insert multiple rows into mysql

Question

I have a value like this

$id='11';   
$value1= 'tb1,tb2,tb3,tb4';    
$value2= 'th1,th2,th3,th4';

and all I want to do is insert it into MySql (via PHP) like this:

+---------+-------------+-------------+
|      id |         tb |      th      |
+---------+-------------+-------------+
|      11 |        tb1 |      th1     |
|      11 |        tb2 |      th2     |
|      11 |        tb3 |      th3     |
|      11 |        tb4 |      th4     |
+---------+------------+--------------+

I will appreciate any help I get.

https://stackoverflow.com/questions/9192740/insert-multiple-rows-into-mysql-items-separated-by-comma?rq=1 — Anitha S, Mar 29 '18 at 07:16

ScaisEdge · Answer 1 · 2018-03-29T07:18:52.803

1

You could use batch insert

  Insert into my_table (id, tb,th)
  values ( 11 ,  'tb1' , 'th1' ),
         ( 11 ,  'tb2' , 'th2' ),
         ( 11 ,  'tb3' , 'th3' ),
         ( 11 ,  'tb4' , 'th4' )

edited Mar 29 '18 at 07:18

answered Mar 29 '18 at 07:16

ScaisEdge

131,976
10
91
107

$value1= 'tb1,tb2,tb3,tb4'; $value2= 'th1,th2,th3,th4'; I will get values like this. – Anitha S Mar 29 '18 at 07:18
@AnithaS .. if you have an array then you should iterate over the array for build and assigning the proper values to the code .. – ScaisEdge Mar 29 '18 at 07:23

score 1 · Accepted Answer · answered Mar 29 '18 at 07:37

I think you have string type values so first explode it then insert see below code

$id='11';   
$value1= 'tb1,tb2,tb3,tb4';    
$value2= 'th1,th2,th3,th4';
$vals1= explode(',',$value1);
$vals2= explode(',',$value2);
$i=0;
foreach($vals1 as $val1)
{
  $ins="INSERT INTO my_table (id, tb, th)values ( ".$id." ,  '".$val1."' ,'".$vals2[$i]."' )";
  $i++;
}

Toine H · Answer 3 · 2018-04-05T14:03:30.390

Assuming you are using PDO for database communication and the amount of values in $value1 and $value2 will be equal:

<?php

$id = 11;
$value1 = 'tb1,tb2,tb3,tb4';
$value2 = 'th1,th2,th3,th4';
$values1 = explode(',', $value1);
$values2 = explode(',', $value2);
$sql = "INSERT INTO table (id, tb, th) VALUES (:id, :value1, :value2 )";
$stmt = $pdo->prepare($sql);
for ($i = 0; $i < count($values1) $i++)
{
    $stmt->bindParam('id', $id);
    $stmt->bindParam('value1', $values1[$i]);
    $stmt->bindParam('value2', $values2[$i]);
    if($stmt->execute()){
        echo "QUERY EXECUTED";
    }
}

?>

Edited for more efficient use of prepare

To get the most from prepared statements, the prepare should be done outside the loop and executed for each set of values. — Nigel Ren, Mar 29 '18 at 07:37

Insert multiple rows into mysql

3 Answers3