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();
}