PHP / MySQLi函数，从3个表生成CSV，但需要单独生成一行

Question

This is my final task on this project and unsure how I can adjust for what I need. I am new to all this, so rather happy I've got this far!

The following function gets data from 3 tables and generates a CSV file (great and works fine) however it splits the file up in 3 sections and ideally I would like all data per order per line.

As it stands, you see the 3 tables in 3 sections but I really need all data from the invoice id within 1 line.

Code:

// 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

Assuming invoice is unique in the invoices table and the customers table, and that contains the common value we use to "match" rows in all of the tables...

(A quick note here: this is where actual table definitions, including PRIMARY KEYs, UNIQUE KEYs, FOREIGN KEYs, datatypes of columns, and even column comments, would be invaluable in aiding communication, and us not having to make possibly erroneous assumptions...)

Here's an example of the SQL query you would need to "combine" the rows from all of the tables, one row per 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

Add whatever columns from each table you need, and make sure each column has a unique name in the returned row (using an AS column_alias after each expression in the SELECT list.)