How to load JSON in Mysql, including JSON specific fields

Question

I wonder if someone could help me with a question that’s been slaughtering me?

First, I am trying to upload this JSON to Mysql

{
    "business_id": "fNGIbpazjTRdXgwRY_NIXA",
    "full_address": "1201 Washington Ave\nCarnegie, PA 15106",
    "hours": {

    },
    "open": true,
    "categories": ["Bars",
                   "American (Traditional)",
                   "Nightlife",
                   "Lounges",
                   "Restaurants"],
    "city": "Carnegie",
    "review_count": 5,
    "name": "Rocky's Lounge",
    "neighborhoods": [],
    "longitude": -80.084941599999993,
    "state": "PA",
    "stars": 4.0,
    "latitude": 40.396468800000001,
    "attributes": {
        "Alcohol": "full_bar",
        "Noise Level": "average",
        "Music": {
            "dj": false,
            "background_music": true,
            "karaoke": false,
            "live": false,
            "video": false,
            "jukebox": false
        },
        "Attire": "casual",
        "Ambience": {
            "romantic": false,
            "intimate": false,
            "touristy": false,
            "hipster": false,
            "divey": false,
            "classy": false,
            "trendy": false,
            "upscale": false,
            "casual": false
        },
        "Good for Kids": true,
        "Wheelchair Accessible": false,
        "Good For Dancing": false,
        "Delivery": false,
        "Coat Check": false,
        "Smoking": "no",
        "Accepts Credit Cards": true,
        "Take-out": false,
        "Price Range": 2,
        "Outdoor Seating": false,
        "Takes Reservations": false,
        "Waiter Service": true,
        "Caters": false,
        "Good For": {
            "dessert": false,
            "latenight": false,
            "lunch": false,
            "dinner": false,
            "brunch": false,
            "breakfast": false
        },
        "Parking": {
            "garage": false,
            "street": false,
            "validated": false,
            "lot": false,
            "valet": false
        },
        "Has TV": true,
        "Good For Groups": true
    },
    "type": "business"
}

And I’m using the following code

<?php
try 
{
    $conn = new PDO("mysql:host=localhost:118;dbname=mydb", "root", "1234");
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully"; 
}
catch(PDOException $e)
{
    echo "Connection failed: " . $e->getMessage();
}

//read the json file contents
$jsondata = file_get_contents('c:\yelp_academic_dataset_business.json');

ini_set('memory_limit', '512M');
//convert json object to php associative array
$data = json_decode($jsondata, true);

//get the employee details
$idBusiness = $data['business_id'];
$name = $data['name'];
$neighborhoods = $data['neighborhoods'];
$full_address = $data['full_address'];
$city = $data['city'];
$state = $data['state'];
$latitude = $data['latitude'];
$longitude = $data['longitude'];
$stars = $data['stars'];
$review_count = $data['review_count'];
$open = $data['open'];
$procedure = $conn -> prepare("INSERT INTO business(business_id, name, neighborhoods, full_address, city, state, latitude, longitude, stars, review_count, open)
VALUES('$idBusiness', '$name', '$neighborhoods', '$full_address', '$city', '$state', '$latitude', '$longitude', '$stars', '$review_count', '$open')");
$procedure -> execute(); 
?>

You’re making a mistake in business_id, you can’t enter it, is there something wrong with the code? or is it the relational model that makes the mistake? the error is as follows:

Fatal error: Uncaught Exception 'Pdoexception' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'business_id' in 'field list'' in C: wamp www Yelp_mysql_business.php on line 50

---

Second, I have 2 tables in the relational model with the name, "complex attributes" containing id and Designation and "simple attributes" containing id, designation and value.

I wanted a way to be able to save in the table designation field complex attributes the value "good_for", "music" etc... which unfold in simple attributes as in the case of "music", dj, karaoke, etc.

But with the above code only allows me to store the JSON value and not the same attributes.

For example for Attributes you can’t load into the relational model so I had to create 2 tables related to the Business table, "Complex Attributes" that are those that have several attributes within this as is the case of Music, Ambience, and "Simple attributes" as in the case of Alcohol, Oise level. In the simple attributes I created 2 fields, designation and value, which translates into Alcohol being the designation and full bar the value. My question is how to break the data so that Alcohol and these simple attributes are loaded into the designation field in the "Simple attributes" table in Mysql.

This is my relational model, there are other JSON like Tip, Review, User and Check-in, but knowing how to load for business and how to solve the problem of attributes later for the rest could already do.

Can someone please help me, it’s important.

Answer 1

The error you are getting:

"Fatal error: Uncaught Exception 'Pdoexception' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'business_id' in 'field list'' in C: wamp www Yelp_mysql_business.php on line 50"

It’s because of that line here:

$procedure = $conn->prepare("INSERT INTO business(business_id, name,
 neighborhoods, full_address, city, state, latitude, longitude, stars,
 review_count, open)
VALUES('$idBusiness', '$name', '$neighborhoods', '$full_address', '$city',
 '$state', '$latitude', '$longitude', '$stars', '$review_count', '$open')");

Note that the first line is using the business_id as column name, but column name is idBusiness. I believe it was simply a confusion/conflict between the ID name in JSON and Mysql.

Then modify your SQL to look like this:

INSERT INTO business(idBusiness, name, ... )
VALUES('$idBusiness', '$name', ... )