Php, Mysql insert dynamic query

Question

I have an array like this:

stdClass Object
(
    [id] => 1
    [name] => John
    [email] => lcook0@mail.co.uk
    [website] => google.com
    [phone] => 0-(343)926-3114
    [address] => 620 Main Center
    [city] => Fukushima-shi
    [country] => JP
    [zip] => 
    [vat] => 123456
    [status] => 1
    [created] => 2016-03-10 15:56:44
)

but I will not know the keys and values, since each array will be different. What I need is to insert array into database. Something like:

$keys = '';
$values = '';
foreach ($array as $key => $value) {
    $keys .= implode(",", $key);
    $values .= implode(",", $value);
}
"INSERT INTO `users` ($keys) VALUES ($values);"

My example does not work, since implode requires an array

Possible duplicate of [Insert array into MySQL database with PHP](http://stackoverflow.com/questions/10054633/insert-array-into-mysql-database-with-php) — CodeGodie, May 17 '16 at 14:38
can you add the DDL of the target table you wanna use? The structure of the array will always be the same, only the content will varry or does even the structure of the array will varry? — Michael, May 17 '16 at 14:46

Niet the Dark Absol · Accepted Answer · 2016-05-17T14:48:31.633

0

Consider using the alternative insert format: INSERT INTO tbl SET x=1, y=2...

This is also a great opportunity for prepared queries:

$mapped = array_map(function($k) {return "`".$k."` = ?";},array_keys($array));
$stmt = $dbconnection->prepare("INSERT INTO `users` SET ".implode(", ",$mapped);
$stmt->execute(array_values($array));

The exact code, including how to pass the parameter values, will vary but the above is PDO-ish in style. In your example, you'd end up with:

INSERT INTO `users` SET `id` = ?, `name` = ? ...

And the values 1, John ... would be sent appropriately.

edited May 17 '16 at 14:48

answered May 17 '16 at 14:25

Niet the Dark Absol

320,036
81
464
592

I think your list line of code is wrong $stmt->execute(array_values($mapped)); it should be $stmt->execute(array_values($array)); – Alko May 17 '16 at 14:32
@Alko Indeed, well spotted! – Niet the Dark Absol May 17 '16 at 14:48

score 0 · Answer 2 · answered May 17 '16 at 14:47

The problem in your code is that you are imploding a string rather than an array. Do it in this manner:

foreach ($obj as $key => $value) {
    $keys[] = "`$key`";
    $values[] = "'$value'";
}

$keys = implode(",", $keys);
$values = implode(",", $values);

$sql = "INSERT INTO `users` ($keys) VALUES ($values);";

Php, Mysql insert dynamic query

2 Answers2