JSON to MySQL with PHP

[scrivomcdivo]

Hey folks.

I currently have a large JSON file made up of entries similar to the below. The file contains information relating to photographs:-

{
  "SourceFile": "C:/Users/Me/Desktop/Photos/5294030805_9497032cd7_o.jpg",
  "ExifToolVersion": 8.63,
  "FileName": "5294030805_9497032cd7_o.jpg",
  "Directory": "C:/Users/Me/Desktop/Photos",
  "FileSize": "1094 kB",
  "FileModifyDate": "2011:09:09 22:40:39+01:00",
  "FilePermissions": "rw-rw-rw-",
  "FileType": "JPEG",
  "MIMEType": "image/jpeg",
  "JFIFVersion": 1.01,
  "ExifByteOrder": "Big-endian (Motorola, MM)",
  "Make": "Apple",
  "Model": "iPhone 4",
  "Orientation": "Horizontal (normal)",
  "XResolution": 72,
  "YResolution": 72,
  "ResolutionUnit": "inches",
  "Software": "Camera+ 2.0",
  "ModifyDate": "2010:12:26 22:27:09",
  "HostComputer": "iPhone (iPhone OS 4.2.1)",
  "YCbCrPositioning": "Centered",
  "ExposureTime": "1/15",
  "FNumber": 2.8,
  "ExposureProgram": "Program AE",
  "ISO": 1000,
  "ExifVersion": "0221",
  "DateTimeOriginal": "2010:12:26 22:27:09",
  "CreateDate": "2010:12:26 22:27:09",
  "ComponentsConfiguration": "Y, Cb, Cr, -",
  "ShutterSpeedValue": "1/15",
  "ApertureValue": 2.8,
  "MeteringMode": "Spot",
  "Flash": "Off, Did not fire",
  "FocalLength": "3.9 mm",
  "SubjectArea": "1295 967 933 696",
  "FlashpixVersion": "0100",
  "ColorSpace": "sRGB",
  "ExifImageWidth": 2592,
  "ExifImageHeight": 1936,
  "SensingMethod": "One-chip color area",
  "ExposureMode": "Auto",
  "WhiteBalance": "Auto",
  "SceneCaptureType": "Standard",
  "Sharpness": "Soft",
  "ImageWidth": 2592,
  "ImageHeight": 1936,
  "EncodingProcess": "Baseline DCT, Huffman coding",
  "BitsPerSample": 8,
  "ColorComponents": 3,
  "YCbCrSubSampling": "YCbCr4:2:0 (2 2)",
  "Aperture": 2.8,
  "ImageSize": "2592x1936",
  "ShutterSpeed": "1/15",
  "FocalLength35efl": "3.9 mm",
  "LightValue": 3.6
},
{
  "SourceFile": "C:/Users/Me/Desktop/Photos/BU09-C03-07-09.JPG",
  "ExifToolVersion": 8.63,
  "FileName": "BU09-C03-07-09.JPG",
  "Directory": "C:/Users/Me/Desktop/Photos",
  "FileSize": "88 kB",
  "FileModifyDate": "2011:09:09 22:39:08+01:00",
  "FilePermissions": "rw-rw-rw-",
  "FileType": "JPEG",
  "MIMEType": "image/jpeg",
  "JFIFVersion": 1.01,
  "ResolutionUnit": "None",
  "XResolution": 1,
  "YResolution": 1,
  "Comment": "CREATOR: gd-jpeg v1.0 (using IJG JPEG v62), quality = 95\n",
  "ImageWidth": 600,
  "ImageHeight": 400,
  "EncodingProcess": "Baseline DCT, Huffman coding",
  "BitsPerSample": 8,
  "ColorComponents": 3,
  "YCbCrSubSampling": "YCbCr4:2:0 (2 2)",
  "ImageSize": "600x400"
},

As you can see, the first entry contains a lot of information whereas the second entry contains less information (no entry for "flash" for example).

What I am looking to do is, through PHP as I appreciate this works nicely with MySql (or another programming language should you see it more suitable), scan through the JSON file and then populate a MySQL table with the unique column headers. So, for example, the first entry in the file has c.53 unique headings such as filename, filesize, make, model etc.... I'd like the script to put these as column headers and then move on to image 2. If new headers are found, these should be appended and if any duplicates encountered, ignores. Once the JSON file has been completely sifted through for unique entry types, I'd then like the code to then commence populating the respective values into the columns so for example, for image 1, "Apple" would be put into the "Make" field and "iPhone" into the "Model" field etc....

Any suggestions how to do this programatically? Is there a more efficient way of doing this than I suggest above?

Thanks

[diafol]

you can use json_decode: http://php.net/manual/en/function.json-decode.php

1. ensure that you have ALL the possible column headers as field names in a table before you start.
2. read the json file into a php string variable.
3. do a json_decode on the variable, ensuring that you choose the 'associative' parameter to true

$my_arr = json_decode($var, true);

Data is now in an array
4. loop through the array (foreach) and build up your SQL query, e.g.

foreach($my_arr as $key => $value){
  $sql[] = (is_numeric($value)) ? "`$key` = $value" : "`$key` = '" . mysql_real_escape_string($value) . "'"; 
}
$sqlclause = implode(",",$sql);
$rs = mysql_query("INSERT INTO `table` SET $sqlclause");

Something like that anyway. Not tested.

[fenixZ]

//First you need to get all needed columns and CREATE TABLE. For this task you need to read the whole file first.
//Here we go:
//

$f = file_get_contents('json.txt');
$arr = explode('},',$f);  // Prepare for json_decode BUT last } missing
$global_arr = array(); // Contains each decoded json (TABLE ROW)
$global_keys = array(); // Contains columns for SQL


if(!function_exists('json_decode')) die('Your host does not support json');

for($i=0; $i<count($arr); $i++)
{
    $decoded = json_decode($arr[$i].'}',true); // Reappend last } or it will return NULL
    $global_arr[] = $decoded;
    foreach($decoded as $key=> $value)
    {
    $global_keys[$key] = '';
    }
}

// CREATE SQL TABLE
$query = "CREATE TABLE IF NOT EXISTS `json_table` (
  `id` int(11) unsigned NOT NULL auto_increment,";
  foreach($global_keys as $key => $val)
  {
	$query .= "
	 `$key` varchar(100) NOT NULL default '' ,";  
	}
  
  
$query .= " 
PRIMARY KEY  (`id`)
)";
mysql_query($query) or die('mysql_error: '.mysql_error()); 
// iterate $global_arr
for($i=0; $i<count($global_arr); $i++) // this is faster than foreach
{
// NOW use what ardav suggested
    foreach($global_arr[$i] as $key => $value){
    $sql[] = (is_numeric($value)) ? "`$key` = $value" : "`$key` = '" . mysql_real_escape_string($value) . "'";
    }
    $sqlclause = implode(",",$sql);
    $rs = mysql_query("INSERT INTO `json_table` SET $sqlclause");

} // for i
//

THIS IS NOT TESTED but parsed for syntax errors.
The task is almost complete but you need to define more accurate column types.

[h3rb]

hi all, i have a similar 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. 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. So here is the file:

[{"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

[Hex_1]

Just try JTS by Hexide => jts.hexide.pl, easy JSON to SQL, script on GNU, we give You Example, You can edit like You need. And have mercy, this is Startup:D