Creating Export CSV using PHP – Simple Script

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, so let’s begin…

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 IF NOT EXISTS `csv_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_title` varchar(255) NOT NULL,
  `product_price` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

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 = mysql_connect('localhost', 'root', ''); 	
if(!$db) { 
	echo mysql_error(); 
} 
$select_db = mysql_select_db('export_csv'); // db name 	
if(!$select_db) { 
	echo mysql_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>
<head>
<meta charset="utf-8">
<title>Demo</title>
<link href="style/style.css" rel="stylesheet" type="text/css" media="all" />
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/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 = mysql_query("SELECT `id`, `product_title`, `product_price` FROM `csv_data`");                 
        while($row = mysql_fetch_array($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.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
}

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

require_once("database.php");

define("DB_NAME", ""); // db name
define("TABLE_NAME", "csv_data"); // table

if(isset($_POST['submit'])) {

	$id_array = 	  $_POST['data']; // return array
	$id_count = count($_POST['data']); // count array

	$out = '';
	$field_name  = mysql_list_fields( DB_NAME, TABLE_NAME );
	$count_field = mysql_num_fields($field_name); // count the table field

	for($i = 0; $i < $count_field; $i++) { // name of all fields
		$l= mysql_field_name($field_name, $i);
			 $out .= $l . ', '; // echo table fileds,
	}

	$out .= "\n"; // echo new line

	for($j = 0; $j < $id_count; $j++) { // each checked

		$id = $id_array[$j];
		$query = mysql_query("SELECT * FROM `csv_data` WHERE `id` = '$id'");

		while ($row = mysql_fetch_array($query)) {
			for($i = 0; $i < $count_field; $i++) { 				
					$out .= $row["$i"] . ', '; // echo data, 			
			} 			
			$out .= "\n";  // echo new line per data 		
		} 	
	} 	// Output to browser with appropriate mime type. 	
	header("Content-type: text/x-csv"); 	
	header("Content-Disposition: attachment; filename=".time().".csv"); 	
	echo $out; // output 	exit; 
} 
?>

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.

If you enjoyed this article, please consider sharing it!




Leave a Reply

Your email address will not be published. Required fields are marked *