使用PHP在MySQL数据库中插入JSON数据 [英] Insert JSON data in MySQL database with PHP

问题描述

我尝试使用PHP解析JSON文件. 然后，将提取的数据放入MySQL数据库中.

I tried to parse a JSON file using PHP. Then I would put the extracted data in a MySQL database.

这是我的JSON文件

    {"u":[
        {"text":"Chef salad is calling my name, I\u0027m so hungry!",
        "id_str":"28965131362770944",
        "created_at":"dom gen 23 24:00:00 +0000 2011",
        "user":
            {"id_str":"27144739",
            "screen_name":"LovelyThang80",
            "name":"One of A Kind"}},

        {....} //Other same code
    }

这是我的PHP代码

<?php

    //connect to mysql db

    //read the json file contents

    //convert json object to php associative array
    $data = json_decode($jsondata, true);

    //prepare variables for insert
    $sqlu = "INSERT INTO user(id_user, screen_name, name)
    VALUES ";
    $sqlt = "INSERT INTO tweet(id_user, date, id_tweet, text)
    VALUES ";

    //I analyze the whole array and assign the values to variables
    foreach ($data as $u => $z){
        foreach ($z as $n => $line){
            foreach ($line as $key => $value) { 
                switch ($key){
                    case 'text':
                        $text = $value;
                        break;
                    case 'id_str':
                        $id_tweet = $value;
                        break;
                    case 'created_at':
                        $date = $value;
                        break;
                    case 'user':
                        foreach ($value as $k => $v) {
                            switch($k){
                                case 'id_str':
                                    $id_user = $v;
                                    break;
                                case 'screen_name':
                                    $screen_name = $v;
                                    break;
                                case 'name':
                                    $name = $v;
                                    break;
                            }
                        }
                        //insert value into table
                        $sqlu .= "('".$id_user."', '".$screen_name."', '".$name."')";
                        break;
                }
            }
            //insert value into table
            $sqlt .= "('".$id_user."', '".$date."', '".$id_tweet."', '".$text."')";
        }
    }

?>

但是在表格中什么也没输入！

But in the table doesn't enter anything!

如果我尝试:

echo $sqlu;

输出:

INSERT INTO user(id_user, screen_name, name) 
VALUES ('27144739', 'LovelyThang80', 'One of A Kind')
INSERT INTO user(id_user, screen_name, name) 
VALUES ('27144739', 'LovelyThang80', 'One of A Kind')('21533938', 'ShereKhan77', 'Loki Camina Cielos ')
INSERT INTO user(id_user, screen_name, name) 
VALUES ('27144739', 'LovelyThang80', 'One of A Kind')('21533938', 'ShereKhan77', 'Loki Camina Cielos ')('45378162', 'Cosmic_dog', 'Pablo')

与

echo $sqlt;

为什么?

推荐答案

您忘记在值之间添加,:

INSERT INTO user(id_user, screen_name, name) VALUES ('27144739', 'LovelyThang80', 'One of A Kind'), ('21533938', 'ShereKhan77', 'Loki Camina Cielos ')

INSERT INTO user(id_user, screen_name, name) VALUES ('27144739', 'LovelyThang80', 'One of A Kind'), ('21533938', 'ShereKhan77', 'Loki Camina Cielos ')

并且您一次只能插入一个 INSERT语句.

And you can insert only one INSERT statement at once.

这篇关于使用PHP在MySQL数据库中插入JSON数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！