Insert multidimensional array to mysql

Question

I want to insert multidimensional array to mysql using insert

with the below code it inserts as below..

"insert into table (col1) value(1)";
"insert into table (col2) value(2)";
"insert into table (col3) value(3)";
"insert into table (col4) value(6)";

...
"insert into table (col1) value(7)";
"insert into table (col2) value(5)";
"insert into table (col2) value(3)";
"insert into table (col2) value(8)";
...

instead it should insert

"insert into table (col1,col2,col3,col4,col5) values(1,2,3,6,7) "
"insert into table (col1,col2,col3,col4,col5) values(7,5,3,8) "
...


$res_arr= array(
    0=>array(1,2,3,6),
    1=>array(7,5,3,8),
2=>array(6,5,9,8),


....
10000
);

Below is the code I use

$maketbl = "CREATE  TABLE $table(";
//$maketemp .= " id int(11) , ";

for($i=0;$i<= 5;$i++)
{
$maketbl .= " COLUMN_$i varchar(128), ";
//echo "<br>";
} 
 $maketbl .=")";
 $maketbl = substr($maketbl,0,-3);
  $maketbl.="  )";
  mysql_query( $maketbl ) or die ( "Sql error : " . mysql_error ( ) );

  foreach ($res_arr as $k=>$subArray)
     {

     foreach ($subArray as $id=>$value)
     {
         @$avArray[$id] = $value;
         if(!empty($avArray[$id]))
         {



        mysql_query("insert into $table(COLUMN_$id) values($avArray[$id]))"; 


          }

     }
     }

The count 5 is dynmaic it can contain uptil 16 columns

mark rammmy
– mark rammmy

2014-02-08 13:26:29 +00:00
Commented Feb 8, 2014 at 13:26 — mark rammmy
– mark rammmy, Commented Feb 8, 2014 at 13:26

Sanoob · Accepted Answer · 2014-02-08 14:57:26Z

<?php
 $values= array(0=>array(1,2,3,6),
                1=>array(7,5,3,8),
                2=>array(6,5,9,8));

$cols = array('a','b','c','d');

$table = 'test_table';

$col_limit = 4;


    $sql_sub_str = "insert into $table (";

    for($i=0 ; $i<$col_limit;$i++){
        $sql_sub_str.= $cols[$i]; // addes col name
        if($i!=$col_limit-1)  $sql_sub_str.= ","; // addes comma b/n each colum except last one

    }
    $sql_sub_str .= ") "; // Close braches

$sql_str = '';
foreach ($values as $value) {
    $sql_str .= $sql_sub_str;
    $sql_str .= 'values (';
    for ($i=0; $i<$col_limit;$i++){
        $sql_str.= "'$value[$i]'"; // addes col values
        if($i!=$col_limit-1)  $sql_str.= ","; // addes comma b/n each value except last one
    }
    $sql_str .= '); '; // closes brach 
}

    echo $sql_str;
?>

I think this code will help you.. it makes first part of string insert into table (col1,col2,col3,col4,col5) using a loop then that string will be used to make values part

values(1,2,3,6,7); "

If you like to can add new line between each statements

Output:

insert into test_table (a,b,c,d) values ('1','2','3','6'); insert into test_table (a,b,c,d) values ('7','5','3','8'); insert into test_table (a,b,c,d) values ('6','5','9','8');

Collectives™ on Stack Overflow

Insert multidimensional array to mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related