PHP/MySQLi 函数从 3 个表生成 CSV，但分别需要 1 行

Question

这是我在这个项目上的最后一个任务，不确定如何根据我的需要进行调整。我对这一切都很陌生，所以很高兴我已经走到这一步了!

以下函数从 3 个表获取数据并生成一个 CSV 文件(很棒并且工作正常)，但是它将文件分为 3 个部分，理想情况下我希望每行每个订单的所有数据。

就目前情况而言，您会看到 3 个表分为 3 个部分，但我确实需要 1 行内发票 ID 中的所有数据。

代码:

// download invoice csv sheet
if ($action == 'download_csv'){

    header("Content-type: text/csv"); 

    // output any connection error
    if ($mysqli->connect_error) {
        die('Error : ('.$mysqli->connect_errno .') '. $mysqli->connect_error);
    }

    $tables = array('invoices', 'customers', 'invoice_items'); // array of tables need to export

    $file_name = 'invoice-export-'.date('d-m-Y').'.csv';   // file name
    $file_path = 'downloads/'.$file_name; // file path

    $file = fopen($file_path, "w"); // open a file in write mode
    chmod($file_path, 0777);    // set the file permission

    // loop for tables
    foreach($tables as $table) {
        $table_column = array();
        $query_table_columns = "SHOW COLUMNS FROM $table";

        // fetch table field names
        if ($result_column = mysqli_query($mysqli, $query_table_columns)) {
            while ($column = $result_column->fetch_row()) {
                $table_column[] = $column[0];
            }
        }

        // Format array as CSV and write to file pointer
        fputcsv($file, $table_column, ",", '"');

        $query_table_columns_data = "SELECT * FROM $table";

        if ($result_column_data = mysqli_query($mysqli, $query_table_columns_data)) {

            // fetch table fields data
            while ($column_data = $result_column_data->fetch_row()) {
                $table_column_data = array();
                foreach($column_data as $data) {
                    $table_column_data[] = $data;
                }

                // Format array as CSV and write to file pointer
                fputcsv($file, $table_column_data, ",", '"');
            }

        }
    }

    //if saving success
    if ($result_column_data = mysqli_query($mysqli, $query_table_columns_data)) {
        echo json_encode(array(
            'status' => 'Success',
            'message'=> 'CSV has been generated and is available in the /downloads folder for future reference, you can download by <a href="/downloads/'.$file_name.'">clicking here</a>.'
        ));

    } else {
        //if unable to create new record
        echo json_encode(array(
            'status' => 'Error',
            //'message'=> 'There has been an error, please try again.'
            'message' => 'There has been an error, please try again.<pre>'.$mysqli->error.'</pre><pre>'.$query.'</pre>'
        ));
    }


    // close file pointer
    fclose($file);

    $mysqli->close();

}

Answer 1

假设 invoice 在 invoices 表和 customers 表中是唯一的，并且包含我们用于“匹配”行的通用值在所有表格中...

(此处快速说明:这是实际表定义(包括主键、唯一键、外键、列的数据类型，甚至列注释)对于帮助通信非常有值(value)的地方，并且我们不必做出可能错误的假设...)

下面是一个 SQL 查询示例，您需要“组合”所有表中的行，每个 invoice_item 一行

SELECT i.id
     , i.invoice
     , i.foo
     , i.bar

     , c.id        AS c_id
     , c.invoice   AS c_invoice
     , c.name      AS c_name
  -- , c.fee
  -- , c.fi

     , t.id        AS t_id
     , t.invoice   AS t_invoice
     , t.product   AS t_product
     , t.qty       AS t_qty
  -- , t.fo
  -- , t.fum       

  FROM invoice i
  LEFT
  JOIN customer c
    ON c.invoice = i.invoice
  LEFT
  JOIN invoice_item t
    ON t.invoice = i.invoice
 ORDER
    BY i.id
     , c.id
     , t.id

从每个表中添加您需要的任何列，并确保每个列在返回的行中都有一个唯一名称(在 SELECT 中的每个表达式后面使用 AS column_alias列表。)