Is there any way to insert array element to mysql database

Question

I am little confuse about to insert the array element in mysql

i have a array as following
  $a = array(
        '1' => 'a',
        '2' => 'b',
        '3' => 'c',
        '4' => 'd',
        '5' => 'e'
        '6' => 'f'
    );

and i have a table field id1,id2,...may be

id1   id2
a      b
c      d
e      f

can any one help me out with php code.

How does your query and the code you have written so far look? Please past it here, so we can help you improve it. — Peon, Dec 05 '13 at 12:16
Do you want to store array values in 2 columns (only) table? — Kumar V, Dec 05 '13 at 12:19
no multiple column, as the column increase than the data also divided as well — madhurjya, Dec 05 '13 at 12:21
If column count is dynamic, then you have to get the column count "n". Using For loop with incremental value "n", you can insert a record on each loop run. It's just an idea. Not an ultimate solution. — Kumar V, Dec 05 '13 at 12:24
Are you trying to insert array as value in a field or you want to create columns in table dynamically? — Yalamber, Dec 05 '13 at 12:34
@ askkirati i am trying to insert array as value to table but the table has dynamic columns — madhurjya, Dec 05 '13 at 12:50

score 2 · Answer 1 · answered Dec 05 '13 at 12:17

2

Have a look at this short tutorial. It might be just what you need: http://99webtools.com/how-to-store-array-mysql.php

answered Dec 05 '13 at 12:17

Severin

8,508
14
68
117

score 0 · Answer 2 · answered Dec 05 '13 at 12:30

If the table persists out of 2 columns only, id1 and id2

You could do the following:

$insertVals = ''; //Values go here,
$insertCols = ''; //Column names go here, alternating from id1 to id2

$counter = 0; //Will be used to iterate odd and even.

foreach ($a as $value) {
    $insertVals .= "'".$value."',"; //Comma at the end is needed.
    if ($counter % 2 == 0) //if even basicly
      $insertCols .= 'id1,'; //DB fields do not require quotes, backticks are optional
    else
      $insertCols .= 'id2,';
    $counter++ //Increment counter to cause odd values to occur.
}

//Remove last comma's from both strings.
$insertVals = substr($insertVals, 0, -1); //-1 removes the last char in substr.
$insertCols = substr($insertCols, 0, -1);

$query = "INSERT INTO yourtable ({$insertCols}) VALUES ({$insertVals})";

this should do the trick. Let me know if there are any errors you get since this is written straight out of the head and untested.

Regards, Sidney Liebrand

I really appreciate your effort but the used to iterate odd and even is not the exact way as i have multiple of columns. — madhurjya, Dec 05 '13 at 12:41

Rooxo · Answer 3 · 2013-12-05T12:49:53.503

0

$a = array(
        '1' => 'a',
        '2' => 'b',
        '3' => 'c',
        '4' => 'd',
        '5' => 'e'
        '6' => 'f'
    );

$mysqli = new mysqli("host", "user", "pw", "db");

foreach($a as $insert)
{
   $query = INSERT INTO table $insert[1],$insert[2];
   $mysqli=mysql_query($query);
}

edited Dec 05 '13 at 12:49

answered Dec 05 '13 at 12:41

Rooxo

118
8

[see reasons not use `mysql_*`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) – IROEGBU Dec 05 '13 at 12:45
fixed, changed to msqli – Rooxo Dec 05 '13 at 12:50
This is not any better... Learn about [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) and [how to prevent it](http://stackoverflow.com/a/12202218/747609) – IROEGBU Dec 05 '13 at 12:54
What does this have to do with the question? I just wanted to show what he needs to do, not how he should implement it in his actual code – Rooxo Dec 05 '13 at 12:56
*sigh* do what you like. – IROEGBU Dec 05 '13 at 13:03

Kumar V · Answer 4 · 2013-12-05T13:08:48.820

Try this code: It's untested

This will work for any number of columns.

<?php

$a = array(
    '1' => 'a',
    '2' => 'b',
    '3' => 'c',
    '4' => 'd',
    '5' => 'e'
    '6' => 'f'
);
$col_cnt = 3; // get column count from mysql query.
$temp = array();
for($i=0;$i<$col_cnt;$i++)
$temp[] = ''; //initialize the array. If no value in "a", then empty will inserted

for($t=1;$t<=count($a);$t+=$col_cnt)
{
$ins_arr = $temp; //initialize with empty values
for($j=0;$j<$col_cnt;$j++)
{
    $ins_arr[$j] = $a[$t+$j];
}
//
mysql_query("insert into tab1 values(".implode(",",$ins_arr).")");


}

?>

score 0 · Answer 5 · answered Dec 05 '13 at 12:52

0

You need to learn a little bit about Database_normalization http://en.wikipedia.org/wiki/Database_normalization. Basicly you create a new table for the array and tie them together with a key.

answered Dec 05 '13 at 12:52

Esben Skov Pedersen

4,437
2
32
46

score 0 · Accepted Answer · answered Dec 07 '13 at 07:56

Thanks to all of you, I really appreciate your effort to give your precious time to fix my situation, finally I had use the array_chunk function to divide my array to multiple sub array same with column no, than I insert the array value by sub key by for each loop.thank you thank you again.

Is there any way to insert array element to mysql database

6 Answers6