PHP export from MySQL into CSV

Question

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.

ATJ · Answer 1 · 2017-03-03T13:45:52.100

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.

score 1 · Answer 2 · answered Mar 03 '17 at 11:46

1

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

answered Mar 03 '17 at 11:46

Awais Rafique

466
6
17

score 0 · Answer 3 · answered Mar 03 '17 at 11:48

0

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

answered Mar 03 '17 at 11:48

Rahul Prajapati

426
1
4
12

the example shows, that he whats all entries from the parent-ID 2 and all recursiv childs. – Radon8472 Mar 03 '17 at 11:56
Hello @Radon8472, you have right. I need 4 records - those 2 with parentID = 2 and additional they childrens.. do you have some example how to do recursive call? – ZMik Mar 03 '17 at 12:09
Better, if you please give some details and explain about your answer. – Deep Kakkar Mar 03 '17 at 12:20
@Deep, I need to get records for all members with ID = 2 and they childrens. With other words, export need to create CSV file with members which have parentID = 2 but also they childrens. In my initial question I posted table structure (please search for "If I have this in my table:" and table is below that. There you can see that I have 2 members which have parentID = 2 but Mike Jones have one child Jason Williams which have also one child Sara Johnson. That means, export need to export 2 members with parentID = 2 and all they childrens. – ZMik Mar 03 '17 at 12:30
I`m looking for an easy answer too. You could check this article, it looks good: http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query – Radon8472 Mar 03 '17 at 12:51
I'm not sure about MySQL, I think it is PHP case. – ZMik Mar 03 '17 at 13:05

PHP export from MySQL into CSV

3 Answers3

Linked

Related