json_file_to_db_to_csv.php

<?php
$link = mysql_connect('localhost', '', '');
mysql_select_db('');

$create_table = "CREATE TABLE IF NOT EXISTS `json` (
`id` int(11) NOT NULL,
  `reporting-org_type` text NOT NULL,
  `reporting-org_ref` text NOT NULL,
  `reporting-org_text` text NOT NULL,
  `iati-identifier` text NOT NULL,
  `title` text NOT NULL,
  `activity-status_code` text NOT NULL,
  `activity-status_text` text NOT NULL,
  `activity-date_start-actual` text NOT NULL,
  `activity-date_end-actual` text NOT NULL,
  `contact-info_organisation` text NOT NULL,
  `contact-info_website` text NOT NULL,
  `participating-org_lang` text NOT NULL,
  `participating-org_role` text NOT NULL,
  `participating-org_text` text NOT NULL,
  `recipient-country_code` text NOT NULL,
  `recipient-country_text` text NOT NULL,
  `policy-marker` text NOT NULL,
  `budget_value-date` text NOT NULL,
  `budget_currency` text NOT NULL,
  `budet_text` text NOT NULL,
  `last-updated-datetime` text NOT NULL,
  `default-currency` text NOT NULL
);";


mysql_query($create_table);

$file = file_get_contents("source.json");

$data = json_decode($file, TRUE);
$data = $data['iati-activities'];

$columns_array = array();
$result = mysql_query("SHOW COLUMNS FROM json");
while ($row = mysql_fetch_assoc($result)) {
    if($row['Field']!="id")
        $columns_array[] = "`".$row['Field']."`";
}
$columns = implode(', ', $columns_array);

$i = 0;
foreach($data['iati-activity'] as $line){

    if($i<123451){
        $key_values = array(
          mysql_real_escape_string($line['reporting-org']['_type']),
          mysql_real_escape_string($line['reporting-org']['_ref']),
          mysql_real_escape_string($line['reporting-org']['__text']),
          mysql_real_escape_string($line['iati-identifier']),
          mysql_real_escape_string($line['title']),
          mysql_real_escape_string($line['activity-status']['_code']),
          mysql_real_escape_string($line['activity-status']['__text']),
          mysql_real_escape_string($line['activity-date'][0]['_iso-date']),
          mysql_real_escape_string($line['activity-date'][1]['_iso-date']),
          mysql_real_escape_string($line['contact-info']['organisation']),
          mysql_real_escape_string($line['contact-info']['website']),
          mysql_real_escape_string($line['participating-org']['_xml:lang']),
          mysql_real_escape_string($line['participating-org']['_role']),
          mysql_real_escape_string($line['participating-org']['__text']),
          mysql_real_escape_string($line['recipient-country']['_code']),
          mysql_real_escape_string($line['recipient-country']['__text']),
          mysql_real_escape_string($line['policy-marker']),
          mysql_real_escape_string($line['budget']['value']['_value-date']),
          mysql_real_escape_string($line['budget']['value']['_currency']),
          mysql_real_escape_string($line['budget']['value']['__text']),
          mysql_real_escape_string($line['_default-currency']),
          mysql_real_escape_string($line['_last-updated-datetime'])
        );

        $values = implode("', '", $key_values);

        $query = "insert into json($columns) values('$values')";

        mysql_query($query);
        print mysql_error($link)."<br/>";
    }

    $i++;
}



for ($i = 0; $i < count($columns_array); $i++) {
    $result .= '"'.$columns_array[$i].'",';
}
$result .="\n";

while ($row = mysql_fetch_array($sql)) {
    for ($i = 0; $i < count($columns_array); $i++) {
        $result .='"'.$row["$i"].'",';
    }
    $result .="\n";
}

$filename = "myFile.csv";
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);