In this article, we are going to load the data present in the database (MySQL) into CSV file. Display the data in the database and also export that data into CSV file. We are using XAMPP tool to store locally in a database.
XAMPP stands for cross-platform, Apache, MySQL, PHP, and Perl. It is among the simple light-weight local servers for website development.
Steps:
- Create database and create table in your XAMPP server(MySQL ) database.
- Write PHP Script to access that data
- Verify results
Scenario with CSV: Create two PHP scripts named index.php and export.php Place these files in xampp/htdocs/folder/
index.php: Display the columns along with data using mysqli_fetch_array. The fetch_array() / mysqli_fetch_array() function fetches a result row as an associative array, a numeric array, or both.
Execution Steps:
1. Open XAMPP Server and start Apache and MySQL server
2. Create database with name “article_neveropendata”
3. Create table inside this database named “neveropendata” and insert data
Open Notepad and type the following code. Save this file as index.php
PHP
<?php // Connect syntax $connect = mysqli_connect("localhost", "root", "", "article_neveropendata"); // Display data from neveropendata table $query ="SELECT * FROM neveropendata"; // Storing it in result variable $result = mysqli_query($connect, $query); ?>// HTML code to display our data// present in table<!DOCTYPE html><html><head> <script src= </script> <link rel="stylesheet" href= <script src= </script></head><body> <div class="container" style="width:900px;"> <h2 align="center">Geeks for Geeks</h2> <h3 align="center"> Export data into CSV from Data Base </h3> <br /> <!-- After clicking on submit button export.php code is revoked --> <form method="post" action="export.php" align="center"> <input type="submit" name="export" value="CSV Export" class="btn btn-success" /> </form> <br /> <!-- Code for table because our data is displayed in tabular format --> <div class="table-responsive" id="employee_table"> <table class="table table-bordered"> <tr> <th width="5%">UserID</th> <th width="35%">Name</th> <th width="10%">Article</th> <th width="20%">Article Type</th> <th width="5%">Published Date</th> </tr> <?php // Fetching all data one by one using // while loop while($row = mysqli_fetch_array($result)) { ?> <!-- taking attributes and storing in table cells --> <tr> <!-- column names in table --> <td><?php echo $row["user_id"]; ?></td> <td><?php echo $row["name"]; ?></td> <td><?php echo $row["article"]; ?></td> <td><?php echo $row["article_type"]; ?></td> <td><?php echo $row["published_date"]; ?></td> </tr> <?php }?> </table> <!-- Closing table tag --> </div> <!-- Closing div tag --> </div></body></html> |
Output of this web page
Now open another page in notepad and type the following code. Save this file as export.php
PHP
<?php // Checking data by post methodif(isset($_POST["export"])) { // Connect to our data base $connect = mysqli_connect("localhost", "root", "", "article_neveropendata"); // Accept csv or text files header('Content-Type: text/csv; charset=utf-8'); // Download csv file as neveropendata.csv header('Content-Disposition: attachment; filename=neveropendata.csv'); // Storing data // Placing data using fputcsv fputcsv($output, array('User_ID','Name', 'Article_Name', 'Article_Type', 'Data_published')); // SQL query to fetch data from our table $query = "SELECT * from neveropendata"; // Result $result = mysqli_query($connect, $query); while($row = mysqli_fetch_assoc($result)) { // Fetching all rows of data one by one fputcsv($output, $row); } // Closing tag fclose($output); } ?> |
Output:
EXECUTION VIDEO
