PHP export from MySQL into CSV

问题:

I need to export data from MySQL to CSV but from specific ID. Table contains ID, ParentID and Name and here is code which exports all records:

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

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

    $output = fopen("php://output",'w') or die("Can't open php://output");
    header("Content-Type:application/csv"); 
    fputcsv($output, array('ID','NAME','PARENTID'));

    // build the tree
    foreach($index as $id => &$row){
      if ($id === 0) continue;
      $parent = $row['PARENTID'];
      $index[$parent][] = &$row;
      fputcsv($output, $row);
    }
    fclose($output) or die("Can't close php://output");
    unset($row);

    // start from this ID
    $index = $index[2];

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

If I have this in my table (this is table declaration):

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

How to export all children's for member with parentID = 2 (recursive)? With other words, I need output like this:

  ID  PARENT    NAME
  3   2         Mike Jones
  4   3         Jason Williams
  5   4         Sara Johnson
  7   2         Amy Martin

That means, exported need to be 2 members with ParentID = 2 but member Mike Jones have child (Jason Williams) which also need to be exported. Mike Williams have also one child (Sara Johnson) which also need to be exported. With other words, function need to find all members with ParentID = 2 but also they childrens and export them all. Thank you for your help.

回答1:

$sql = "SELECT NAME, ID, PARENTID from tablename WHERE ID=2";

回答2:

To find all rows with ID=2 (i.e. Mike and Amy), just modify your MYSQL statement to include a WHERE clause:

    $sql = 'SELECT NAME, ID, PARENTID from tablename WHERE ID="2"';

To find all rows with ID=2, or rows where the parent corresponds to ID=2 (i.e. Mike, Jason and Amy), you can use:

    $sql = 'SELECT t1.NAME FROM test2 AS t1 JOIN test2 AS t2 ON t1.PARENTID = t2.ID WHERE t1.PARENTID = "2" OR t2.PARENTID = "2"';

The JOIN does the 'recursive' bit, finding the IDs for the values in the PARENTID column, so that you can query each line on both the ID of the row itself, and the PARENTID of the row corresponding to the PARENTID.

For one additional level of recursivity (i.e. to find Mike, Jason, Amy and Sara), use:

    $sql = 'SELECT t1.NAME FROM test2 AS t1 JOIN test2 AS t2 ON t1.PARENTID = t2.ID JOIN test2 AS t3 ON t1.PARENTID = t3.ID WHERE t1.PARENTID = '2' OR t2.PARENTID = '2' OR t3.PARENTID = '3'';

In this solution, you will need to add an extra join for each level of recursivity.

回答3:

 $sql = "SELECT NAME, ID, PARENTID from tablename Where ID='2'";