Rik Nijdeken Rik Nijdeken - 3 months ago 6
JSON Question

Cant get right value from JSON file to MySQL server

I am trying to import data from an external API to my own MySQL server. But the problem is, I want to get a value from a specific field but it just sets it to 1 or 2.

This is an example of one of the JSON objects I am trying to import:

{
_index: "events",
_type: "event",
_id: "2290", **<-- THIS IS THE VALUE I WANT TO GET**
_score: null,
fields: {
genres: [
"["World Music","Dance","Theater"]"
],
name: [
"Festival Spoffin 2016"
],
start: [
"2016-08-25T11:00:00+0000"
],
venue.location: [
5.387848,
52.1563989
],
description: [
"TEXT"
],
logo: [
"URL"
],
header: [
"URL"
],
venue.country: [
"Nederland"
],
venue.city: [
"Amersfoort"
]
},
sort: [
1472122800000
]
},


But when I run my query that works good, here is the code:

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
$jsondata = file_get_contents('https://hugo.events/genre/dance/next/900/1');
$data = json_decode($jsondata, true);
$uname = '****';
$upass = '****';
$dbh = new PDO('mysql:host=localhost;dbname=wtd', $uname, $upass);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "TRUNCATE TABLE dance";
$stmt = $dbh->prepare($sql);
$stmt->execute();
if (!$stmt) {
echo "Statement failed";
} else {
$statement = $dbh->prepare("INSERT INTO dance(event_id, event_name, event_date, event_country, event_genres, event_desc, event_logo, event_header_pic)
VALUES(:eventid, :name, :date, :country, :genres, :desc, :logo, :header_pic)");
$id = $data[1]['hits']['hits'][1]['_id'];
foreach ($data[1]['hits']['hits'] as $row) {
$statement->execute(array(
"eventid" => $row['_id'][0], **<-- THIS IS THE COLUMN I WANT TO PUT IT IN**
"name" => $row['fields']['name'][0],
"date" => $row['fields']['start'][0],
"country" => isset($row['fields']['venue.country']) ? $row['fields']['venue.country'][0] : '',
"genres" => $row['fields']['genres'][0],
"desc" => $row['fields']['description'][0],
"logo" => $row['fields']['logo'][0],
"header_pic" => $row['fields']['header'][0]
));
}
echo "Data inserted into dance table";
}


It just returns this when I echo it as JSON and also shows the same in phpMyAdmin:

{
id: "1",
event_id: "2", **<-- THIS IS SUPPOSED TO BE THE VALUE OF _ID**
event_name: "Festival Spoffin 2016",
event_date: "2016-08-25T11:00:00+0000",
event_country: "Nederland",
event_genres: "["World Music","Dance","Theater"]",
event_desc: "TEXT",
event_logo: "URL",
event_header_pic: "URL"
},

Answer

The id is an integer, not an array, so you don't need the extra [0] at the end. Otherwise, you're just getting the first number in the integer.

"eventid" => $row['_id'],
Comments