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 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
        },

Warning: You are wide open to SQL Injections and should use parameterized prepared statements instead of manually building your queries. They are provided by PDO or by MySQLi. 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. Escaping is not enough! — Dharman
– Dharman ♦, Commented Oct 21, 2022 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 or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API. — Dharman
– Dharman ♦, Commented Oct 21, 2022 at 20:15
Dharman is right, use prepared statements and mysql_* is obsolete. — Valeriu Ciuca
– Valeriu Ciuca, Commented Oct 21, 2022 at 20:31
@ValeriuCiuca that must have been an accident. I removed it and still nothing — Jason Ayala
– Jason Ayala, Commented Oct 21, 2022 at 21:36

Valeriu Ciuca · Accepted Answer · 2022-10-21 20:22:42Z

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, 2022 at 20:22

answered Oct 21, 2022 at 20:12

Valeriu Ciuca

2,10413 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Valeriu Ciuca Over a year ago

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.

Jason Ayala Over a year ago

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

Valeriu Ciuca Over a year ago

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 Over a year ago

You should add a unique key to everything that must be unique. It's your decision.

Valeriu Ciuca Over a year ago

Print every SQL query from the loop.. maybe you have a constraint that breaks the loop, a duplicate line or something

|

Collectives™ on Stack Overflow

Insert Array Data from File into MYSQL using PHP

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related