Demo for Socrata API JSON import into MySQL

demobuild.php

<?php
// Credentialing for MySQL server if you want the code to create table and load data automatically
$host = "localhost";
$user = "user";
$password = "password";
$base = "database";
$connection = mysql_connect($host,$user,$password) or die (mysql_error());
mysql_select_db($base,$connection);

// $id is the unique identifier for the Socrata data.
// You can find it in the API link from the Socrata platform
if($_GET['id'] <> '') {
	$id = $_GET['id'];
} else {
	$id = 'yef5-h88r';
}

// $socrata is the URL of the Socrata implementation you're using.
$socrata = 'data.honolulu.gov';

$url = "http://".$socrata."/api/views/".$id."/rows.json";
$json = file_get_contents($url);
$out = json_decode($json, true);

// The foreach loops below parse the metadata for the Socrata data.
// It is required for the data load as it counts the number of fields required for load.
$fields = '';
$keys = '';
$import = '';
$counter = 0;
$metacounter = 0;
foreach ($out[meta] as $meta) {
	$table = str_replace(' ', '_', $meta[name]);
	foreach ($meta[columns] as $columns) {
		if($columns[dataTypeName] <> 'meta_data') {
			$fields = $fields.'`'.$columns[fieldName].'` varchar('.$columns[width].') NOT NULL,';
			$keys = $keys.'KEY `'.$columns[fieldName].'` ('.$columns[fieldName].'),';
			$import = $import.$columns[fieldName].', ';
			$counter ++;
		} else {
			$metacounter++;
		}
	}
}

$keys = substr($keys, 0, strlen($keys)-1);
$import = substr($import, 0, strlen($import)-2);

// $sql can be removed if you're not creating a MySQL table and just importing into an existing table created by this code.
$sql = "CREATE TABLE IF NOT EXISTS ".$table." (".$fields." ".$keys.") ENGINE=MyISAM DEFAULT CHARSET=utf8;";
mysql_query($sql) or die (mysql_error()); 
mysql_query("TRUNCATE TABLE ".$table) or die (mysql_error()); 

// This foreach loop imports the data into the newly created table.
foreach ($out[data] as $value) {
	$count = 0;
	$values = '';
	while($count <= $counter - 1) {
		if(is_array($value[$metacounter + $count]) == 0) {
			$values = $values.'"'.mysql_real_escape_string($value[$metacounter + $count]).'", ';
			$count ++;
		} else {
			$values = $values.'"", ';
			$count ++;
		}
	}	
	$values = substr($values, 0, strlen($values)-2);
	$sql = "INSERT INTO ".$table." (".$import.") VALUES (".$values.")";
	mysql_query($sql) or die (mysql_error()); 
}
?>