JSON file parsing need help PHP to SQL

Question

h3rb 0 Newbie Poster

12 Years Ago

hi all, i have a problem with multiline json file that i wish to insert into an sql db. my problem arose as i went from one data set to two in the JSON file. the one data set was sucked from the json file to the sql db via PHP no problems at all, but at the moment i lack the programming skills to complete the nested loop as i am new to programming with JSON and jsonlines via PHP and the "foreach" statement. I red up on it and checked the forum here for similar issues. So here is the file the data sets are separated by ]}, and a newline:

[{"utc": ["22. September 2011, 01\n Uhr"], "name": [" Vorarlberg"], "temp": ["11.6", "11.6", "10.2", "11.3", "10.5", "9.7", "11.1", "9.4", "7.3", "9.2", "6.9", "9.1", "8.7", "6.9"], "humidity": ["97 %", "98 %", "96 %", "91 %", "95 %", "100 %", "87 %", "77 %", "99 %", "90 %", "87 %", "79 %", "90 %", "94 %"], "pressure": ["1021.5 ", " ", "1020.2 ", " ", "1021.1 ", " ", "1021.4 ", " ", "1021.4 ", " ", "1021.7 ", " ", "k.A. ", "1021.7 ", " ", "1022.6 ", " ", "k.A. ", "k.A. ", "k.A. ", "k.A. ", "k.A. "], "location": ["Bregenz", "Rohrspitz", "Dornbirn", "Fraxern", "Feldkirch", "Bludenz", "Sulzberg", "Alberschwende", "Schoppernau", "Schroecken", "Warth", "Langen am Arlberg", "Brand", "Gaschurn"], "height": ["440 ", "395 ", "407 ", "810 ", "436 ", "561 ", "1014 ", "715 ", "837 ", "1244 ", "1470 ", "1218 ", "1032 ", "973 "], "wind": ["Sued, 8 ", "Suedost, 5 ", "Suedost, 1 ", "Nordost, 4 ", "Suedost, 1 ", "Nord, 1 ", "Sued, 7 ", "Nord, 1 ", "Suedost, 3 ", "Ost, 3 ", "Ost, 2 ", "Nordost, 8 ", "Suedwest, 4 ", "Ost, 1 "]},
{"utc": ["22. September 2011, 01\n Uhr"], "name": [" Tirol"], "temp": ["8.7", "6.2", "5.9", "7.9", "5.0", "3.6", "7.0", "3.8", "8.5", "7.7", "7.3", "6.0", "3.3", "3.2", "2.6", "4.6", "4.4", "8.6", "9.1", "8.5", "6.6", "6.3", "4.9", "4.7", "8.0", "5.9", "9.1", "8.1", "9.7", "9.6", "7.2", "7.7", "7.3", "11.5", "7.4", "3.6", "9.0", "8.0"], "humidity": ["57 %", "90 %", "98 %", "99 %", "98 %", "90 %", "44 %", "97 %", "95 %", "99 %", "95 %", "95 %", "96 %", "43 %", "24 %", "69 %", "97 %", "97 %", "96 %", "88 %", "68 %", "92 %", "95 %", "96 %", "94 %", "96 %", "97 %", "92 %", "94 %", "74 %", "93 %", "89 %", "96 %", "88 %", "97 %", "97 %", "83 %", "88 %"], "pressure": ["k.A. ", "k.A. ", "k.A. ", "1022.4 ", " ", "k.A. ", "k.A. ", "k.A. ", "k.A. ", "1023.4 ", " ", "1023.9 ", " ", "1024.6 ", " ", "k.A. ", "k.A. ", "k.A. ", "k.A. ", "k.A. ", "k.A. ", "1023.1 ", " ", "1023.1 ", " ", "k.A. ", "k.A. ", "k.A. ", "k.A. ", "k.A. ", "1022.3 ", " ", "k.A. ", "1024.0 ", " ", "k.A. ", "1022.0 ", " ", "k.A. ", "1023.4 ", " ", "k.A. ", "1022.7 ", " ", "1021.2 ", " ", "k.A. ", "k.A. ", "k.A. ", "k.A. "], "location": ["Galzig", "St. Anton am Arlberg", "Holzgau", "Reutte", "Ehrwald", "Galtuer", "Ischgl Idalpe", "Nauders", "Landeck", "Imst", "Haiming", "Umhausen", "St. Leonhard Pitztal", "Pitztaler Gletscher", "Brunnenkogel", "Obergurgl", "Seefeld", "Innsbruck Flugh.", "Innsbruck Univ.", "Rinn", "Patscherkofel", "Neustift Milders", "Steinach am Brenner", "Brenner", "Mayrhofen", "Achenkirch", "Jenbach", "Alpbach", "Kufstein", "Hahnenkamm", "Kitzbuehel", "Hochfilzen", "Koessen", "Lienz", "Sillian", "St. Jakob   Defereggen", "Virgen", "Kals"], "height": ["2090 ", "1300 ", "1110 ", "843 ", "980 ", "1577 ", "2319 ", "1341 ", "809 ", "773 ", "671 ", "1029 ", "1452 ", "2843 ", "3440 ", "1939 ", "1184 ", "585 ", "609 ", "924 ", "2252 ", "1004 ", "1032 ", "1369 ", "633 ", "908 ", "539 ", "927 ", "492 ", "1763 ", "748 ", "960 ", "589 ", "668 ", "1081 ", "1383 ", "1193 ", "1351 "], "wind": ["Nordwest, 1 ", "Suedwest, 4 ", "West, 3 ", "Sued, 3 ", "Nordwest, 1 ", "Sued, 1 ", "Ost, 7 ", "Suedost, 4 ", "West, 7 ", "Windstille", "West, 3 ", "Sued, 3 ", "Windstille", "West, 11 ", "Suedwest, 9 ", "Sued, 4 ", "Suedwest, 4 ", "West, 5 ", "Suedwest, 3 ", "Sued, 4 ", "Nordwest, 19 ", "Suedwest, 5 ", "Suedost, 6 ", "Nordost, 4 ", "West, 1 ", "Suedwest, 2 ", "Windstille", "Ost, 4 ", "Suedwest, 3 ", "West, 4 ", "Ost, 1 ", "Ost, 10 ", "Sued, 1 ", "Nordwest, 3 ", "West, 3 ", "West, 3 ", "Nordwest, 4 ", "West, 1 "]}]

