Insert Array Data from File into MYSQL using PHP-CodePudding

I am having trouble trying to connect to a MySQL DB to insert certain JSON values from a .json file.

I am still fairly new to working with data, connecting to a DB via PHP and such.

The db is in the same cpanel/host/server as where this file is found. Please let me know if I need to change, add or improve anything.

What I am trying to do, is read the file.json and then insert those entries into a remote DB that is on my server.

What I am looking for is how to insert these values into insert the data into a MYSQL, not print it on a page.

This question doesn't answer my question: How to extract and access data from JSON with PHP?

    <!DOCTYPE html>
<html>
<body>
<h1>Insert Data into DB</h1>
<?php
    //connect to mysql db
    $con = mysql_connect("idg","ThePass","") or die('Could not connect: ' . mysql_error());
    //connect to the employee database
    mysql_select_db("dbtest", $con);

    //read the json file contents
    $jsondata = file_get_contents('http://path.to.file.com/file.json');
    
    //convert json object to php associative array
    $data = json_decode($jsondata, true);
    
    //convert json object to php associative array
    $data = json_decode($jsondata, true);
    
    foreach ($data as $jsons)
     {
          $id = null;
    $fname = null;
    $lname = null;
    $email = null;
    $phone = null;
    $date = null;
    $state = null;
    
    foreach($jsons as $key => $value)
     {
         if($key == 'id') {
             $id = $value;
         }
         
          if($key == 'date_created') {
             $date = $value;
         }
         
          if($key == '1') {
             $email = $value;
         }
         
          if($key == '3.3') {
             $fname = $value;
         }
         
          if($key == '3.6') {
             $lname = $value;
         }
         
         if($key == '5') {
             $phone = $value;
         }
         
         if($key == '6') {
             $state = $value;
         }
    
     }
    //insert into mysql table
    $sql = "INSERT INTO contact(id, date, first, last, phone, email, state)
    VALUES('$id', '$date', '$fname', '$lname', '$phone', '$email', '$state')";
    if(!mysql_query($sql,$con))
    {
        die('Error : ' . mysql_error());
    }
    }
?>

</body>
</html>

here is an example of a JSON entry

{
    "total_count": 209,
    "entries": [
        {
            "id": "544537",
            "form_id": "2",
            "post_id": null,
            "date_created": "2022-10-21 17:26:18",
            "date_updated": "2022-10-21 17:26:18",
            "is_starred": "0",
            "is_read": "0",
            "ip": "68.126.222.136",
            "source_url": "/contact\",
            "user_agent": "Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/106.0.0.0 Safari\/537.36",
            "currency": "USD",
            "payment_status": null,
            "payment_date": null,
            "payment_amount": null,
            "payment_method": null,
            "transaction_id": null,
            "is_fulfilled": null,
            "created_by": null,
            "transaction_type": null,
            "status": "active",
            "1": "[email protected]",
            "2": "Contractor\/GC",
            "3.3": "first",
            "3.6": "last",
            "4": "Company",
            "5": "(111)132-4567",
            "6": "California",
            "7": "I am seeking for a bid to furnish and install",
            "is_approved": "3",
            "3.2": "",
            "3.4": "",
            "3.8": "",
            "8": "",
            "workflow_current_status_timestamp": false,
            "gpnf_entry_parent": false,
            "gpnf_entry_parent_form": false,
            "gpnf_entry_nested_form_field": false
        },

CodePudding user response：

You should do:

foreach ($data['entries'] as $jsons)

because you loop through entries. And you also have:

$data = json_decode($jsondata, true);

twice.