0

I have a JSON file which contains data that i want to store in two different database in sql.

The json file looks like this:

[
    {
        "title": "CONSERVE IT LTD",
        "address": "12 Truman Ave (10) ",
        "phones": [
            {
                "name": "telephone_1",
                "number": "876-754-0220"
            },
            {
                "name": "telephone_2",
                "number": "876-754-0221"
            }
        ],
        "website": "www.conserveitja.com",
        "page": 1
    },
    {
        "title": "Consie Walters Cancer Hospital",
        "address": "22 Deanery Rd (3) ",
        "phones": [
            {
                "name": "telephone_1",
                "number": "876-930-5016"
            }
        ],
        "page": 1
    },
...
]

I managed to store title, address, website and page in my 1st table in the database but now I want to store in a different table in the same database the phones for each business.

Here is the code I am using to store the information for the first table and I little code for the 2nd table.

$connect = mysqli_connect("localhost", "root", "1234", "businesses"); //Connect PHP to MySQL Database
    $query = '';
    $query_phones='';
    $table_data = '';
    $filename = "businesses.json";
    $businesses = file_get_contents($filename);
    $business = json_decode($businesses, true);
    foreach($business as $row)
    {
        $query .= "INSERT INTO business(title, address, website, page) VALUES ('".$row["title"]."', '".$row["address"]."', '".$row["website"]."', '".$row["page"]."'); ";
        //data that i will show on page
        $table_data .= '
            <tr>
       <td>'.$row["title"].'</td>
       <td>'.$row["address"].'</td>
       <td>'.$row["website"].'</td>
       <td>'.$row["page"].'</td>
      </tr>
           ';
    }

Code for inserting the phone data in the second table in database 

foreach($business as $row)
    {
        $query_phones .="INSERT INTO business_phones(business_title, phone_number, phone_name) VALUES ('".$row["title"]."', '".$row["number"]."', '".$row["name"]."');";
    }

Structure of 1st table (business)

    1   idPrimary   int(11)             

    2   title   varchar(50)     

    3   address     varchar(50)     

    4   website     varchar(50)     

    5   page    int(11)

Structure of 2nd table (business_phones)

    1   idPrimary   int(11)             

    2   business_title  varchar(250)    

    3   phone_number    varchar(255)    

    4   phone_name  varchar(255)
Waaaaat
  • 634
  • 3
  • 14
  • 29
  • This is unstable and insecure. You need to implement prepared statements. – mickmackusa Nov 17 '19 at 13:35
  • 1
    If you are going to maintain any meaningful relationship between these tables, you are going to need the `LAST_INSERT_ID()` of the business before you can insert that business's phone numbers. https://stackoverflow.com/a/18069213/2943403 – mickmackusa Nov 17 '19 at 14:09

0 Answers0