and here is the code that worked up til the second data set:

$string = file_get_contents($handle);
		$arr = json_decode($string, true);
		#echo var_dump($arr);
		
		
		//array instances specific to json items
		
		$id = $arr["name"];
		$id2 = $arr["location"];
		$id3 = $arr["height"];
		$id4 = $arr["utc"];
		$id5 = $arr["temp"];
		$id6 = $arr["humidity"];
		$id7 = $arr["wind"];
		$id8 = $arr["pressure"];
		
		$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++){
			$name = $id[0];
			$location = $id2[$i];
			$height = $id3[$i];
			$utc = $id4[0];
			$temp = $id5[$i];
			$humidity = $id6[$i];
			$wind = $id7[$i];
			$pressure = $id8[$i];
			
			mysql_query("INSERT INTO wetteroe (name, location, height, utc, temp, humidity, wind, pressure, DATEtime) VALUES('$name', '$location', '$height', '$utc', '$temp', '$humidity', '$wind', '$pressure',current_timestamp())") or die (mysql_error());
	
		}

ANY help on this would be appreciated!

Thanks,

Herb

php

2 Contributors
7 Replies
235 Views
1 Day Discussion Span
Latest Post 12 Years Ago Latest Post by cereal

All 7 Replies

cereal 1,524 Nearly a Senior Poster

12 Years Ago

Maybe there's something wrong, to me that JSON doesn't seems to have two blocks. You could do something like this:

{
"first block":
    {
    "name":"value 1",
    "location":"value 2"
    },
"second block":
    {
    "name":"value 1",
    "location":"value 2"
    }
}

And in the PHP part:

<?php
$f = file_get_contents('j.json');
$j = json_decode($f,true);
echo $j['first block']['name'] . "\n";
echo $j['first block']['location'] . "\n";
echo $j['second block']['name'] . "\n";
echo $j['second block']['location'] . "\n";
?>

Hope it helps, bye :)

cereal 1,524 Nearly a Senior Poster

12 Years Ago

If you remove print_r() from line 64 and write $result, you can do this:

$result = array_merge_recursive($m2,$a4);
$q = '';
foreach($result as $key => $value)
{
	$name = trim($name);
	$location = trim($key);
	$utc = str_replace("\n",'',$utc);
	$coma = (empty($q)) ? '':',';
	$q .= "$coma values('$name', '$location', '$value[0]', '$utc', '$value[1]', '$value[2]', '$value[3]', current_timestamp())";
}

