Insert JSON into MySQL.

Question

i have .json file [categories.json]

like this

{
    "apple": [
        "fruit",
        "15"
    ],
    "cat": [
        "animal",
        "400"
    ],
    "pumpkin": [
        "vegetables",
        "20"
    ],
    "orange": [
        "fruit",
        "30"
    ]
}

i want to insert json object into mysql using loop php like this

|___id__|___ product__|_____type_____|__price__|
|   1   |     apple   |    fruit     |    15   |
|   2   |     cat     |    animal    |    400  |
|   3   |   pumpkin   |  vegetables  |    20   |
|   4   |    orange   |    fruit     |    30   |

how can i do thank you

Fisherman · Accepted Answer · 2014-05-06 11:55:22Z

2

 $file = 'www.mysite.com/categories.json';
 $data = json_decode(file_get_contents($file), true);

foreach($data as $product => $row){
$sql = "INSERT INTO product ";
  $sql .= "SET product='".mysql_real_escape_string($product)."',type='".mysql_real_escape_string($row[0])."',price=".mysql_real_escape_string($row[1]);
mysql_query($sql);
} // hoping your id field in db is auto_increment

edited May 6, 2014 at 11:55

answered May 6, 2014 at 10:33

Fisherman

6,1311 gold badge33 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Quentin Over a year ago

Danger: You are vulnerable to SQL injection attacks that you need to defend yourself from.

Fisherman Over a year ago

@quentin yes. I just tried to give a simple answer so that he understand better .. May be he will implement with all those check in PDO or mysqli

user3598505 Over a year ago

in this line $file = 'var/www/somefile.json'; my file location is www.mysite.com/categories.json , then i write $file = 'var/www/categories.json'; but not work (not insert) how can i do ?

Fisherman Over a year ago

use www.mysite.com/categories.json as your path to file_get_content that should work

Fisherman Over a year ago

are you using mysqli or mysql ?

Rizerzero · Accepted Answer · 2014-05-06 10:37:22Z

1

just use json_decode to convert your Json file into a an array and then loop and insert as you'd do that with an array

answered May 6, 2014 at 10:37

Rizerzero

1,17612 silver badges13 bronze badges

Collectives™ on Stack Overflow

Insert JSON into MySQL.

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related