PHP is widely used for building a wide range of products ranging from web apps to enterprise-level applications. The key to efficient PHP code is to follow proper workflows and automate processes. The result is high quality and bug-free code.
In this post, we are going to discuss how can we create a CSV file from MySQL table data by using a PHP script. Nowadays comma-separated values file data is the most commonly supported file format for export tabular records between web applications. Exporting of Mysql table data to CSV format file is a useful feature in your web application and it will become increasingly common in all types of web-based applications.
This is because this type of file consume more data and gain less space. We can store more data in CSV file format and it consumes less web space as compare to excel file format. So here we have to write a PHP script that makes CSV file from MySQL table data. In PHP script first, we have set the HTTP header for define content type for CSV file and we have also set the header for download file as an attachment and we have also defined the name of the file, then after we have an open file from PHP output stream and then after we have to fetch data from MySQL table and by using file put CSV function we have to write MySQL table data into CSV file line by line. This way we can export MySQL table data to CSV file by using PHP script.
A step-by-step guide to export data to CSV from MySQL using PHP. It is a basic task for any application that needs a reporting feature to CSV; here we are going to explain how to generate a CSV file from the MySQL records.
Table of Contents
Export MySQL Database Tables To Excel XLS Using PHP:
<?php /****************************************************************************/ // Enter The Required Data Here /****************************************************************************/ $connection; // Your Connection Query Variable $TableName= 'YourTableName'; // Define Your Table Name $csv_filename = 'YourFileName.csv'; Define Excel (.csv) file name $downloadQuery = "SELECT * FROM $TableName"; $result = mysqli_query($connection,$downloadQuery); if(!$result){ // Error Reporting Message To User echo "<div class='alert alert-danger'>ERROR # '".mysqli_errno($connection)."' | ERROR: '".mysqli_error($connection)."'. ".exe_contactUs()."</div>"; exit(); die(); } else { // create empty variable to be filled with export data $csv_export = ''; // query to get data from database $field = mysqli_field_count($connection); // create line with field names for($i = 0; $i < $field; $i++) { $csv_export.= mysqli_fetch_field_direct($result, $i)->name.','; } // newline (seems to work both on Linux & Windows servers) $csv_export.= ' '; // loop through database query and fill export variable while($row = mysqli_fetch_array($result)) { // create line with field values for($i = 0; $i < $field; $i++) { $csv_export.= '"'.$row[mysqli_fetch_field_direct($result, $i)->name].'",'; } $csv_export.= ' '; } // Export the data and prompt a csv file for download header('Content-Encoding: UTF-8'); header('Content-Type: text/x-csv; charset=utf-8'); header("Content-Disposition: attachment; filename=$csv_filename"); header("Pragma: no-cache"); header("Expires: 0"); echo $csv_export; } ?>
Customization:
You can change anything in the upper code if you are pro else we do not recommend you to edit the code as it may break while executing and can cause fatal errors to your server.
Troubleshooting the Errors
Do it with concentration and patience. Check your alls steps and again and all codes or scripts. If you find any error you can contact us anytime via comment or better via email, We are always here to help you.
Final Words:
That’s all we have. We hope that you liked this article. If you have any problem with this code in your file then feel free to contact us with a full explanation of your problem. We will reply to you as time allows us or If you have any doubts and problem please comment below. We are happy to help you! If you liked this article, Don’t forget to share this with your friends so they can also take benefit from it and leave your precious feedback in our comment form below. Happy development, See you in the next article.
Be the first to write a comment.