Demo for Socrata API JSON import into MySQL

demobuild.php

<?php

function hobo_escape($val){
	// because there is no good way to escape a dynamic table/field name
	$ret = preg_replace('/[^a-zA-Z _]+/', '', $val);
	$ret = preg_replace('/\s+/', '_', $ret);
	return $ret;
}

// Credentialing for MySQL server if you want the code to create table and load data automatically
$debug = true;
$include_metadata = false;
$host = "localhost";
$user = "user";
$password = "password";
$base = "database";

// $id is the unique identifier for the Socrata data.
// You can find it in the API link from the Socrata platform
if(isset($_GET['id']) && $_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.
$metadata_fields = array();
$fields = array();
$keys = array();
$import = array();
foreach ($out['meta'] as $meta) {
	$table = hobo_escape($meta['name']);
	foreach ($meta['columns'] as $index => $columns) {
		$fieldName = hobo_escape($columns['fieldName']);
		$width = intval($columns['width']);

		if ($columns['dataTypeName'] === 'meta_data') {
			if ($include_metadata === false) {
				$metadata_fields[] = $index;
				continue;
			}
		}
		$fields[] = "`{$fieldName}` varchar({$width}) NOT NULL";
		$keys[] = "KEY `{$fieldName}` ({$fieldName})";
		$import[] = "`{$fieldName}`";
	}
}

$fields_str = implode(", ", $fields);
$keys_str = implode(", ", $keys);
$import_str = implode(", ", $import);

// $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_str} {$keys_str}) ENGINE=MyISAM DEFAULT CHARSET=utf8;";
if (!$debug){
	$connection = mysql_connect($host,$user,$password) or die (mysql_error());
	mysql_select_db($base,$connection);
	mysql_query($sql) or die (mysql_error()); 
	mysql_query("TRUNCATE TABLE `{$table}`") or die (mysql_error()); 
} else {
	var_dump($sql);
}

// This foreach loop imports the data into the newly created table.
foreach ($out['data'] as $row) {
	$values = array();
	foreach($row as $index => $value) {
		if (in_array($index, $metadata_fields, true)){
			continue;
		}
		if (is_array($value) === false) {
			$val = mysql_real_escape_string($value);
			$values[] = "\"{$val}\"";
		} else {
			$values[] = '""';
		}
	}
	$values_str = implode(", ", $values);
	$sql = "INSERT INTO `{$table}` ({$import_str}) VALUES ({$values_str})";
	if (!$debug){
		mysql_query($sql) or die (mysql_error()); 
	} else {
		var_dump($sql);
	}
}