codeRed codeRed - 3 months ago 34
JSON Question

Primary key ID field auto increment not allowing JSON import. PHP/PDO and MySQL

I am trying to import a JSON file using PHP/PDO into a MySQL database. It will import fine if there is no auto incrementing ID field but when I add the field, the JSON file will not import at all.

Table....

enter image description here

Id field set as Primary Key, Auto Increment.

Example JSON data...

{
"name": "Google",
"uri": "https://www.google.com",
"description": "The largest and most popular search engine in the world",
},
{
"name": "NFL",
"uri": "https://www.nfl.com",
"description": "The National Football League",
},
{
"name": "CNN",
"uri": "https://www.cnn.com",
"description": "Cable News Network",
},


Here is the PHP/PDO code I am using to import the fields...

$jsondata = file_get_contents('http://www.somewebsite/?format=json');
$data = json_decode($jsondata, true);

$stmt = $db->prepare("insert into bookmark values(?,?,?)");

foreach ($data as $row) {
$stmt->bindParam(1, $row['name']);
$stmt->bindParam(2, $row['uri']);
$stmt->bindParam(3, $row['description']);
$stmt->execute();
}

Answer

I believe you should change:

$stmt = $db->prepare("insert into bookmark values(?,?,?)");

to

$stmt = $db->prepare("insert (name,uri,description) bookmark values(?,?,?)");

or

$stmt =  $db->prepare("insert bookmark values(null,?,?,?)");
Comments