kinlane
2/25/2016 - 12:05 AM

Export ClinicalTrials.gov Database As JSON

Export ClinicalTrials.gov Database As JSON

<?php

$host = "[your database server";]
$database = "[your database name]";
$user = "[your user]";
$pass = "[your password]";

// database connection
mysql_connect($host, $user, $pass) or die ('cannot connect to the database: ' . mysql_error());
mysql_select_db($database) or die ('cannot select database: ' . mysql_error());

function safe_json($json) {
            $json = empty($json) ? '[]' : $json ;
            $search = array('\\',"\n","\r","\f","\t","\b","'") ;
            $replace = array('\\\\',"\\n", "\\r","\\f","\\t","\\b", "&#039");
            $json = str_replace($search,$replace,$json);
            return $json;
        }

// function for creating default 200 OK
function getOK($table)
	{
		
	$R = array();
	$R['description'] = $table . " response";		
	$R['schema'] = array();
	
	$S = array();
	$S['type'] = "array";
	
	$I = array();
	$I['$ref'] = "#/definitions/" . $table;
	
	$S['items'] = $I;
	
	$R['schema'] =  $S;			
	
	$ok = new stdClass;
	$response = "200";
	$ok->$response = $R;		
		
	return $ok;	
	}

$Spec_Name = str_replace("_"," ",$database);
$Spec_Name = ucwords($Spec_Name);

// loop through table + fields
$loop = mysql_query("SHOW tables FROM " . $database) or die ('cannot select tables');

while($table = mysql_fetch_array($loop))
{
	
	// We will need to identif
	$this_key = "";
	
	// The table name
    $table = $table[0];
	
	$ReturnObject = array();
	
	if($table=='arm_groups' || $table=='authorities' || $table=='central_contacts' || $table=='central_contacts')
		{	
	
		$select = "SELECT * FROM `" . $table . "`";
		
		$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );
	
		$field_names = mysql_num_fields ( $export );
		
		for ($i = 0; $i < $field_names; $i++)
			{
		   	$fields[$i] = mysql_field_name( $export , $i );
			}
	
		while( $row = mysql_fetch_row( $export ) )
			{
			
			$Object = array();
			
			$col = 0;
		    foreach( $row as $value )
		    	{
		    	$field_name = $fields[$col];		    	                                        
			    $Object[$field_name] = $value;
				$col++;
			    }
			
		    array_push($ReturnObject,$Object);
			
			}
		
		$data = stripslashes(safe_json(json_encode($ReturnObject,JSON_HEX_QUOT)));
			
		$file_name = $table . ".json";
		$writefile = "files/" . $file_name;
		echo "writing " . $writefile . "\n";
		$myfile = fopen($writefile, "w") or die("Unable to open file!");
		fwrite($myfile, $data);
		fclose($myfile);
		}
	} 
?>