Search This Blog

Saturday, October 19, 2013

Exporting Data with CSV

A comma separated value (CSV) is a common format for exporting and importing data from the 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 the data from database table to cvs. A page listed of data from the database table, in a list we can choose a individual data to export or if you all data export you can be able check all and export in one click. 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.
1CREATE TABLE IF NOT EXISTS `csv_data` (
2  `id` int(11) NOT NULL AUTO_INCREMENT,
3  `product_title` varchar(255) NOT NULL,
4  `product_price` varchar(255) NOT NULL,
5  PRIMARY KEY (`id`)
6);


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’.
1<?php
2$db = mysql_connect('localhost', 'root', '');
3    if(!$db) { echo mysql_error(); }
4$select_db = mysql_select_db('export_csv'); // db name
5    if(!$select_db) { echo mysql_error(); }
6?>


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 to 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
1<?php require_once("database.php"); ?>
2<!DOCTYPE html>
3<html>
4<head>
5<meta charset="utf-8">
6<title>Exporting Data with CSV | istockphp.com</title>
7<link href="style/style.css" rel="stylesheet" type="text/css" media="all" />
8<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"> </script>
9<script type="text/javascript" src="js/script.js"></script>
10</head>
11 
12<body>
13    <div id="wrap" align="center"> <!--wrap start-->
14        <h1>Exporting Data with CSV</h1>
15         <form action="export_csv.php" method="post" name="data_table">
16        <table id="table_data">
17 
18            <tr>
19                <td>all<input type="checkbox" id="check_all" value=""></td>
20                <td>id</td>
21                <td>Product name</td>
22                <td>Price</td>
23 
24            </tr>
25            <?php $query = mysql_query("SELECT `id`, `product_title`, `product_price` FROM `csv_data`");
26                while($row = mysql_fetch_array($query)) {
27            ?>
28            <tr>
29                <td><input type="checkbox" value="<?php echo $row['id']; ?>" name="data[]" id="data"></td>
30                <td><?php echo $row['id']; ?></td>
31                <td><?php echo $row['product_title']; ?></td>
32                <td><?php echo $row['product_price']; ?></td>
33            </tr>
34            <?php } unset($row); ?>
35 
36        </table>
37        <br />
38        <input name="submit" type="submit" value="Export" id="submit">
39       </form>
40     </div> <!--wrap end-->
41</body>
42</html>
A simple style for the page.
style.css
1body {
2    background:url(bg.jpg);
3    font-family: Arial, Helvetica, sans-serif;
4    font-size:13px
5}
6h1 {
7    color:#000000;
8    font-family:"Helvetica Neue",Helvetica,Arial,sans-serif;
9}
10p {
11    margin:10px;
12    padding:10px;
13    color:#000000;
14}
15table#table_data {
16    border: 1px solid #CCCCCC;
17    width: 485px;
18}
19table#table_data tr:first-child {
20    font-weight:bold;
21    text-transform:uppercase
22}
23table#table_data tr td {
24    border: 1px solid #CCCCCC;
25    text-align:center
26}
27table#table_data tr:first-child {
28    text-align:center;
29    border:1px solid #999
30}
script.js
1jQuery(function($) {
2        $("form input[id='check_all']").click(function() {
3 
4            var inputs = $("form input[type='checkbox']");
5 
6            for(var i = 0; i < inputs.length; i++) {
7                var type = inputs[i].getAttribute("type");
8                    if(type == "checkbox") {
9                        if(this.checked) {
10                            inputs[i].checked = true;
11                        } else {
12                            inputs[i].checked = false;
13                         }
14                    }
15            }
16        });
17 
18        $("form input[id='submit']").click(function() {
19 
20            var count_checked = $("[name='data[]']:checked").length;
21            if(count_checked == 0) {
22                alert("Please select a product(s) to export.");
23                return false;
24            }
25 
26        });
27}); // 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
1<?php
2require_once("database.php");
3 
4define("DB_NAME", ""); // db name
5define("TABLE_NAME", "csv_data"); // table
6 
7if(isset($_POST['submit'])) {
8 
9    $id_array =       $_POST['data']; // return array
10    $id_count = count($_POST['data']); // count array
11 
12    $out = '';
13    $field_name  = mysql_list_fields( DB_NAME, TABLE_NAME );
14    $count_field = mysql_num_fields($field_name); // count the table field
15 
16    for($i = 0; $i < $count_field; $i++) { // name of all fields
17        $l= mysql_field_name($field_name, $i);
18             $out .= $l . ', '; // echo table fileds,
19    }
20 
21    $out .= "\n"; // echo new line
22 
23    for($j = 0; $j < $id_count; $j++) { // each checked
24 
25        $id = $id_array[$j];
26        $query = mysql_query("SELECT * FROM `csv_data` WHERE `id` = '$id'");
27 
28        while ($row = mysql_fetch_array($query)) {
29            for($i = 0; $i < $count_field; $i++) {
30                $out .= $row["$i"] . ', '; // echo data,
31            }
32            $out .= "\n";  // echo new line per data
33        }
34 
35    }
36 
37    // Output to browser with appropriate mime type.
38    header("Content-type: text/x-csv");
39    header("Content-Disposition: attachment; filename=".time().".csv");
40    echo $out; // output
41    exit;
42}
43?>
In the php export script we require the database connection first and the 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 the data 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!
Posted by at
Labels:
Location: Rajkot, Gujarat, India

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)