How to Export Data from MySQL Database to a CSV file Using PHP and MySQLi
What Will I Learn?
- You will learn how to export data from a MySQL database using PHP programming language and MySQLi queries.
Requirements
- A Computer System is required for the purpose of this tutorial.
- WAMP/MAMP/LAMP/XAMPP or any other PHP/MySQL web server stack.
- A Text Editor(Notepad ++, Sublime text, Brackets, Codeblocks, PHP Storm, Atom or any other text editor).
- A Basic Knowledge in HTML, PHP and MySQL
Difficulty
- Intermediate (The codes in this tutorial requires only basic knowledge of the HTML, PHP and MySQL languages, hence the difficulty is intermediate)
Tutorial Contents
Following the previous tutorial on CSV upload, in this tutorial we are going to learn how to export data from our MySQL database to a Microsoft Excel CSV file and it will display just the way it is in the database.
STEP 1: Create an HTML form with a submit button
<html>
<head>
<title>CSV Export</title>
</head>
<body>
<form action="<?php echo $_SERVER["PHP_SELF"]; ?>" method="POST">
<input type="submit" name="export_csv_btn" value="Export Data into CSV" />
</form>
</body>
</html>
Code Explanation:
<title>CSV Export</title>this is what will display on the browser tab, here thetitleof the page will beCSV Exportaction="<?php echo $_SERVER["PHP_SELF"]; ?>"this means that the code that will process the form is in the same page with the form. If another page is to be used for processing the form, it will beaction="pagename.php".method="POST"this refers to how the form will be processed. ThePOSTmethod is mostly used when processing forms because it keeps the formvaluehidden in the browser URL bar. Other ways of passing information in PHP is theGETetc.<input type="submit" name="export_csv_btn" value="Export Data into CSV" />This is the HTML submit button that was created to. Its function in this tutorial is to process the data export to CSV query when the button is clicked. That is for the export query to run, the HTML submit button must be clicked.
Note: It is believed that you(readers) have a basic knowledge knowledge on HTML, hence I will assume that every other HTML tag in the above codes is familiar to you.
STEP 2: Create a Database Connection
<?php
//Turn off all error messages
error_reporting(0);
//Create Connection
$connection = mysqli_connect("localhost", "root", "password", "testCSV");
// Check Connection
if (!$connection) {
die("Connection failed: " . mysqli_connect_error());
}
?>
Code Explanation
error_reporting(0);This turns off all default unnecessary error messages displayed by the server.$connection = mysqli_connect("localhost", "root", "password", "testCSV");This creates a connection to the database. In this tutorial, just like in the previous the Severname islocalhost, database user isroot, database password ispasswordand the database name istestCSV.if (!$connection) {die("Connection failed: " . mysqli_connect_error());}This is used to check whether the connection was successful. If the connection is not successful, an error message will display on the browser showing the error.
STEP 3: Write the Database Export to CSV Query
<?php
if(isset($_POST['export_csv_btn'])){
$query = "SELECT name, email FROM users";
$dataTable = mysqli_query($connection, $query);
$rowTable = mysqli_num_rows($dataTable);
if($rowTable >= 1){
$file = "export/" . strtotime(now) . ".csv";
$openFile = fopen($file,"w");
echo "Export Successful<br>";
$allData = mysqli_fetch_assoc($dataTable);
$line = 0;
foreach($allData as $name => $value){
$line++;
if($line<2){
$label .= $name .",";
}else{
$label .= $name ."\n";
}
}
$query = "SELECT name, email FROM users";
$dataTable2 = mysqli_query($connection, $query);
while($allData2 = mysqli_fetch_assoc($dataTable2)){
$dataValue .= $allData2["name"] .",". $allData2["email"] ."\n";
}
fputs($openFile,$label . $dataValue);
echo "<a href='$file'>Download Excel file here</a>";
}else{
echo "You don't have any data from MySQL...";
}
}
?>
Code Explanation:
if(isset($_POST['export_csv_btn']))checks if the submit button has been clicked, if it has been clicked then the query nested in its curly braces{}will be executed.$query = "SELECT name, email FROM users";Query to select thename, andemailfields from the database. Remember this will be done if the the submit button is clicked.$dataTable = mysqli_query($connection, $query);This executes the MySQL query$query = "SELECT name, email FROM users";.$rowTable = mysqli_num_rows($dataTable);stores all the data in the database to a variable which will be used in the process of tutorial.if($rowTable >= 1)this checks if the database contains any data that will exported. If it does, it will then proceed to the next instruction.$file = "export/" . strtotime(now) . ".csv";This saves the format of naming the csv file. The format involves the current timestamp of the computer system to be appended with a.csvat the end and this will be saved in a folder calledexportinside the project directory. Hence, you must create folder namedexportin your project directory where the exported database to csv files will be saved.$openFile = fopen($file,"w");this exports the database content into a folder nameexportin your project directory, saves the file to be exported with the name as the current time of the computer as at when the query was executed and it appends.csvto the name of the file.$allData = mysqli_fetch_assoc($dataTable);This fetches all the data in the database.$line = 0;foreach($allData as $name => $value){$line++;if($line<2){$label .= $name .",";}else{$label .= $name ."\n";}}This is loop that loops through all the database entries and ensures that the database field title is not selected among the entries or data that will be fetched and exported into theCSVfile. That is the first entry field which is the database column/field title will not be fetched.fputs($openFile,$label . $dataValue);this now put all the fetched data into the csv file that was created by the queries above.echo "<a href='$file'>Download Excel file here</a>";This is a link that will display for users to download the exported csv file if the query was successful.echo "You don't have any data from MySQL...";This will display if the selected database is empty.
STEP 4: Close Database Connection
//Close Connection
mysqli_close($connection);
mysqli_close($connection);: This closes the earlier created database connection.
Curriculum
Related Posts on Utopian-io:
Posted on Utopian.io - Rewarding Open Source Contributors
why am i not a programmer? I love your dedication by the way. Keep it up
Thank you very much @kivar. You can still learn how to code if you wish. It's never too late.
Thank you for the contribution. It has been approved.
You can contact us on Discord.
[utopian-moderator]
Thank you very much @amosbastian for approval. I am grateful.
Hi You are writing nicely and infrmative.. I am facing bandwidth exceed issue and after 3 days today i able to usemy account
Oh, I am very sorry about the bandwidth issue. Thank God you are back online. Blog on @biyanoor.
Yeah i ll try to do.. Another issue i am seriously sick since three days.. Fever not going down than 102°F.. So facing weeknes and not able to do my routine work
Hey @casweeney I am @utopian-io. I have just upvoted you!
Achievements
Suggestions
Get Noticed!
Community-Driven Witness!
I am the first and only Steem Community-Driven Witness. Participate on Discord. Lets GROW TOGETHER!
Up-vote this comment to grow my power and help Open Source contributions like this one. Want to chat? Join me on Discord https://discord.gg/Pc8HG9x