php - json转mysql。我如何将 json 对象转换为 sql 查询？

Question

我有一个看起来像这样的 json 对象:

"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"]
        }

    ]

我正在使用 json_decode() 方法使它成为一个像这样的数组:

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 ) ) ) ) 

我成功地从 mysql 中获取了上述数组并将其毫无问题地传递给 javascript，但是我对如何将相同的 json 数组放回 mysql 感到困惑。

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

请帮助我了解如何最好地解决这个问题。

谢谢

最大

Answer 1

读取提供的输入数据和输出(数据库表预期)，为您制定此解决方案:

来自 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);

}