Json to mysql. how do i transform json object to sql query?

Question

I have a json object that looks like this:

"Sunday":[
        {
            "low":"00:15",
            "high":"00.45",
            "mid":["00:30"]
        },
        {
            "low":"01:15",
            "high":"02.45",
            "mid":["01:30","01:45","02:00","02:15","02:30"]
        }

    ],
    "Monday":[
        {
            "low":"00:15",
            "high":"00.45",
            "mid":["00:30"]
        },
        {
            "low":"01:15",
            "high":"02.45",
            "mid":["01:30","01:45","02:00","02:15","02:30"]
        }

    ]

i am using json_decode() method to make it an array which is something like this:

Array ( [Sunday] => Array ( [0] => Array ( [low] => 01:15 [high] => 02.45 [mid] => Array ( [0] => 01:30 [1] => 01:45 [2] => 02:00 [3] => 02:15 [4] => 02:30 ) ) [1] => Array ( [low] => 00.45 [high] => 00:30 [mid] => Array ( [0] => 01:30 [1] => 01:45 [2] => 02:00 [3] => 02:15 [4] => 02:30 ) ) ) )

the i am successfully able to get the above array from mysql and pass it to javascript with no issues, but i am confused on how to put the same json array back to the mysql.

mysql table looks like this: |ID|Day|Low|High|Mid|

Please help me know how to best approach this problem.

Thanks

Max

Have you checked answer I gave you? Did it solve your problem? — Tpojka
– Tpojka, Commented May 16, 2016 at 14:11
Worked like magic. although i just wanted a clue; you gave me the complete solution :P Thanks a lot :) — Maxxer
– Maxxer, Commented May 16, 2016 at 18:54

Tpojka · Accepted Answer · 2016-05-15 15:39:19Z

Reading provided input data and output (DB table expectation), made this solution for you:

Your input array from JSON:

public function arr2ins()
{
    $arr = [
        "Sunday" => [
            [
                'low' => "00:15", 
                'high' => "00.45",
                'mid' => [
                    "00:30",
                ],
            ],
            [
                'low' => "01:15", 
                'high' => "02.45",
                'mid' => [
                    "01:30",
                    "01:45",
                    "02:00",
                    "02:15",
                    "02:30",
                ],
            ],
        ],
        "Monday" => [
            [
                'low' => "00:15", 
                'high' => "00.45",
                'mid' => [
                    "00:30",
                ],
            ],
            [
                'low' => "01:15", 
                'high' => "02.45",
                'mid' => [
                    "01:30",
                    "01:45",
                    "02:00",
                    "02:15",
                    "02:30",
                ],
            ],
        ],
    ];

    $insert = array();

    foreach($arr as $k1 => $v1)
    {
        foreach($v1 as $k2 => $v2)
        {
            $insert[] = array(
                'ID' => '', 
                'Day' => $k1, 
                'Low' => $v2['low'],
                'High' => $v2['high'],
                'Mid' => is_array($v2['mid']) ? implode(',', $v2['mid']) : $v2['mid'],
            );
        }
    }

    echo '<pre>',var_dump($insert);

}

Collectives™ on Stack Overflow

Json to mysql. how do i transform json object to sql query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related