Export data to CSV from MySQL


Many times I get request from client for data dump. I use mysql database, and in mysql it is quite
easy to create CSV files directly from MySQL with just one query!

Let's say you want to export the email and name fields from your member table to a CSV file. Here is your code:

SELECT email, name INTO OUTFILE '/tmp/data_dump_member.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM member
Note : Make sure your MySQL server has write permissions to the location where you want to store the results file.

You can either download the csv file from server's '/tmp' directory, or you can move this to http root directory and send link to client to download.
cd /tmp/
mv data_dump_member.csv /var/www/html/
gzip -9 data_dump_member.csv

Comments

Post a Comment

Popular posts from this blog

Detecting user's screen size and resolution

If you're developing a site, you should carefully consider how your web pages appear on different kinds of screens. Today people access website on devices with different types of screens, you should account for some factors that affect the way your web pages appear on these devices. Javascript one simple way is to identify the screen width with the help of javascript and then render the webpage accordingly. Following is sample code to identify screen width and set it in cookie. <?php if(isset($_COOKIE["device_resolution"])) $screen_res = $_COOKIE["device_resolution"]; else //cookie is not found set it using Javascript { ?> <? } echo "Your screen resolution is set at ". $screen_res; ?> PHP With php, it is very easy to identify device using $_SERVER['HTTP_USER_AGENT']. Usining strpos() function find position of first occurrence of a string. <?php $iphone = strpos($_SERVER['HTTP_USER_AGENT'],"iPho...
Read more

autoload class

The __autoload() magic function is used to dynamically load classes. Whenever PHP encounters a non-existent class, it will first call the __autoload() function, and only then declare an error. This can be used to load classes on-the-fly. error_reporting (E_ALL); if (version_compare(phpversion(), '5.1.0', '<') == true) { die ('PHP5.1 Only'); } // Constants: define ('DIRSEP', DIRECTORY_SEPARATOR); // Get site path $site_path = realpath(dirname(__FILE__) . DIRSEP . '..' . DIRSEP) . DIRSEP; define ('site_path', $site_path); $registry = new Registry; // Set some data $registry->set ('name', 'Don'); // Get data, using get() echo $registry->get ('name'); // Get data, using array access echo $registry['name'] function __autoload($className) { // Assume that all class files are located in the same dir and subdirs $fname = str_replace('::', DIRECTORY_SEPARATOR, $className) . '.php...
Read more

MySql Slow Queries

MySQL has a slow query log which can be enabled if you want to identify the queries that are causing problems for a website or application. Checking slow queries is one of the most important steps in optimizing and tuning mysql. This article shows how to identify those 'slow queries' that need special attention and proper optimization. First let’s check on the server if slow query logging is enabled on MySql. On Windows cd c:\xampp\mysql\bin bin>mysqladmin var | find "log_slow_queries" On linux bin>mysqladmin var |grep log_slow_queries Enable the slow query log MySQL prior to 5.1.0 requires a change to the MySQL my.cnf file and a restart in order to log slow queries. From MySQL 5.1.0 you can apparantly change this dynamically without having to restart. Too make the change permanent, you could add the following lines under the [mysqld] section of your my.ini or my.cnf configuration file: >vi /etc/my.cnf [mysqld] # Activate the Slow Q...
Read more