Insert Array Data from File into MYSQL using PHP

Question

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
   
$username = "user";
$password = "pass";


// Create connection
$con = new PDO('mysql:host=host;dbname=DBNAME', $username, $password);
   

    //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);
    
    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@email.com",
            "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
        },

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) — Dharman, Oct 21 '22 at 20:15
**Warning:** `mysql_*` extension is deprecated as of PHP 5.5.0 (2013), and has been removed as of PHP 7.0.0 (2015). Instead, either the [mysqli](https://www.php.net/manual/en/book.mysqli.php) or [PDO_MySQL](https://www.php.net/manual/en/book.pdo.php) extension should be used. See also the [MySQL API Overview](https://www.php.net/manual/en/mysqlinfo.api.choosing.php) for further help while choosing a MySQL API. — Dharman, Oct 21 '22 at 20:15
Dharman is right, use prepared statements and mysql_* is obsolete. — Valeriu Ciuca, Oct 21 '22 at 20:31
@ValeriuCiuca that must have been an accident. I removed it and still nothing — Jason Ayala, Oct 21 '22 at 21:36

Valeriu Ciuca · Accepted Answer · 2022-10-21T20:22:42.593

1

You should do:

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

because you loop through entries. And you also have:

$data = json_decode($jsondata, true);

twice.

edited Oct 21 '22 at 20:22

answered Oct 21 '22 at 20:12

Valeriu Ciuca

2,084
11
14

2

I'm glad! I don't know why people are downvoting. This was the issue, the pdo/mysqli advice is just best practices, not resolving the problem. But you should rewrite your query with mysqli or pdo prepare statements. – Valeriu Ciuca Oct 21 '22 at 21:52
1

I actually changed the connection to mysqli as well. I just didn't update the code here. I am running into another situation where I have to make sure to write new data and no overwrite the existing – Jason Ayala Oct 21 '22 at 22:02
2

You should add a constraint on your table, a unique key on the email column, or a compound key on a combination of columns. You could also make a query before the insert to check the existing data. – Valeriu Ciuca Oct 21 '22 at 22:05
1

I actually made the ID being primary on the table, should I also make it unique? I will look into how to check data before the query. but any direction would be great since this is something new for me – Jason Ayala Oct 21 '22 at 22:13
2

You should add a unique key to everything that must be unique. It's your decision. – Valeriu Ciuca Oct 21 '22 at 22:15
1

So I do have one more issue with this. So you see how in the above example has 209 entries from the JSON? on the initial dump into the DB it only dumped 11 entries into the new/Clean DB. is there something I am missing? – Jason Ayala Oct 24 '22 at 21:07
2

Print every SQL query from the loop.. maybe you have a constraint that breaks the loop, a duplicate line or something – Valeriu Ciuca Oct 24 '22 at 21:20
1

I actually did that and I see over 200 entries when I print it but in the DB I only see 10 rows – Jason Ayala Oct 24 '22 at 21:24
1

I have pasted the new code in my new question so you can see for yourself. https://stackoverflow.com/questions/74187090/not-all-entries-json-inserted-into-empty-mysql-db – Jason Ayala Oct 24 '22 at 21:40

Insert Array Data from File into MYSQL using PHP

1 Answers1