title: Formatting PHP/MySQL data in correct JSON format for HighCharts
body: <p>I have been pulling my hair out for several days trying to get some data organized into the appropriate JSON format so that it can be plotted in a HighCharts graph.</p>
  
  <p>My plan is to execute a jQuery function to call a PHP script which pulls a few columns of weather data from a MySQL database. I can re-ogranize data on the JavaScript side, but would prefer to have all of the data organization done in PHP and then have a single, correct JSON structure going into the HighCharts engine.</p>
  
  <p>I am looking at weather data, so I have several columns of data sampled at every minute. The data columns I am retrieving from MySQL are: JSTimestamp (which is a unix timestamp * 1000 for JavaScript), avgOutdoorTemperature, avgIndoorTemperature, avgOutdoorHumidity, avgIndoorHumidity. I have populated each of these columns in their own array.</p>
  
  <p>I can't seem to wrap my head around the PHP logic required to manipulate the data into the following JSON/array format:</p>
  
  <pre><code>[
    [
      [JSTimestamp1, avgOutdoorTemperature1],
      [JSTimestamp2, avgOutdoorTemperature2],
      [JSTimestamp3, avgOutdoorTemperature3],
      [JSTimestampN, avgOutdoorTemperatureN]
    ],
    [
      [JSTimestamp1, avgIndoorTemperature1],
      [JSTimestamp2, avgIndoorTemperature2],
      [JSTimestamp3, avgIndoorTemperature3],
      [JSTimestampN, avgIndoorTemperatureN]
    ],
    [
      [JSTimestamp1, avgOutdoorHumidity1],
      [JSTimestamp2, avgOutdoorHumidity2],
      [JSTimestamp3, avgOutdoorHumidity3],
      [JSTimestampN, avgOutdoorHumidityN]
    ],
    [
      [JSTimestamp1, avgIndoorHumidity1],
      [JSTimestamp2, avgIndoorHumidity2],
      [JSTimestamp3, avgIndoorHumidity3],
      [JSTimestampN, avgIndoorHumidityN]
    ]
  ]
  </code></pre>
  
  <p>I have populated the above structure with dummy data and it plots beautifully in HighCharts. Here's a sample demo showing what the graph will look like: <a href="http://highcharts.com/jsbin/afutef/11/edit#javascript,live" rel="nofollow">http://highcharts.com/jsbin/afutef/11/edit#javascript,live</a></p>
  
  <p>My current code is:</p>
  
  <pre><code>&lt;?php
  
  include_once('./include/mysqldatabase.php');
  include_once('./include/mysqlresultset.php');
  
  date_default_timezone_set('America/New_York');
  
  $MYSQL_HOST = 'ip_address';
  $MYSQL_USER = 'username';
  $MYSQL_PASS = 'password';
  $MYSQL_NAME = 'weatherdata';
  
  $db = MySqlDatabase::getInstance();
  try {
      $conn = $db-&gt;connect($MYSQL_HOST, $MYSQL_USER, $MYSQL_PASS, $MYSQL_NAME);
  }
  catch (Exception $e) {
      die($e-&gt;getMessage());
  }
  
  $query = "
      select
          RecDate,
          unix_timestamp(RecDateTime)*1000 as JSTime,
          avgOutdoorTemperature,
          avgIndoorTemperature,
          avgOutdoorHumidity,
          avgIndoorHumidity
      from `rollup-minute-60`
      where 
          RecDate &gt; 201301010000
      ";
  
  $timeArray = array();
  $avgOutdoorTemperatureArray = array();
  $avgIndoorTemperatureArray = array();
  $combinedArray = array();
  $i = 0;
  
  $seriesNames = array('Average Outdoor Temperature','Average Indoor Temperature', 'Average Outdoor Humidity', 'Average Indoor Humidity');
  
  foreach ($db-&gt;iterate($query) as $r) {
      $jstime = $r-&gt;JSTime;
      $avgOutdoorTemperature = $r-&gt;avgOutdoorTemperature;
      $avgIndoorTemperature = $r-&gt;avgIndoorTemperature;
      $avgOutdoorHumidity = $r-&gt;avgOutdoorHumidity;
      $avgIndoorHumidity = $r-&gt;avgIndoorHumidity;
  
      $timeArray[$i] = (float)$jstime;
      $avgOutdoorTemperatureArray[$i] = (float)$avgOutdoorTemperature;
      $avgIndoorTemperatureArray[$i] = (float)$avgIndoorTemperature;
      $avgOutdoorHumidityArray[$i] = (float)$avgOutdoorHumidity;
      $avgIndoorHumidityArray[$i] = (float)$avgIndoorHumidity;    
  
      $combinedArray[$i] = array((float)$avgOutdoorTemperature, (float)$avgIndoorTemperature, (float)$avgOutdoorHumidity, (float)$avgIndoorHumidity);
  
      $i++;
  }
  
  echo "Combined array:&lt;br&gt;";
  echo json_encode($combinedArray);
  echo "&lt;br&gt;&lt;br&gt;&lt;br&gt;";
  
  echo "Time array:&lt;br&gt;";
  echo json_encode($timeArray);
  echo "&lt;br&gt;&lt;br&gt;&lt;br&gt;";
  
  $arraySize = sizeof($timeArray);
  $seriesNameSize = sizeof($seriesNames);
  $outputJSONArray = array();
  $tempArray = array();
  
  for ($i=0; $i&lt;$seriesNameSize; $i++) {
  
      for ($j=0; $j&lt;$arraySize; $j++) {
          $tempArray = array();
  
          array_push($tempArray, $timeArray[$j], $combinedArray[$j][$i]);
          echo "i=".$i.", j=".$j.", tempArray: " . json_encode($tempArray) ."&lt;br&gt;";
  
          $outputJSONArray[$i] = $tempArray;
  
       } 
  }
  
  echo json_encode($outputJSONArray);
  
  ?&gt;
  </code></pre>
  
  <p>With the current code above, my output array is only showing the last element in $outputJSONArray instead of the multi-dimensional construct.</p>
  
  <p>Can anyone help point me in the right direction? I have found a lot of good information on this site to help me get started but need a little assistance getting over the final hurdle.</p>
  
  <p>Thank you!</p>