dongyanzhui0524 2013-10-20 05:05
浏览 501
已采纳

使用PHP将JSON解析为MySQL

I have some JSON files I want to record on MySQL, I read through some of the help topics in SO but I'm having a problem. 1 2 3

My JSON looks like this;

     {
          "hoursPlayed": 21775,
          "communityVisibilityState": 3,
          "timeCreated": 1261107470,
          "props": [
       {
          "level": 53,
          "quality": 6,
          "origin": 0,
       },
       {
          "level": 10,
          "quality": 6,
          "origin": 0,
       }
    ]
  }

I already created the table and its fields, but I can't figure out what to do when it comes to the "props" array in the last entry of the JSON. It is an array in itself and it should have its own MySQL table. Without the "props" part I can read, use json_decode() and loop through the array to store every other value but "props" part gets me puzzled.

My table should have fields, right?

  • hoursPlayed * INT
  • communityVisibilityState * INT
  • timeCreated * INT
  • props * ??

How do I store this in a table in MySQL?

Normally;

mysqli_query($con,"INSERT INTO my_jsonDB (hoursPlayed, communityVisibilityState, timeCreated)
VALUES (21775, 3,1261107470)");

What about in my case?

  • 写回答

3条回答 默认 最新

  • dousikuai5417 2013-10-20 05:26
    关注

    Its time to take a look into the foreign key constraints in MySQL. Basically your Table structure will look like the below:

    • Table#1 Users - UserID | hoursPlayed | communityVisibilityState | timeCreated
    • Table#2 UserProperty - userKey | level | quality | origin.

    Since you are using MySQL, you can look into autoincrement for primary key UserID. You will use this primary key, to insert as a foreign key in the UserProperty table.

    The overall process would look like this:

    • Insert a row in the first table. Get the primary key of the insertion (ie. the first column).
    • Iterate over all the "props" fields:
      • Next, use the key you got in the above step, and use it while inserting the current "prop" in the second table.
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 求chat4.0解答一道线性规划题,用lingo编程运行,第一问要求写出数学模型和lingo语言编程模型,第二问第三问解答就行,我的ddl要到了谁来求了
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥15 maple软件,用solve求反函数出现rootof,怎么办?
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果