A comma separated value (CSV) is a common format for exporting and importing data from the mysql database and it is a widely supports of specific web and desktop application for exporting a bunch of data and exporting to the other application, and off course same table structure for not returning error for importing.
In this tutorial, we create small a php application can be able to export mysql to csv from database table. A page listed of data from the mysql table, in a list we can choose an individual data to export or if you all data export you can be able check all and export in one click.
You can view the live demo and download the work files, so let’s begin…
Demo
Step 1: Create a Database
First create a database named ‘export_csv’ you can change if you want, and create a table named ‘csv_data’ and fields, and insert some Data.
CREATE TABLE `csv_data` ( `id` int(10) UNSIGNED NOT NULL, `product_title` varchar(255) NOT NULL, `product_price` varchar(255) NOT NULL ); ALTER TABLE `csv_data`ADD PRIMARY KEY (`id`); ALTER TABLE `csv_data` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
Step 2: Create Database Connection
Create file name ‘database.php‘ to connect from the database we have assigning the database credentials, MySQL host, user, password and database name the ‘import_csv’.
<?php
$db = mysqli_connect('localhost', 'root', '', 'axlmulat_demo');
if(!$db) { echo mysqli_connect_error(); }
?>
Step 3: Creating the Page Template
Create a file name ‘index.php’ in the page we have a list of sample products and check boxes can be too able to choose a product to import, and we need to create a jQuery function can be able to check all in one click to import all products.
index.php
<?php require_once("database.php"); ?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name="robots" content="noindex,nofollow"/>
<title>Export Mysql to CSV using Php</title>
<link href="style/style.css" rel="stylesheet" type="text/css" media="all" />
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script type="text/javascript" src="js/script.js"></script>
</head>
<body>
<div id="wrap" align="center"> <!--wrap start-->
<h1>Exporting Data with CSV</h1>
<form action="export_csv.php" method="post" name="data_table">
<table id="table_data">
<tr>
<td>all<input type="checkbox" id="check_all" value=""></td>
<td>id</td>
<td>Product name</td>
<td>Price</td>
</tr>
<?php $query = mysqli_query($db, "SELECT `id`, `product_title`, `product_price` FROM `csv_data`");
while($row = mysqli_fetch_assoc($query)) { ?>
<tr>
<td><input type="checkbox" value="<?php echo $row['id']; ?>" name="data[]" id="data"></td>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['product_title']; ?></td>
<td><?php echo $row['product_price']; ?></td>
</tr>
<?php } unset($row); ?>
</table>
<input name="submit" type="submit" value="Export" id="submit">
</form>
</div> <!--wrap end-->
</body>
</html>
A simple style for the page.
style/style.css
body {
background:url(bg.jpg);
font-family: Arial, Helvetica, sans-serif;
font-size:13px
}
h1 {
color:#000000;
font-family:"Helvetica Neue",Helvetica,Arial,sans-serif;
}
p {
margin:10px;
padding:10px;
color:#000000;
}
table#table_data {
border: 1px solid #CCCCCC;
width: 485px;
}
table#table_data tr:first-child {
font-weight:bold;
text-transform:uppercase
}
table#table_data tr td {
border: 1px solid #CCCCCC;
text-align:center
}
table#table_data tr:first-child {
text-align:center;
border:1px solid #999
}
js/script.js
jQuery(function($) {
$("form input[id='check_all']").click(function() {
var inputs = $("form input[type='checkbox']");
for(var i = 0; i < inputs.length; i++) {
var type = inputs[i].getAttribute("type");
if(type == "checkbox") {
if(this.checked) {
inputs[i].checked = true;
} else {
inputs[i].checked = false;
}
}
}
});
$("form input[id='submit']").click(function() {
var count_checked = $("[name='data[]']:checked").length;
if(count_checked == 0) {
alert("Please select a product(s) to export.");
return false;
}
});
}); // jquery end
In the jQuery script we assign the id attribute to the check box in the header of the table and triggered to click to check all the check boxes, we count and loop the check boxes and get each attribute type, if the attribute type is checkbox it will checked.
Step 4: The Export PHP Script
export_csv.php
<?php
require_once("database.php");
if(isset($_POST['submit'])) {
$id_array = $_POST['data']; // return array
$id_count = count($_POST['data']); // count array
$out = array();
for($j = 0; $j < $id_count; $j++) { // each checked
$id = $id_array[$j];
$query = mysqli_query($db, "SELECT * FROM `csv_data` WHERE `id` = '$id'");
while ($row = mysqli_fetch_assoc($query)) {
$out[] = $row;
}
} // Output to browser with appropriate mime type.
//print_r($out);
header("Content-type: text/x-csv");
header("Content-Disposition: attachment; filename=".time().".csv");
$output = fopen('php://output', 'w');
fputcsv($output, array('id', 'Product name', 'Price'));
if (count($out) > 0) {
foreach ($out as $row) {
fputcsv($output, $row);
}
}
}
?>
In the php export script we connect to mysql database first and define the database name and table name and post submit. We loop first the table fields and con cat to comma and same for the products. In the output we set the file name as time function to generate numbers.
Step 5: Complete
Were done, we created a php web application can be able to export data from mysql to csv. Let’s have a look at what we’ve achieved:
- We’ve set up a database.
- We’ve create page to fetch the data.
- We’ve write the jQuery script for check all function.
- We’ve write the php script for to generated the csv.
If you enjoyed this article, please consider sharing it!
