ajax动态对象（关联数组）通过PDO分成两个MySQL表

Question

I have a js dynamic object that I "stringified" via JSON.stringify(obj_to_str);

I'm able to receive this object in a php file under the super-global $_POST. The object's format (once in the $_POST super-global) looks like this: {"6869lx":"mds","2416ex":"mdr","5126mv":"mdf","7079px":"dtr","5350ds":"dts"}. I'd like to be able to insert each key-value pare into a different column -for instance: 6869lx into a column called 'key', and on the same row on a different column called 'cat', mds.

The problem I'm having is to prepare a dynamic PDO statement to insert this js object into two different MySQL columns (considering the fact that I do not know the length of the object before-hand). I also read that if the object is very large a for loop (or any type of php loop) would slow down the insertion process.

Is it possible ? What would be the best way of doing it? Thanks much in advance.

Answer 1

Here's how you do it:

$data = json_decode($_POST['data'], true);
$stmt = $pdo->prepare("INSERT INTO yourtable (key, cat) VALUES (:key, :cat)");
$stmt->bindParam(':key', $key);
$stmt->bindParam(':cat', $cat);
foreach ($data as $key => $cat) {
    $stmt->execute();
}

To do a multi-row insert, you can do:

$data = json_decode($_POST['data'], true);
$values = implode(', ', array_fill(0, count($data), '(?, ?)'));
$stmt = $pdo->prepare("INSERT INTO yourtable (key, cat) VALUES " . $values);
$i = 1;
foreach ($data as $key => $cat) {
    $stmt->bindValue($i++, $key);
    $stmt->bindValue($i++, $cat);
}
$stmt->execute();