Rik Nijdeken Rik Nijdeken - 1 year ago 70
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: [
venue.location: [
description: [
logo: [
header: [
venue.country: [
venue.city: [
sort: [

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

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);
$sql = "TRUNCATE TABLE dance";
$stmt = $dbh->prepare($sql);
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) {
"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 Source

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'],
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download