echo "INSERT INTO wetteroe (name, location, height, utc, temp, humidity, wind, DATEtime) $q"; #

This will give you two big inserts, basing on the names: Vorarlberg and Tirol.
I would use three tables, not only one: the first for the names, another for the locations and one for height, temp, humidity, wind and pressure. But that's just my opinion. Bye :)

Just a tip on previous code, online 4 and 5 I wrote:

$s = '1';
for( $i=0; $i<=$s; $i++){

The correct version is:

$s = count($arr);
for( $i=0; $i<$s; $i++){

Without equal ^__^'

Edited 12 Years Ago by cereal because: added correction

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

h3rb 0 Newbie Poster · Answer 1 · 2011-09-22T18:30:34+00:00

Maybe there's something wrong, to me that JSON doesn't seems to have two blocks. You could do something like this:

{
"first block":
    {
    "name":"value 1",
    "location":"value 2"
    },
"second block":
    {
    "name":"value 1",
    "location":"value 2"
    }
}

And in the PHP part:

<?php
$f = file_get_contents('j.json');
$j = json_decode($f,true);
echo $j['first block']['name'] . "\n";
echo $j['first block']['location'] . "\n";
echo $j['second block']['name'] . "\n";
echo $j['second block']['location'] . "\n";
?>

Hope it helps, bye :)

Thanks for the input, u are right it isn't normal JSON i think that's where my trouble begins it is a derivative of json called jsonlines... i still can't get my mind to think of a test to tell the string "array" i'm feeding the jsonlines into to view the "]}," delimiter as a new element in the string array "ARGGGH" I'd be better off using XML nodes :-/

cereal 1,524 Nearly a Senior Poster Featured Poster · Answer 2 · 2011-09-22T20:49:18+00:00

Maybe I got some results, but I need to understand if you need to combine pressure values with locations, because the numbers are not the same and I getting some errors, here's what I've done:

<?php
$f = file_get_contents('js2.json');
$arr = json_decode($f,true);
$s = '1';
for( $i=0; $i<=$s; $i++){
	$name = $arr[$i]["name"][0];
	$location = $arr[$i]["location"];
	$height = $arr[$i]["height"];
	$utc = $arr[$i]["utc"][0];
	$temp = $arr[$i]["temp"];
	$humidity = $arr[$i]["humidity"];
	$wind = $arr[$i]["wind"];
	$pressure = $arr[$i]["pressure"];

	$arr1 = array();
	foreach($location as $key => $value)
	{
		$arr1[]= $value;
	}

	$arr2 = array();
	foreach($height as $key => $value)
	{
		$arr2[]= $value;
	}

	$arr3 = array();
	foreach($temp as $key => $value)
	{
		$arr3[]= $value;
	}

	$arr4 = array();
	foreach($humidity as $key => $value)
	{
		$arr4[]= $value;
	}

	$arr5 = array();
	foreach($wind as $key => $value)
	{
		$arr5[]= $value;
	}

	$arr6 = array();
	foreach($pressure as $key => $value)
	{
		$arr6[]= $value;
	}

	echo 'Name: '. trim($name) . "\n";
	echo 'UTC: ' . trim($utc) . "\n";
	echo "\n";
	
	$a1 = array_combine($arr1,$arr2);
	$a2 = array_combine($arr1,$arr3);
	$a3 = array_combine($arr1,$arr4);
	$a4 = array_combine($arr1,$arr5);
	$a5 = array_combine($arr1,$arr6);

	echo 'Merge: ';
	$m1 = array_merge_recursive($a1,$a2);
	$m2 = array_merge_recursive($m1,$a3);
	print_r(array_merge_recursive($m2,$a4)); # hide this if pressure array is fixed

	# uncomment below if pressure array is fixed
/*
	$m3 = array_merge_recursive($m1,$a4);
	print_r(array_merge_recursive($m3,$a5));

*/
}
?>

If you don't fix pressure array and you want to join it with location array you will get an error like this one:

array_combine(): Both parameters should have an equal number of elements ... on line 59

Probably there is a much elegant way to achieve the same result, but right now, I can't get it, hope someone else will help you, good luck!

p.s. From this script you will get an array for each location with temperature, height, humidity, wind and pressure. From that point it's easy to do the insert queries.

h3rb 0 Newbie Poster · Answer 3 · 2011-09-23T14:42:32+00:00

Maybe I got some results, but I need to understand if you need to combine pressure values with locations, because the numbers are not the same and I getting some errors, here's what I've done:

<?php
$f = file_get_contents('js2.json');
$arr = json_decode($f,true);
$s = '1';
for( $i=0; $i<=$s; $i++){
	$name = $arr[$i]["name"][0];
	$location = $arr[$i]["location"];
	$height = $arr[$i]["height"];
	$utc = $arr[$i]["utc"][0];
	$temp = $arr[$i]["temp"];
	$humidity = $arr[$i]["humidity"];
	$wind = $arr[$i]["wind"];
	$pressure = $arr[$i]["pressure"];

	$arr1 = array();
	foreach($location as $key => $value)
	{
		$arr1[]= $value;
	}

	$arr2 = array();
	foreach($height as $key => $value)
	{
		$arr2[]= $value;
	}

	$arr3 = array();
	foreach($temp as $key => $value)
	{
		$arr3[]= $value;
	}

	$arr4 = array();
	foreach($humidity as $key => $value)
	{
		$arr4[]= $value;
	}

	$arr5 = array();
	foreach($wind as $key => $value)
	{
		$arr5[]= $value;
	}

	$arr6 = array();
	foreach($pressure as $key => $value)
	{
		$arr6[]= $value;
	}

	echo 'Name: '. trim($name) . "\n";
	echo 'UTC: ' . trim($utc) . "\n";
	echo "\n";
	
	$a1 = array_combine($arr1,$arr2);
	$a2 = array_combine($arr1,$arr3);
	$a3 = array_combine($arr1,$arr4);
	$a4 = array_combine($arr1,$arr5);
	$a5 = array_combine($arr1,$arr6);

	echo 'Merge: ';
	$m1 = array_merge_recursive($a1,$a2);
	$m2 = array_merge_recursive($m1,$a3);
	print_r(array_merge_recursive($m2,$a4)); # hide this if pressure array is fixed

	# uncomment below if pressure array is fixed
/*
	$m3 = array_merge_recursive($m1,$a4);
	print_r(array_merge_recursive($m3,$a5));

*/
}
?>

If you don't fix pressure array and you want to join it with location array you will get an error like this one:

array_combine(): Both parameters should have an equal number of elements ... on line 59

Probably there is a much elegant way to achieve the same result, but right now, I can't get it, hope someone else will help you, good luck!

p.s. From this script you will get an array for each location with temperature, height, humidity, wind and pressure. From that point it's easy to do the insert queries.

I think this is what i|m looking for! Perfect without the pressure array, i decided to leave it out for now until i know how to repair it, probably by filling the empty pressure array with if(pressurearray == 0 or == ' ') then insert some value.

Great cereal thanks alot for the help!
all i need now is a for / while loop to insert all the data into my db. :)

