I'm currently developing a function to convert data into CSV format, but I've hit a roadblock.
At the moment, the function is fetching data from two tables and saving it to a CSV file in a single line for each order, including invoice and customer details.
However, I also need to retrieve invoice_items by ID (which may involve looping through multiple items) and add them to the query. Additionally, I'm unsure how to set column titles for the output.
Ideally, the final output should be structured like this:
|invoice details customer details | invoice items
LIST ITEM LIST ITEM LIST ITEM
Instead of the current format:
invoice details | customer details | invoice items LIST ITEM LIST ITEM LIST ITEM
A sample output with real data and titles might look something like this:
id | invoice number | amount | name | address | invoice items (and list below this part)
As for the PHP code, here is what I have so far:
header("Content-type: text/csv");
// Handle any connection errors
if ($mysqli->connect_error) {
die('Error : ('.$mysqli->connect_errno .') '. $mysqli->connect_error);
}
$file_name = 'invoice-export-'.date('d-m-Y').'.csv'; // Define file name
$file_path = 'downloads/'.$file_name; // Define file path
$file = fopen($file_path, "w"); // Open file in write mode
chmod($file_path, 0777); // Set file permissions
$query_table_columns_data = "SELECT *
FROM invoices i
JOIN customers c
ON c.invoice = i.invoice
WHERE i.invoice = c.invoice
ORDER BY i.invoice";
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, ",", '"');
}
}