How to insert arrays of same values in json format into a column php mysql

Question

I have a json format as below

{
    "results": [
{
"url": "someurl",
"imageUrl": "somepath",
    "tickets": [
                {
                    "id": 98655,
                    "name": "ADULT TICKET",
                    "soldOut": false,
                    "provisionallySoldOut": false,
                    "price": 100,
                    "salesStart": "2018-06-26T11:52:00",
                    "salesEnd": "2018-07-28T17:00:00",
                    "description": "",
                    "donation": false,
                    "vendorTicket": false
                },
                {
                    "id": 98656,
                    "name": "UNDER 12",
                    "soldOut": false,
                    "provisionallySoldOut": false,
                    "price": 80,
                    "salesStart": "2018-06-26T11:53:00",
                    "salesEnd": "2018-07-28T17:00:00",
                    "description": "",
                    "donation": false,
                    "vendorTicket": false
                }
            ]
},
{
"url": "someurl",
"imageUrl": "somepath",
"tickets": [
                {
                    "id": 98735,
                    "name": "ADULT EARLY BIRD",
                    "soldOut": false,
                    "provisionallySoldOut": false,
                    "price": 150,
                    "salesStart": "2018-06-26T12:47:00",
                    "salesEnd": "2018-08-12T10:00:00",
                    "description": "",
                    "donation": false,
                    "vendorTicket": false
                },
                {
                    "id": 98736,
                    "name": "UNDER 12 - EARLY BIRD",
                    "soldOut": false,
                    "provisionallySoldOut": false,
                    "price": 120,
                    "salesStart": "2018-06-26T12:47:00",
                    "salesEnd": "2018-08-12T10:00:00",
                    "description": "",
                    "donation": false,
                    "vendorTicket": false
                }
            ]
}
],
"pageSize": 10,
    "pages": 1,
    "records": 2,
    "extras": null,
    "message": null,
    "statusCode": 0
}

I have a table in my database as "event_tickets" and it has columns of name and price i want to show the names ADULT TICKET and UNDER 12 in the same column separated by comma and prices 100 and 80 in the same column under price separated by comma

i want it to show something like this on my table.To make it more clear I want to INSERT the value as mentioned in the table above using php. The table format should be like so

name                                      |   price
ADULT TICKET,UNDER 12                     |  100,80
ADULT EARLY BIRD, UNDER 12 - EARLY BIRD   |  150,120

is there any way i can achieve that using php?

because when i try to run my code the names show on different column and prices in different columns

Thanks in advance

Jeppsen · Accepted Answer · 2018-07-05 13:50:38Z

1

One solution in PHP:

// This is your json data.
$jsonData = '{
    "results": [
{
"url": "someurl",
"imageUrl": "somepath",
    "tickets": [
                {
                    "id": 98655,
                    "name": "ADULT TICKET",
                    "soldOut": false,
                    "provisionallySoldOut": false,
                    "price": 100,
                    "salesStart": "2018-06-26T11:52:00",
                    "salesEnd": "2018-07-28T17:00:00",
                    "description": "",
                    "donation": false,
                    "vendorTicket": false
                },
                {
                    "id": 98656,
                    "name": "UNDER 12",
                    "soldOut": false,
                    "provisionallySoldOut": false,
                    "price": 80,
                    "salesStart": "2018-06-26T11:53:00",
                    "salesEnd": "2018-07-28T17:00:00",
                    "description": "",
                    "donation": false,
                    "vendorTicket": false
                }
            ]
},
{
"url": "someurl",
"imageUrl": "somepath",
"tickets": [
                {
                    "id": 98735,
                    "name": "ADULT EARLY BIRD",
                    "soldOut": false,
                    "provisionallySoldOut": false,
                    "price": 150,
                    "salesStart": "2018-06-26T12:47:00",
                    "salesEnd": "2018-08-12T10:00:00",
                    "description": "",
                    "donation": false,
                    "vendorTicket": false
                },
                {
                    "id": 98736,
                    "name": "UNDER 12 - EARLY BIRD",
                    "soldOut": false,
                    "provisionallySoldOut": false,
                    "price": 120,
                    "salesStart": "2018-06-26T12:47:00",
                    "salesEnd": "2018-08-12T10:00:00",
                    "description": "",
                    "donation": false,
                    "vendorTicket": false
                }
            ]
}
],
"pageSize": 10,
    "pages": 1,
    "records": 2,
    "extras": null,
    "message": null,
    "statusCode": 0
}';

$servername = '';
$username   = '';
$password   = '';
$dbname     = ''; 

// Connect to your database
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$formatedData = json_decode($jsonData);

foreach($formatedData->results as $result) {

    $names  = array();
    $prices = array();

    foreach($result->tickets as $ticket) {
        // Add each price and name to arrays
        $names[]  = $ticket->name;
        $prices[] = $ticket->price;
    }

    // Glue the arrays with comma into strings
    $combinedNames  = implode(',', $names);
    $combinedPrices = implode(',', $prices);

    // Perform your database query
    $sql = "INSERT INTO your_table 
         (name, price) 
       VALUES
         ('$combinedNames', '$combinedPrices')";
    // Remember to watch out for SQL injections
    if ($conn->query($sql) === true) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }


}
$conn->close();

edited Jul 5, 2018 at 13:50

answered Jul 3, 2018 at 12:31

Jeppsen

4973 silver badges10 bronze badges

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

6 Comments

Praneet Singh Roopra Over a year ago

i have edited my question a bit please can you go through it again??

Jeppsen Over a year ago

I've edited the answer to fit the new json format. We needed another foreach loop for the results array.

Praneet Singh Roopra Over a year ago

Yes thanks bro i figured it out already .. but I really appreciate your time and effort, it was your previous answer that helped me figure it out. .. thanks a ton :)

Jeppsen Over a year ago

Great to be able to help. Can you mark my answer as the most useful answer?

Praneet Singh Roopra Over a year ago

sure i am new to stackoverflow can you tell me how to do that??

|

Collectives™ on Stack Overflow

How to insert arrays of same values in json format into a column php mysql

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related