Insert array to mysqldb

Question

I'm trying to insert the following array ( from a html form ) but i believe i'm working with this array in a very awful way

The array:

Array (
    [0] => Array (
        [0] => local
        [1] => file
        [2] => a

    )
    [1] => Array (
        [0] => remote
        [1] => image
        [2] => b
    )
)

Actual code:

<?php
if(isset($_POST)==true && empty($_POST)==false){ 
$dataa = ($_POST['code_list']);
}
$data = array_chunk($_POST['code_list'],6);

foreach ($data as  $type=>$ba) {
    echo "INSERT INTO table(f1, f2, f3,) values (";
    foreach ($ba as $a) {
        echo "'$a',";
    }
    echo ");<br>";
}

?>

current output:

INSERT INTO table(f1, f2, f3,) values ('local','file','a',);
INSERT INTO table(f1, f2, f3,) values ('remote','image','b',);

What would be a decent way to do it?

EDIT : No need to show how to connect to a database, i just need to deal with the array data.

if you are adding all items as different row then its the correct way I assume. Only thing u need to take care taking out last extra , from the query — Abhik Chakraborty
– Abhik Chakraborty, Commented Jan 17, 2014 at 21:52

Funk Forty Niner · Accepted Answer · 2014-01-17 23:20:54Z

1

A few comments:

You should switch to PDO or mysqli and use prepared statements with bound variables. Now you have a serious sql injection problem;
You can combine multiple inserts in one insert statement: INSERT ... VALUES ('local','file','a'),('remote','image','b'),(etc.).
You could also prepare your statement once and then execute it multiple times but combining would be more efficient, especially with a growing number of inserts.

A simple (uncomplete and untested...) example in PDO:

$sql = 'INSERT INTO table(f1, f2, f3) VALUES ';
$values = array();
$valCount = 1;
foreach ($data as  $type=>$ba) {
  $row = array();
  foreach ($ba as $a) {
     $val = ":value{$valCount}";
     $row[] = $val;
     $values[$val] = $a;
     $valCount++;
  }
  $sql .= '(' . implode(', ', $row) . '),';
}
$sql = rtrim($slq, ',');

// execute the sql statement using PDO
$stmt = $db->prepare($sql);
$stmt->execute($values);

At the end of the loop and the trim, the sql should look like:

INSERT INTO table(f1, f2, f3) VALUES (:value1, :value2, :value3),(:value4, :value5, :value6)

And the value array should have the keys of :value1 - :value6 with your values.

edited Jan 17, 2014 at 23:20

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

answered Jan 17, 2014 at 21:56

jeroen

91.8k22 gold badges118 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

thclpr Over a year ago

Could you give an example how could i work the array to do use it?

jeroen Over a year ago

@Thales I have added an example, but it is untested so you would need to check if the sql string and the values array are built correctly.

thclpr Over a year ago

I just had to replace some variables but this worked better than i expected! thanks a lot

Collectives™ on Stack Overflow

Insert array to mysqldb

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related