PHP export to CSV instead of JSON

Question

I have working code which export records to JSON (please see below) and now I need that records are exported to CSV. Code below have "root node" which means, it will export members with PARENTID=2 and all they children's (recursively). What I need is that exported are records for given PARENTID but in CSV instead in JSON.

    <?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "mydataabse";
    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 
    $index = array();
    $sql = "SELECT NAME, ID, PARENTID FROM mytable";
    $result = $conn->query($sql);

    while($row = $result->fetch_array(MYSQLI_ASSOC)){
      $rows[] = $row;
      $index[$row['ID']] = $row;
    }

    // build the tree
    foreach($index as $id => &$row){
      if ($id === 0) continue;
      $parent = $row['PARENTID'];
      $index[$parent]['children'][] = &$row;
    }
    unset($row);

    // root node - exported are members with this PARENTID and all they children's
    $index = $index[2]['children'];

    /* free result set */
    $result->close();
    /* close connection */
    $conn->close();

    // output json
    header('Content-Type: application/json');
    echo json_encode($index, JSON_PRETTY_PRINT);

Here is mytable structure, if needed:

ID  PARENT    NAME
1     0       John Doe
2     1       Sally Smith
3     2       Mike Jones
4     3       Jason Williams
5     4       Sara Johnson
6     1       Dave Wilson
7     2       Amy Martin

Thank you so much.

Answer 1

You can use the native function fputcsv like this for example

EDIT : Included a full example with your code

<?php

$rows = array(
array('id' => 1, 'Parent' => 0, 'name' => 'John Doe'),
array('id' => 2, 'Parent' => 1, 'name' => 'Sally Smith'),
array('id' => 3, 'Parent' => 2, 'name' => 'Mike Jones'),
array('id' => 4, 'Parent' => 3, 'name' => 'Jason Williams'),
array('id' => 5, 'Parent' => 4, 'name' => 'Sara Johnson'),
array('id' => 6, 'Parent' => 1, 'name' => 'Dave Wilson'),
array('id' => 7, 'Parent' => 2, 'name' => 'Amy Martin'),
);
// create an index on id
$index = array();

foreach($rows as $row){
  $index[$row['id']] = $row;
}

// build the tree
foreach($index as $id => &$row){
  if ($id === 0) continue;
  $parent = $row['Parent'];
  $index[$parent]['children'][] = &$row;
}
unset($row);

// obtain root node
$index = $index[2]['children'];

// Construct the final datas
$columns = array('Name', 'Id', 'ParentID');

$datas = array($columns);
foreach($index as $data) {
    $datas[] = array(
        $data['name'],
        $data['id'],
        $data['Parent']

    );
}

// Tell the client to download file
headerDownload('mySuperFile.csv');

// Export the CSV to standard output
exportToCSV('php://output', $datas);

function exportToCSV($file, array $values, $delimiter = ";", $enclosure = '"', $escape = "\\")
{
    $handler = @fopen($file, 'w');

    if(! $handler) {
        throw new Exception(sprintf("Unable to open stream '%s'", $file));
    }

    foreach ($values as $value) {
        fputcsv($handler, $value, $delimiter, $enclosure, $escape);
    }
    return fclose($handler);
}

function headerDownload($filename)
{
    // disable caching
    $now = gmdate("D, d M Y H:i:s");
    header("Expires: Tue, 03 Jul 2001 06:00:00 GMT");
    header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");
    header("Last-Modified: {$now} GMT");

    // force download
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");

    // disposition / encoding on response body
    header("Content-Disposition: attachment;filename={$filename}");
    header("Content-Transfer-Encoding: binary");
}