h3rb 0 Newbie Poster · Answer 4 · 2011-09-23T23:16:40+00:00

If you remove print_r() from line 64 and write $result, you can do this:
$result = array_merge_recursive($m2,$a4);
$q = '';
foreach($result as $key => $value)
{
	$name = trim($name);
	$location = trim($key);
	$utc = str_replace("\n",'',$utc);
	$coma = (empty($q)) ? '':',';
	$q .= "$coma values('$name', '$location', '$value[0]', '$utc', '$value[1]', '$value[2]', '$value[3]', current_timestamp())";
}

echo "INSERT INTO wetteroe (name, location, height, utc, temp, humidity, wind, DATEtime) $q"; #
This will give you two big inserts, basing on the names: Vorarlberg and Tirol.
I would use three tables, not only one: the first for the names, another for the locations and one for height, temp, humidity, wind and pressure. But that's just my opinion. Bye :)
Just a tip on previous code, online 4 and 5 I wrote:
$s = '1';
for( $i=0; $i<=$s; $i++){
The correct version is:
$s = count($arr);
for( $i=0; $i<$s; $i++){
Without equal ^__^'

thanks again, yeah i figured that last tip out already enumerate the array and quantify $s variable to run as many times as i need :D,
Great stuff thanks for the help

cereal 1,524 Nearly a Senior Poster Featured Poster · Answer 5 · 2011-09-24T00:07:09+00:00

cereal 1,524 Nearly a Senior Poster

12 Years Ago

You're welcome!

JSON file parsing need help PHP to SQL

Recommended Answers Collapse Answers

All 7 Replies

Recommended Answers