from json api to mysql

Question

i need help to parsing json data to mysql. my code its not work, this is my code.

<?php
$server = "localhost";
$username = "root";
$password = "12345";
$database = "json";
mysql_connect($server,$username,$password) or die("Failed");
mysql_select_db($database) or die("Database Failed");

$url = "http://demo.miliarta.com/cityapi/all/?dealerid=TEN000005&user=dealer&passwd=dealer&cityid=316";
$string = file_get_contents($url);
$arr = json_decode($string, true);

//array instances specific to json items
$id = $arr["cityid"];
$id2 = $arr["stateid"];
$id3 = $arr["cityname"];
$id4 = $arr["statename"];
$s=0;
//Enumerating Array index
foreach($arr as $item=> $value){
    $s=count($value); // WIN
}
echo $s;
//suck the array for loop
for($i=0;$i<$s;$i++){
    $cityid= $id[0];
    $stateid = $id2[$i];
    $cityname = $id3[$i];
    $statename = $id4[0];
    mysql_query("INSERT INTO city (cityid, stateid, cityname, statename) VALUES('$cityid', '$stateid', '$cityname', '$statename')") or die (mysql_error());
}
?>

the problem is on line 22. Notice: Undefined index: cityid in C:\xampp\htdocs\json\jsontosql.php on line 22 Notice: Undefined index: stateid in C:\xampp\htdocs\json\jsontosql.php on line 23 Notice: Undefined index: cityname in C:\xampp\htdocs\json\jsontosql.php on line 24 Notice: Undefined index: statename in C:\xampp\htdocs\json\jsontosql.php on line 25 4Table 'json.city' doesn't exist

tq for help.

Answer 1

Fixed it for you. Your looping is wrong, you need to look on $arr first to get each rows of data.

Try this:

<?php
// Your database configs here

$url = "http://demo.miliarta.com/cityapi/all/?dealerid=TEN000005&user=dealer&passwd=dealer&cityid=316";
$string = file_get_contents($url);
$arr = json_decode($string, true);

foreach($arr as $item){
    $cityid = $item['cityid'];
    $stateid = $item['stateid'];
    $cityname = $item['cityname'];
    $statename = $item['statename'];

    mysql_query("INSERT INTO city (cityid, stateid, cityname, statename) VALUES('$cityid', '$stateid', '$cityname', '$statename')") or die (mysql_error());
}