使用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?

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.

Your props should be in their own table and have a foreign key reference back to my_jsonDB.

For example you should have a table called Props.

The columns would be id, my_jsonDB_id, level, quality, origin.

The first row might be: 1,1, 53,6,0

Then when you insert into the Props table, you just set my_jsonDB_id to the id that the prop data relates to in the my_jsonDB table.

Have a look at this for an example: http://www.w3schools.com/sql/sql_foreignkey.asp

You can also use Text field, for example. And use serialize() function before you will save it in your database.