最受欢迎

通过 PHP 脚本将 JSON 文件插入 MySQL

Inserting JSON file into MySQL via PHP Script

本文关键字：文件 插入 MySQL JSON PHP 脚本 通过 | 更新日期: 2023-09-27

我正在尝试通过php脚本将数据从JSON文件添加到MySQL中，并且在MySQL中得到一个空数组和以下错误。

我在windp上使用xampp lite 1.8

注意：未定义的变量：标签颜色 C：''xampp''htdocs''jsonreader''index.php 在第 64 行

注意：未定义的变量：标签在第 64 行的 C：''xampp''htdocs''jsonreader''index.php 中修改

注意：C：''xampp''htdocs''jsonreader''index.php 第 66 行中的数组到字符串转换 错误 :P roblem 添加到数据库中 您的 SQL 语法有错误;检查与您的MySQL服务器版本相对应的手册 在"订单，描述"附近使用正确的语法( 第 1 行的值("e"、"(">

源代码索引.php

    <?php
require_once('includes/conn.php');
/* $fo=fopen("epl.json","r");
$fr=fread($fo,filesize("epl.json"));
$data=json_decode($fr,true); */
//read the json file contents
 $jsondata = file_get_contents('epl.json');
 //convert json object to php associative array
$data = json_decode($jsondata,true);
//var_dump($jsondata);
/* if($data)
{
    echo "AWESOME";
}
exit(); */
 //get the table details 
    $short_desc=$data['short_desc'];
    $original_id = $data['tags']['original_id'];
  $name = $data['tags']['name'];
    $color = $data['tags']['color'];
    $modified = $data['tags']['modified'];
$abstract = $data['tags']['abstract'];
$category = $data['tags']['category'];
$tag = $data['tags']['tag'];
$order_in_category = $data['tags']['order_in_category'];
 $short_desc=$data['short_desc'];
$id = $data['topics']['id'];
$user_id = $data['topics']['user_id'];
$postedby = $data['topics']['postedby'];
$publishDate = $data['topics']['publishDate'];
$photoUrl = $data['topics']['photoUrl'];
$description = $data['topics']['description'];
$title = $data['topics']['title'];
$addedOn = $data['topics']['addedOn'];
 $topic_rating = $data['topics']['topic_rating'];
$topic_rate_num = $data['topics']['topic_rate_num'];
$tags = $data['topics']['tags'];
$url = $data['topics']['url'];
$photosUrl = $data['topics']['photosUrl'];
$youtubeUrl = $data['topics']['youtubeUrl'];
 $topicsmodified = $data['topics']['modified'];
 $mainTag = $data['topics']['mainTag'];
 $topicscolor = $data['topics']['color'];
 $hashtag = $data['topics']['hashtag'];
 $captionUrl = $data['topics']['captionUrl'];
$players = $data['topics']['players']; 
 $players=$data['players'];
$original_id = $data['hashtags']['original_id'];
$name = $data['hashtags']['name'];
$color = $data['hashtags']['color'];
$order = $data['hashtags']['order'];
$description = $data['hashtags']['description'];


//insert into mysql table
  $sql1 = "INSERT INTO tags(short_desc, original_id, name,color, modified,abstract, category, tag, order_in_category)
VALUES('$short_desc','$original_id','$name', '$tagscolor','$tagsmodified','$abstract','$category','$tag','$order_in_category')";
$sql2 = "INSERT INTO topics(id,user_id,postedby,publishDate,photoUrl,description,title,addedOn,topic_rating,topic_rate_num,tags,url,photosUrl,youtubeUrl,modified,mainTag,color,hashtag,captionUrl,players)
VALUES('$id','$user_id','$postedby','$publishDate','$photoUrl','$description','$title','$addedOn','$topic_rating','$topic_rate_num','$tags','$url','$photosUrl','$youtubeUrl','$topicsmodified','$mainTag','$topicscolor','$hashtag','$captionUrl','$players')"; 
$sql3 = "INSERT INTO hashtags(players,original_id,name,color,order,description)
VALUES('$players','$original_id','$name','$color','$order','$description')";
if(!mysqli_query($conn,$sql1))
{
    die('Error :Problem adding into DB ' . mysqli_error($conn));
}
if(!mysqli_query($conn,$sql2))
{
    die('Error :Problem adding into DB ' . mysqli_error($conn));
}
if(!mysqli_query($conn,$sql3))
{
    die('Error :Problem adding into DB ' . mysqli_error($conn));
}

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Json reader</title>
    <meta content="text/html;charset=utf-8" http-equiv="Content-Type">
    <meta content="utf-8" http-equiv="encoding">
</head>
<body>
</body>
</html>

康恩.php

    <?php
$host="localhost";
$user="root";
$pass="";
$db="soccer";
$conn=mysqli_connect($host,$user,$pass,$db) or die("No connection    exist".mysqli_error($conn));

?>

epl.json

{
"short_desc": [],
"tags": [
    {
        "original_id": "395",
        "name": "Social",
        "color": "#55acee",
        "modified": "1441458636",
        "abstract": "",
        "category": "TYPE",
        "tag": "TYPE_SOCIAL",
        "order_in_category": "34"
    },
    {
        "original_id": "3",
        "name": "Football",
        "color": "#008348",
        "modified": "1441458636",
        "abstract": "",
        "category": "SPORT",
        "tag": "TOPIC_SOCCER",
        "order_in_category": "4"
    },
    {
        "original_id": "394",
        "name": "Article",
        "color": "#f94500",
        "modified": "1441458636",
        "abstract": "",
        "category": "TYPE",
        "tag": "TYPE_ARTICLE",
        "order_in_category": "33"
    },
    {
        "original_id": "2",
        "name": "EPL",
        "color": "#005CAB",
        "modified": "1441458636",
        "abstract": "",
        "category": "LEAGUE",
        "tag": "LEAGUE_EPL",
        "order_in_category": "15"
    },
    {
        "original_id": "31",
        "name": "Manchester United",
        "color": "#DA020E",
        "modified": "1441458636",
        "abstract": "",
        "category": "TEAM",
        "tag": "TEAM_MANCHESTER_UNITED",
        "order_in_category": "2"
    },
    {
        "original_id": "619",
        "name": "Cristiano Ronaldo",
        "color": "#3239c7",
        "modified": "1441458636",
        "abstract": "",
        "category": "THEME",
        "tag": "THEME_CRISTIANO_RONALDO",
        "order_in_category": "8"
    },
    {
        "original_id": "546",
        "name": "Breaking News",
        "color": "#3d9ab4",
        "modified": "1441458636",
        "abstract": "",
        "category": "THEME",
        "tag": "THEME_BREAKING_NEWS",
        "order_in_category": "1"
    },
    {
        "original_id": "34",
        "name": "Real Madrid",
        "color": "#173980",
        "modified": "1441458636",
        "abstract": "",
        "category": "TEAM",
        "tag": "TEAM_REAL_MADRID",
        "order_in_category": "6"
    },
    {
        "original_id": "566",
        "name": "International Football",
        "color": "#e52e13",
        "modified": "1441458636",
        "abstract": "",
        "category": "THEME",
        "tag": "THEME_INTERNATIONAL_FOOTBALL",
        "order_in_category": "23"
    },
    {
        "original_id": "612",
        "name": "Argentina",
        "color": "#efae47",
        "modified": "1441458636",
        "abstract": "",
        "category": "THEME",
        "tag": "THEME_ARGENTINA",
        "order_in_category": "1"
    },
    {
        "original_id": "687",
        "name": "Sergio Aguero",
        "color": null,
        "modified": "1441886413",
        "abstract": "",
        "category": "THEME",
        "tag": "THEME_SERGIO_AGUERO",
        "order_in_category": "16"
    },
    {
        "original_id": "444",
        "name": "Barcelona",
        "color": "#A2214B",
        "modified": "1441458636",
        "abstract": "",
        "category": "TEAM",
        "tag": "TEAM_FC_BARCELONA",
        "order_in_category": "8"
    },
    {
        "original_id": "547",
        "name": "Transfer Window",
        "color": "#0e9796",
        "modified": "1441458636",
        "abstract": "",
        "category": "THEME",
        "tag": "THEME_TRANSFER_WINDOW",
        "order_in_category": "4"
    },
    {
        "original_id": "548",
        "name": "Artur Davtyan",
        "color": "#1c87a1",
        "modified": "1441458636",
        "abstract": "",
        "category": "THEME",
        "tag": "THEME_ARTUR_DAVTYAN",
        "order_in_category": "5"
    },
    {
        "original_id": "577",
        "name": "Serge Deble",
        "color": "#cd8bbe",
        "modified": "1441458636",
        "abstract": "",
        "category": "THEME",
        "tag": "THEME_SERGE_DEBLE",
        "order_in_category": "10"
    },
    {
        "original_id": "549",
        "name": "Marin Leovac",
        "color": "#790470",
        "modified": "1441458636",
        "abstract": "",
        "category": "THEME",
        "tag": "THEME_MARIN_LEOVAC",
        "order_in_category": "6"
    },
    {
        "original_id": "711",
        "name": "Philippe Coutinho",
        "color": null,
        "modified": "1442221206",
        "abstract": "",
        "category": "THEME",
        "tag": "THEME_PHILIPPE_COUTINHO",
        "order_in_category": "24"
    },
    {
        "original_id": "30",
        "name": "Arsenal",
        "color": "#EF0107",
        "modified": "1441458636",
        "abstract": "",
        "category": "TEAM",
        "tag": "TEAM_ARSENAL",
        "order_in_category": "1"
    }
],
"topics": [
    {
        "id": "86820",
        "user_id": "8003",
        "postedby": "Admin Administrator",
        "publishDate": "2015-10-08T08:30:38+3:00",
        "photoUrl": "http://img.bleacherreport.net/img/images/photos/003/531/421/hi-res-afcc062b0248a8903713f20fdaf4f6a2_crop_north.jpg?w=650&h=440&q=75",
        "description": "",
        "title": "Cristiano Ronaldo Pays Tribute to 'Father Figure' Sir Alex Ferguson",
        "addedOn": "2015-10-09T09:59:01+3:00",
        "topic_rating": "0",
        "topic_rate_num": "0",
        "tags": [
            "TOPIC_SOCCER",
            "TYPE_ARTICLE",
            "LEAGUE_EPL",
            "TEAM_MANCHESTER_UNITED",
            "THEME_CRISTIANO_RONALDO",
            "THEME_BREAKING_NEWS",
            "TEAM_REAL_MADRID"
        ],
        "url": "http://bleacherreport.com/articles/2577078-cristiano-ronaldo-pays-tribute-to-father-figure-sir-alex-ferguson",
        "photosUrl": "",
        "youtubeUrl": "",
        "modified": "1444373946",
        "mainTag": "TYPE_ARTICLE",
        "color": "#f94500",
        "hashtag": "Article",
        "captionsUrl": "",
        "players": []
    },
    {
        "id": "85087",
        "user_id": "8003",
        "postedby": "Admin Administrator",
        "publishDate": "2015-10-07T13:28:38+3:00",
        "photoUrl": "http://img.bleacherreport.net/img/images/photos/003/531/080/hi-res-cb95763e1709d4d1e71198b525af84ff_crop_north.jpg?w=650&h=440&q=75",
        "description": "",
        "title": "Leo Messi Tells Sergio Aguero He Has to Wear His No. 10 Shirt While He's Out",
        "addedOn": "2015-10-09T09:59:01+3:00",
        "topic_rating": "0",
        "topic_rate_num": "0",
        "tags": [
            "TOPIC_SOCCER",
            "TYPE_ARTICLE",
            "LEAGUE_EPL",
            "THEME_INTERNATIONAL_FOOTBALL",
            "THEME_BREAKING_NEWS",
            "THEME_ARGENTINA",
            "THEME_SERGIO_AGUERO",
            "TEAM_FC_BARCELONA"
        ],
        "url": "http://bleacherreport.com/articles/2576736-leo-messi-tells-sergio-aguero-he-has-to-wear-his-no10-shirt-while-hes-out",
        "photosUrl": "",
        "youtubeUrl": "",
        "modified": "1444373945",
        "mainTag": "TYPE_ARTICLE",
        "color": "#f94500",
        "hashtag": "Article",
        "captionsUrl": "",
        "players": []
    },
    {
        "id": "84876",
        "user_id": "8003",
        "postedby": "Admin Administrator",
        "publishDate": "2015-10-07T10:56:43+3:00",
        "photoUrl": "http://img.bleacherreport.net/img/images/photos/003/531/018/hi-res-718c63db2f32226a5917bccd633c9e7c_crop_north.jpg?w=650&h=440&q=75",
        "description": "",
        "title": "Arsenal Transfer News: Sergi Samper Open to Gunners Move, Latest Rumours",
        "addedOn": "2015-10-09T09:59:01+3:00",
        "topic_rating": "0",
        "topic_rate_num": "0",
        "tags": [
            "TOPIC_SOCCER",
            "TYPE_ARTICLE",
            "LEAGUE_EPL",
            "THEME_BREAKING_NEWS",
            "THEME_TRANSFER_WINDOW",
            "THEME_ARTUR_DAVTYAN",
            "THEME_SERGE_DEBLE",
            "THEME_MARIN_LEOVAC",
            "TEAM_FC_BARCELONA"
        ],
        "url": "http://bleacherreport.com/articles/2576694-arsenal-transfer-news-sergi-samper-open-to-gunners-move-latest-rumours",
        "photosUrl": "",
        "youtubeUrl": "",
        "modified": "1444373945",
        "mainTag": "TYPE_ARTICLE",
        "color": "#f94500",
        "hashtag": "Article",
        "captionsUrl": "",
        "players": []
    },
    {
        "id": "81954",
        "user_id": "8003",
        "postedby": "Admin Administrator",
        "publishDate": "2015-10-06T09:37:39+3:00",
        "photoUrl": "http://img.bleacherreport.net/img/images/photos/003/530/559/hi-res-5d09eb502460cc7ce08b39a0194ef20e_crop_north.jpg?w=650&h=440&q=75",
        "description": "",
        "title": "Liverpool Transfer News: Philippe Coutinho Reportedly Open to Barcelona Move",
        "addedOn": "2015-10-09T09:59:01+3:00",
        "topic_rating": "0",
        "topic_rate_num": "0",
        "tags": [
            "TOPIC_SOCCER",
            "TYPE_ARTICLE",
            "LEAGUE_EPL",
            "THEME_BREAKING_NEWS",
            "THEME_TRANSFER_WINDOW",
            "THEME_PHILIPPE_COUTINHO",
            "THEME_ARTUR_DAVTYAN",
            "THEME_SERGE_DEBLE",
            "THEME_MARIN_LEOVAC",
            "TEAM_FC_BARCELONA"
        ],
        "url": "http://bleacherreport.com/articles/2576278-liverpool-transfer-news-philippe-coutinho-reportedly-open-to-barcelona-move",
        "photosUrl": "",
        "youtubeUrl": "",
        "modified": "1444373945",
        "mainTag": "TYPE_ARTICLE",
        "color": "#f94500",
        "hashtag": "Article",
        "captionsUrl": "",
        "players": []
    },
    {
        "id": "81955",
        "user_id": "8003",
        "postedby": "Admin Administrator",
        "publishDate": "2015-10-06T09:23:13+3:00",
        "photoUrl": "http://img.bleacherreport.net/img/images/photos/003/530/558/hi-res-d3cda8934b889c7b7a7e16e31e36887d_crop_north.jpg?w=650&h=440&q=75",
        "description": "",
        "title": "Manchester United Transfer News: Neymar Reportedly Stalls on Barcelona Contract",
        "addedOn": "2015-10-09T09:59:01+3:00",
        "topic_rating": "0",
        "topic_rate_num": "0",
        "tags": [
            "TOPIC_SOCCER",
            "TYPE_ARTICLE",
            "LEAGUE_EPL",
            "THEME_BREAKING_NEWS",
            "THEME_TRANSFER_WINDOW",
            "THEME_ARTUR_DAVTYAN",
            "THEME_SERGE_DEBLE",
            "THEME_MARIN_LEOVAC",
            "TEAM_FC_BARCELONA"
        ],
        "url": "http://bleacherreport.com/articles/2576275-manchester-united-transfer-news-neymar-reportedly-stalls-on-barcelona-contract",
        "photosUrl": "",
        "youtubeUrl": "",
        "modified": "1444373945",
        "mainTag": "TYPE_ARTICLE",
        "color": "#f94500",
        "hashtag": "Article",
        "captionsUrl": "",
        "players": []
    },
    {
        "id": "80305",
        "user_id": "8003",
        "postedby": "Admin Administrator",
        "publishDate": "2015-10-05T17:58:34+3:00",
        "photoUrl": "http://img.bleacherreport.net/img/images/photos/003/530/304/hi-res-8fc4faa33377f2bdf325d6c8c5afdf3e_crop_north.jpg?w=650&h=440&q=75",
        "description": "",
        "title": "Manchester United Transfer News: Latest Shkodran Mustafi, James Wilson Rumours",
        "addedOn": "2015-10-09T09:59:01+3:00",
        "topic_rating": "0",
        "topic_rate_num": "0",
        "tags": [
            "TOPIC_SOCCER",
            "TYPE_ARTICLE",
            "LEAGUE_EPL",
            "TEAM_MANCHESTER_UNITED",
            "THEME_BREAKING_NEWS",
            "THEME_TRANSFER_WINDOW",
            "THEME_ARTUR_DAVTYAN",
            "THEME_SERGE_DEBLE",
            "THEME_MARIN_LEOVAC",
            "TEAM_REAL_MADRID"
        ],
        "url": "http://bleacherreport.com/articles/2575994-manchester-united-transfer-news-latest-shkodran-mustafi-james-wilson-rumours",
        "photosUrl": "",
        "youtubeUrl": "",
        "modified": "1444373947",
        "mainTag": "TYPE_ARTICLE",
        "color": "#f94500",
        "hashtag": "Article",
        "captionsUrl": "",
        "players": []
    },
    {
        "id": "79129",
        "user_id": "8003",
        "postedby": "Admin Administrator",
        "publishDate": "2015-10-04T22:12:42+3:00",
        "photoUrl": "http://img.bleacherreport.net/img/images/photos/003/529/933/hi-res-a3326d599e5ed1e926201155635f2603_crop_north.jpg?w=650&h=440&q=75",
        "description": "",
        "title": "Arsenal Transfer News: Gunners Racing to Beat Barca as Nolito Rumours Emerge",
        "addedOn": "2015-10-09T09:59:01+3:00",
        "topic_rating": "0",
        "topic_rate_num": "0",
        "tags": [
            "TOPIC_SOCCER",
            "TYPE_ARTICLE",
            "LEAGUE_EPL",
            "TEAM_ARSENAL",
            "THEME_BREAKING_NEWS",
            "THEME_TRANSFER_WINDOW",
            "THEME_ARTUR_DAVTYAN",
            "THEME_SERGE_DEBLE",
            "THEME_MARIN_LEOVAC",
            "TEAM_FC_BARCELONA"
        ],
        "url": "http://bleacherreport.com/articles/2575668-arsenal-transfer-news-gunners-racing-to-beat-barca-as-nolito-rumours-emerge",
        "photosUrl": "",
        "youtubeUrl": "",
        "modified": "1444373946",
        "mainTag": "TYPE_ARTICLE",
        "color": "#f94500",
        "hashtag": "Article",
        "captionsUrl": "",
        "players": []
    },
    {
        "id": "77815",
        "user_id": "8003",
        "postedby": "Admin Administrator",
        "publishDate": "2015-10-04T12:00:50+3:00",
        "photoUrl": "http://img.bleacherreport.net/img/images/photos/003/529/695/hi-res-e26e40db50d6f7b354782f4eecb4145c_crop_north.jpg?w=650&h=440&q=75",
        "description": "",
        "title": "Liverpool Transfer News: Philippe Coutinho Reacts to Neymar's Barcelona Comments",
        "addedOn": "2015-10-09T09:59:01+3:00",
        "topic_rating": "0",
        "topic_rate_num": "0",
        "tags": [
            "TOPIC_SOCCER",
            "TYPE_ARTICLE",
            "LEAGUE_EPL",
            "THEME_BREAKING_NEWS",
            "THEME_TRANSFER_WINDOW",
            "THEME_ARTUR_DAVTYAN",
            "THEME_SERGE_DEBLE",
            "THEME_MARIN_LEOVAC",
            "TEAM_FC_BARCELONA"
        ],
        "url": "http://bleacherreport.com/articles/2575431-liverpool-transfer-news-philippe-coutinho-reacts-to-neymars-barcelona-comments",
        "photosUrl": "",
        "youtubeUrl": "",
        "modified": "1444373946",
        "mainTag": "TYPE_ARTICLE",
        "color": "#f94500",
        "hashtag": "Article",
        "captionsUrl": "",
        "players": []
    },
    {
        "id": "73005",
        "user_id": "8003",
        "postedby": "Admin Administrator",
        "publishDate": "2015-10-02T06:56:27+3:00",
        "photoUrl": "http://img.bleacherreport.net/img/images/photos/003/528/842/hi-res-afb33e87d9e9eb8e843b12cb197997ec_crop_north.jpg?w=650&h=440&q=75",
        "description": "",
        "title": "Manchester United Transfer News: Toni Kroos Targeted Amid Real Madrid Rumours",
        "addedOn": "2015-10-09T09:59:01+3:00",
        "topic_rating": "0",
        "topic_rate_num": "0",
        "tags": [
            "TOPIC_SOCCER",
            "TYPE_ARTICLE",
            "LEAGUE_EPL",
            "THEME_BREAKING_NEWS",
            "THEME_TRANSFER_WINDOW",
            "THEME_ARTUR_DAVTYAN",
            "THEME_SERGE_DEBLE",
            "THEME_MARIN_LEOVAC",
            "TEAM_REAL_MADRID"
        ],
        "url": "http://bleacherreport.com/articles/2574738-manchester-united-transfer-news-toni-kroos-targeted-amid-real-madrid-rumours",
        "photosUrl": "",
        "youtubeUrl": "",
        "modified": "1444373947",
        "mainTag": "TYPE_ARTICLE",
        "color": "#f94500",
        "hashtag": "Article",
        "captionsUrl": "",
        "players": []
    }
],
}

注意：未定义的变量：标签颜色 C：''xampp''htdocs''jsonreader''index.php 在第 64 行

在 SQL 查询中，您有一个变量$tagscolor，但您没有在 sql 语句之前的代码中设置此变量。

注意：未定义的变量：标签修改于 C：''xampp''htdocs''jsonreader''index.php 在第 64 行

在 SQL 查询中，您有一个变量$tagsmodified，但您没有在 sql 语句之前的代码中设置此变量。

注意：数组到字符串的转换 C：''xampp''htdocs''jsonreader''index.php 在第 66 行 错误 :P roblem add 进入数据库 您的 SQL 语法有错误;检查手册 对应于您的MySQL服务器版本，以便使用正确的语法 靠近"顺序，描述"( 第 1 行的值("e"、">

"(">

变量$players是一个数组，请参阅您的 json 代码( "players": [] (，如果需要或json_encode，您可以字符串化$players

$players = json_encode($players);

顺便说一句：有什么用：

$players = $data['topics']['players']; 
$players=$data['players'];

您覆盖$players .. 并且$data['players']不存在。在提交有关SO的问题之前，请仔细检查您自己的代码，它充满了错误。

除了已突出显示的问题外，您的 JSON 也无效。例如，将数据粘贴到 http://jsonlint.com/中并检查它。删除最后一个逗号应该可以解决这个问题。

一旦您的 JSON 有效，您应该会发现您的 $data 变量不再为 null：

var_dump($data);

您需要修改代码以循环访问它以处理所有内容(例如，有 18 个"标签"数组(。

相关文章：
最新更新
www.56WenDa.com 2012-2023 | php问答网 | php学习