How To Export MySql To CSV
What Will I Learn?
In this tutorial, I will explain you How To Export MySql To CSV very easily.
Requirements
MySql Server
Difficulty
Intermediate
Tutorial Contents
- Assigning the Mysql database & table
- Creating the connection to the Mysql database-export.php
- Creating 'exportMysqlToCsv' function-exportMysqlToCsv.inc.php
In most of project mostly clients wants to export their database in to CSV / Eecel file. It will help us to make a simple record or in future we can also import the same data in to MySql database.
In this tutorial, I will explain you How To Export MySql To CSV very easily.
Export MySql To CSV
STEP 1:Assigning the Mysql database & table.
- $host: Hostname or an ip adress. it is usualy localhost.
- $db: Mysql user account used to access the database.
- $user: this is the password for the Mysql user account.It is usualy root.
- $pass: this is the name of the Mysql database used.It is usualy blank.
- $table: The name of the table that you want from Mysql to csv.
STEP 2: Creating the connection to the Mysql database-export.php
In this function, I will create a simple MySql database connection using the above assigning variable.Now as you can see we are calling here exportMysqlToCsv($table) function and passing the name of table. In Next step we will create this function.
<?php
$host = 'localhost'; // MYSQL database host adress
$db = ''; // MYSQL database name
$user = ''; // Mysql Datbase user
$pass = ''; // Mysql Datbase password
// Connect to the database
$link = mysql_connect($host, $user, $pass);
mysql_select_db($db);
require 'exportMysqlToCsv.inc.php';
$table=""; // this is the tablename that you want to export to csv from mysql.
exportMysqlToCsv($table);
?>
STEP 3: Creating 'exportMysqlToCsv' function-exportMysqlToCsv.inc.php
Create a php file and named it as 'exportMysqlToCsv.inc.php'.
<?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 <img src="icon_wink.gif" alt=";)" class="wp-smiley">
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;
}
?>
As you can see the code in this function we are passing the table name using $table variable. In this example default filename of the exported file is export.csv. you can change it as you like to append date.(ex.export-15-02-20182.csv)
Posted on Utopian.io - Rewarding Open Source Contributors
Congratulations @bej! You have completed some achievement on Steemit and have been rewarded with new badge(s) :
You got your First payout
Click on any badge to view your own Board of Honor on SteemitBoard.
For more information about SteemitBoard, click here
If you no longer want to receive notifications, reply to this comment with the word
STOP
Thank you for the contribution. It has been approved.
You can contact us on Discord.
[utopian-moderator]
Hey @bej I am @utopian-io. I have just upvoted you!
Achievements
Suggestions
Get Noticed!
Community-Driven Witness!
I am the first and only Steem Community-Driven Witness. Participate on Discord. Lets GROW TOGETHER!
Up-vote this comment to grow my power and help Open Source contributions like this one. Want to chat? Join me on Discord https://discord.gg/Pc8HG9x