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 · Answer 1 · 2014-05-06T11:55:22.113

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 06 '14 at 11:55

answered May 06 '14 at 10:33

Fisherman

5,949
1
29
35

1

**Danger**: You are **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that you need to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. – Quentin May 06 '14 at 10:34
1

@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 – Fisherman May 06 '14 at 10:40
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 ? – user3598505 May 06 '14 at 10:47
use www.mysite.com/categories.json as your path to file_get_content that should work – Fisherman May 06 '14 at 11:08
are you using mysqli or mysql ? – Fisherman May 06 '14 at 11:53

score 1 · Answer 2 · answered May 06 '14 at 10:37

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 06 '14 at 10:37

Rizerzero

1,120
12
13

Insert JSON into MySQL.

2 Answers2