PHP从MySQL导出到CSV [英] PHP export from MySQL into CSV

问题描述

我需要将数据从MySQL导出到CSV，但要从特定ID导出.该表包含ID，ParentID和Name，这是导出所有记录的代码:

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

如何导出具有parentID = 2(递归)的成员的所有子代? 换句话说，我需要这样的输出:

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

这意味着，导出的必须是2个具有ParentID = 2的成员，但是成员Mike Jones的孩子(Jason Williams)也需要导出.迈克·威廉姆斯(Mike Williams)也有一个孩子(萨拉·约翰逊(Sara Johnson))，也需要将其出口.换句话说，函数需要查找ParentID = 2的所有成员，但也要找到所有成员并将其全部导出.谢谢您的帮助.

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.

推荐答案

要查找ID = 2的所有行(即Mike和Amy)，只需修改MYSQL语句以包含WHERE子句即可:

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"';

要查找ID = 2的所有行，或查找父级对应于ID = 2的行(例如Mike，Jason和Amy)，可以使用:

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"';

JOIN执行递归"位，在PARENTID列中查找值的ID，以便您既可以查询行本身的ID，又可以查询与PARENTID对应的行的PARENTID的每一行.

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.

要获得更高级别的递归性(即找到Mike，Jason，Amy和Sara)，请使用:

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.

这篇关于PHP从MySQL导出到CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！