Coolpctips

Nearly more than 90% of websites utilize MySQL database for backend storage purpose. Being the most popular database around the work with reliable functionality, MySQL enables the representation of data in any format. Sometimes, we get the need to export MySQL data to the out of website. There can be numerous reasons to do that. Well, in this article, I am sharing you such an exporting technique using which, you can export your MySQL database in form of an excel sheet. Without going much in deep and let you bore, I would like to share the meaningful piece of code below:



Step 1: We need to connect to database first. For this purpose, making a separate file with connection settings would be a better idea, as we can call it on any page whenever we need it. So, the file which we will make is here:



--------------------------X-----------------------



<?php

$host = 'localhost'; // MYSQL database host adress

$db = 'test'; // MYSQL database name

$user = 'root'; // Mysql Datbase user

$pass = ''; // Mysql Datbase password

// Connect to the database

$link = mysql_connect($host, $user, $pass);

mysql_select_db($db);

require 'exportcsv.inc.php';

$table="users"; // this is the tablename that you want to export to csv from mysql.

exportMysqlToCsv($table);

?>



--------------------------X-----------------------



Step 2: Save this file as index.php



Step 3: Now if you have observed carefully, you will find that, line "require 'exportcsv.inc.php' is calling another PHP file. This file contains a function called "exportMysqlToCsv()" which when called, return the CSV/Excel file.



Step 4: Let's create it. Copy the code from below and paste it in you notepad:



--------------------------X-----------------------



<?php

function exportMysqlToCsv($table,$filename = 'export.csv')

{

$csv_terminated = "\n";

$csv_separator = ",";

$csv_enclosed = '"';

$csv_escaped = "\\";

$sql_query = "select * from $table";

// Gets the data from the database

$result = mysql_query($sql_query);

$fields_cnt = mysql_num_fields($result);

$schema_insert = '';

for ($i = 0; $i < $fields_cnt; $i++) { $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, stripslashes(mysql_field_name($result, $i))) . $csv_enclosed; $schema_insert .= $l; $schema_insert .= $csv_separator; } // end for $out = trim(substr($schema_insert, 0, -1)); $out .= $csv_terminated; // Format the data while ($row = mysql_fetch_array($result)) { $schema_insert = ''; for ($j = 0; $j < $fields_cnt; $j++) { if ($row[$j] == '0' || $row[$j] != '') { if ($csv_enclosed == '') { $schema_insert .= $row[$j]; } else { $schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed; } } else { $schema_insert .= ''; } if ($j < $fields_cnt - 1) { $schema_insert .= $csv_separator; } } // end for $out .= $schema_insert; $out .= $csv_terminated; } // end while header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Content-Length: " . strlen($out)); // Output to browser with appropriate mime type, you choose ;) header("Content-type: text/x-csv"); //header("Content-type: text/csv"); //header("Content-type: application/csv"); header("Content-Disposition: attachment; filename=$filename"); echo $out; exit; } ?>



--------------------------X-----------------------



Step 5: Save above code in file named anything like m2c.php.

In above file, following code will downlaod all data of $table table into CSV file.



$sql_query = "select * from $table";



Step 6: If you want only a part of database or only required values to be downloaded in CSV file format, you can alter this line to somewhat like the one given below:



$sql_query = "select * from $table WHERE id='23'";



Like this you can export and download your mysql data in any form in seconds. Hope this would help you.