Insert multidimensional array data into MySQL DB

Question

I need to insert array data into MySQL DB. My code is provided below. The problem is that query is equal to

INSERT INTO MyTab (Array) VALUES (Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array)

So, why do I get Array instead of array values?

$columns = array();
    $values = array();

    $columns[] = array('Num','appearanceTime');

    $curr_time = new DateTime();
    while($row=mysql_fetch_assoc($result_arr)) {
        $values[] = array($row['Num_arr'],$curr_time);
    }

    $cols = implode(",",$columns);
    $vals = implode(",",$values);

$query = "INSERT INTO `MyTab` ($cols) VALUES ($vals)";

UPDATE This code returns Internal Server Error at the line $vals = implode(...).

$columns = array('Num','appearanceTime','earliestTime'); $values = array();

$curr_time = new DateTime();
while($row=mysql_fetch_assoc($result_arr)) {
    $values[] = array($row['Num_arr'],$curr_time,$row['ETA']);
}

$cols = implode(",",$columns);

function get_values($arr) {
return '(' . implode(',', $arr) . ')';
}

$vals = implode(',', array_map('get_values', $values));

$query_queue = "INSERT INTO `MyTab` ('" . $cols . "') VALUES ('" . $vals . "')";

Try to remove the brackets after $columns

Peter
– Peter

2012-08-26 15:28:39 +00:00
Commented Aug 26, 2012 at 15:28 — Peter
– Peter, Commented Aug 26, 2012 at 15:28

Ry- · Accepted Answer · 2012-08-26 15:53:08Z

2

The values inside the arrays are arrays. You need to implode each of them, too:

$vals = implode(',', array_map(function($arr) {
    return '(' . implode(',', $arr) . ')';
}, $values));

As for the columns, I think you want:

$columns = array('Num','appearanceTime');
$values = array();

Not:

$columns = array();
$values = array();

$columns[] = array('Num','appearanceTime');

You'll also need to quote everything to put it in the query. You should use PDO or MySQLi and prepared statements instead of mysql_ if you can.

Given PHP 5.2, the first example needs to be changed to:

function implode_comma($arr) {
    return '(' . implode(',', $arr) . ')';
}

# ...

$vals = implode(',', array_map('implode_comma', $values));

edited Aug 26, 2012 at 15:53

answered Aug 26, 2012 at 15:29

Ry-♦

226k56 gold badges496 silver badges504 bronze badges

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

10 Comments

You Kuper Over a year ago

Thanks,I tried your updates. This one contains some syntax error: $vals = implode(',', array_map(function($arr) { return '(' . implode(',', $arr) . ')'; }, $values)); Do you know what's the problem here?

You Kuper Over a year ago

Also, what is '$arr'? Is it a new variable? Or does it correspond to some of my variables?

Ry- Over a year ago

@YouKuper: That's an anonymous function. Which version of PHP are you using? If it's less than 5.3, you'll need to change that into a real function and use array_map('your_function', $values) instead.

Ry- Over a year ago

@YouKuper: You didn't pay attention. It's not get_values($values). It's 'get_values'.

You Kuper Over a year ago

Ok,I changed to 'get_values' and I placed the function get_values before $vals = implode(...); Now I get Internal Server Error at the line $vals = implode(...); Please see the complete updated code.

|

Collectives™ on Stack Overflow

Insert multidimensional array data into MySQL DB

1 Answer 1

10 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Related