Export MySQL Data to Excel in PHP Using Ajax, for doing this task we have to follow 4+ steps where we create a table to show the data and then export that visible MySQL data to an excel sheet.
Here are the 4+ steps to Export MySQL Data to Excel in PHP Using Ajax,
- Create a database connection file.
- Get the complete data using PHP MySQL fetch query.
- Including Ajax CDN link.
- Create an Export PHP file which we call using Ajax.
- Create our main JS file to hit the download.
- Download complete source code of export to excel using jquery ajax call in PHP.
4 Major files to check the Export in PHP,
- config.php
- index.php
- export.php
- main.js
Create a Database Connection
We create a MySQL DB connection file where we use mysql_connect() to connect the database.
config.php
|
1 2 3 4 5 6 7 |
<?php $con = mysqli_connect('localhost','root','IF_PASSWORD','exportmysql'); if(!$con){ echo "Something went wrong."; } ?> |
Get complete Data from DB
After successfully connected to the database we take the user’s data on our web view using MySQL select query and run that query using PHP mysqli_query() function.
|
1 2 3 4 5 6 7 8 |
<?php //include DB connection file include 'config.php'; //Get all data from the table $sql = "SELECT * FROM `developers`"; $qry = mysqli_query($con, $sql); ?> |
Include Ajax CDN link and Show Data
Here we create our main view to show the data which we get from the select query. And that complete data we export on Excel.
index.php
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
<?php include 'config.php'; $sql = "SELECT * FROM `developers`"; $qry = mysqli_query($con, $sql); ?> <!DOCTYPE html> <html> <head> <title></title> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script> </head> <body> <table border="1"> <tr> <td>Id</td> <td>Name</td> <td>Mobile</td> <td>Address</td> <td>Designation</td> </tr> <tr> <?php while($row=mysqli_fetch_assoc($qry)){ ?> <td><?php echo $row['id']; ?></td> <td><?php echo $row['name']; ?></td> <td><?php echo $row['skills']; ?></td> <td><?php echo $row['address']; ?></td> <td><?php echo $row['designation']; ?></td> </tr> <?php } ?> </table> <br/> <button id="button" value="csv">Export</button> <a href="javascript:void(0)" id="dlbtn" style="display: none;"> <button type="button" id="mine">Export</button> </a> <script type="text/javascript" src="main.js"></script> </body> </html> |
Here is a trick to doing it completely, without a refresh or reload the page,
|
1 2 3 |
<a href="javascript:void(0)" id="dlbtn" style="display: none;"> <button type="button" id="mine">Export</button> </a> |
On the above code snippet, we create a link on the href attribute, and then we hit using our ajax file (main.js) to download the file without open new tab and without refresh the page.
Create an Export PHP file
export.php
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<?php include 'config.php'; if (isset($_POST['csv'])) { header('Content-Type: text/csv; charset=utf-8'); header('Content-Disposition: attachment; filename=DevelopersData.csv'); $output = fopen("php://output", "w"); fputcsv($output, array('Id','Name','Skills','Address', 'Designation')); $sql = "SELECT * FROM `developers`"; $qry = mysqli_query($con, $sql); while($row= mysqli_fetch_assoc($qry)) { fputcsv($output, $row); } fclose($output); } ?> |
Code Explanations:
- First, we include the DB connection file.
- Check the button is clicked or not using
isset()PHP function. - Then we set the mandatory headers for the create Excel supported files.
|
1 2 3 |
header('Content-Type: text/csv; charset=utf-8'); header('Content-Disposition: attachment; filename=DevelopersData.csv'); $output = fopen("php://output", "w"); |
- Now we setting the header of CSV file.
|
1 |
fputcsv($output, array('Id','Name','Skills','Address', 'Designation')); |
- Now we take the complete data using SQL query and put that into the file using
fputcsv($output, $row);function.
Create Ajax File to Call Export Function
Here we create the main JS file which we use to call the export.php file where we write the export or download file code.
main.js
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
$("#button").click(function(){ var csv = "csv"; $.ajax({ type: 'POST', url: 'export.php', data: {csv:csv}, success: function(result) { console.log(result); setTimeout(function() { var dlbtn = document.getElementById("dlbtn"); var file = new Blob([result], {type: 'text/csv'}); dlbtn.href = URL.createObjectURL(file); dlbtn.download = 'myfile.csv'; $( "#mine").click(); }, 2000); } }); }); |
Code Explanations:
- First, we statically assign the value to a variable (you can create it dynamic by getting the button value using ID). We send it to the PHP
isset()function to check button is clicked or not. - Then we get the result on success response.
- On HTML we discuss the URL trick, for that, we take
<a>tag by ID and createhreflink using JS BLOB.- If you want to know more about JS blob, https://developer.mozilla.org/en-US/docs/Web/API/Blob
- And auto click on that button which we create inside that <a> tag to hit the download link.
- Complete process on setTimout() function because link create may take some second to appear.
Here is the complete source code and its explanations to create Export MySQL Data to Excel in PHP Using Ajax, you check the demo on the below video.
Download Complete Source Code of Export MySQL Data to Excel in PHP Using Ajax
Also Check:
- How to Get HTML Tag Value in PHP
- How to Keep Value After Page Reload in PHP
- Count Number of Visits Using PHP Cookies
- Auto Add Country Code in Input using JavaScript
- jQuery Contact Form Send Email Using Ajax
Happy Coding..!
[…] Export MySQL Data to Excel in PHP Using Ajax […]
[…] Export MySQL Data to Excel in PHP Using Ajax […]
[…] Export MySQL Data to Excel in PHP Using Ajax […]
[…] Also Read: Export MySQL Data to Excel in PHP Using Ajax […]
[…] Also Read: Export MySQL Data to Excel in PHP Using Ajax […]