dscrn1974
dscrn1974
2014-08-20 08:45

将数据导出为CSV

已采纳

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)."
";    
        }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."
";
        $loop++;
    }

    $body = $line;

}
print "$head
$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

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • duangai1368 duangai1368 7年前

    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) . "
    ";
    
    点赞 评论 复制链接分享
  • duanli0453 duanli0453 7年前

    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.

    点赞 评论 复制链接分享