Exporting data to CSV

Question

I have very particular problem trying to create my csv file to download

the csv before upload has its data like so..

| HEADER1 | HEADER2 | HEADER3 | HEADER4 | -> | HEADER350 |

| col1data | col2data | col3data | col4data | ->...

| col1data | col2data | col3data | col4data | ->...

| col1data | col2data | col3data | col4data | ->...

| col1data | col2data | col3data | col4data | ->...

when trying to "re-create" the csv file for output im running into problems. currently i have my code like..

$sql = "SELECT id, title, data FROM table ORDER BY ID ASC;";
if(!$result = $mysqli->query($sql))
{ 
    die('There was an error running the query [' . $mysqli->error . ']'); 
}
else
{
  $headers  = array();
  $dataList = array(); 

  while( $row = $result->fetch_assoc() )
  {
    $headers[] = $row['title'];
    $dataList[$row['title']] = json_decode($row['data'], false);
  }

  $head="";
  $line="";

  $totalRows  = count($dataList);   
  if(is_array($dataList))
  {
    // HEADERS
    $firstCount = 1;
    foreach($dataList as $key=>$value)
    {
        if($firstCount==$totalRows){
            $head.= strtoupper($key)."\n\r";    
        }else{
            $head.= strtoupper($key).",";   
        }
        $firstCount++;
    }

    $loop = 0;
    foreach($headers as $headR)
    {
        for($i=0; $i<=$totalRows; $i++)
        {
            if(isset($dataList[$headR][$i])){
                $row.= $dataList[$headR][$i].",";
            }else{
                $row.= ",";
            }
        }
        $line.=$row."\n";
        $loop++;
    }

    $body = $line;

}
print "$head\n$body";
}

So basically I'm printing the headers first to the page..

col1, col2, col3, col4, col5, col6 -->,col350

Then I'm trying to output the associated data for each col.. which should be like below

$dataList['col1'][0], $dataList['col2'][0], $dataList['col3'][0], $dataList['col350'][0]

$dataList['col1'][1], $dataList['col2'][1], $dataList['col3'][1], $dataList['col350'][1]

$dataList['col1'][2], $dataList['col2'][2], $dataList['col3'][2], $dataList['col350'][2]

$dataList['col1'][3], $dataList['col2'][3], $dataList['col3'][3], $dataList['col350'][3]

hopefully that makes some sense, because its frying my brain..

cheers

Marty

Answer 1

Nice piece of code you got there, but i think you are overthinking this :-) It's easier to just transform the data before you are outputting it, since outputting it is easy!

<?php

$sql = "SELECT id, title, data FROM table ORDER BY ID ASC;";
if (!$result = $mysqli->query($sql))
    die('There was an error running the query [' . $mysqli->error . ']');

$dataList = array();
$maxDataCount = 0;  
while ($row = $result->fetch_assoc()) {
    $title = strtoupper($row['title']);
    $data = json_decode($row['data'], false);
    // keep the maximum number of data rows. We need to fill in empty gaps later on.
    $maxDataCount = max($maxDataCount,count($data));
    $dataList[] = array( $title, $data );
}
foreach($dataList as $row)
{
    // the first row of the new output array is an array containing headers
    // we just push new headers to that array.
    if (!isset($output[0]))
        $output[0] = array();
    // 0 = title, 1 = data
    $output[0][] = $row[0];
    $data = $row[1];
    // some magic: we append every value to it's corresponding row
    // if the value does not exist, we put NULL
    for($i = 0, $l = $maxDataCount; $i < $l; $i++)
    {
        $idx = $i+1;
        if (!isset($output[$idx]))
            $output[$idx] = array();
        $output[$idx][] = empty($data[$i]) ? NULL : $data[$i];
    }
}

foreach($output as $row)
    echo implode(',',$row) . "\n";

Answer 2

You're already using variable $row in the mysql part of your code, and later on, you're trying to use it to create your temporary string.

Then, you're never resetting your $row variable in your "line" loop, you keep adding the same content again and again in your $line variable, which result in repeated columns I guess.

Adding $row = ''; at the very beginning of you second foreach should be enough.