PHPMyAdmin Custom Columns Export

vicnumb Mar 18, 2013

Mar 18 2013 Published by under PHP & MySQL

How to export one column of the same table, multiple columns (but not all) of the same table, multiple combined columns from different tables, just some data of mysql search result.

 

Phpmyadmin with its graphical interface is the best tool to export preselected mysql data.

 

1.1. Let’s start with random table: `testtable`

No real data are used for test purposes.

phpmyadmin mysql table

Our goal is creating and exporting to CSV file 10 records of url field.

 

1.2. Run SQL query to select custom data from `testtable`

mysql query

 

1.3. Custom table to export

After running sql select query we got a custom mysql table. Find under the table the link Export and click it.

phpmyadmin custom mysql table

 

1.4. Export custom mysql data

phpmyadmin export custom table

Same way can be exported any SQL queries over your tables by joining data from one or multiple tables.

 

MySQL has a incorporated method of exporting data to csv file

 

2.1. Export selected data from mysql table to csv file

SELECT url
INTO OUTFILE '/tmp/mysqlexport.csv'
FROM testlist limit 10

or, use more sophisticated export, by defining fields terminate, fields enclose, fields escape

SELECT url
INTO OUTFILE '/tmp/mysqlexport.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM testlist limit 10

 

Make sure you have full rights to read/write into export folder, otherwise you will get a error like this:

#1 - Can't create/write to file '/tmp/mysqlexport.csv' (Errcode: 13)

 

Mysql dump export won’t overwrite the file that already exists. e.g. if you earlier exported a file to /tmp/mysqlexport.csv, then try to export it again, you will get a error like:

#1086 - File '/tmp/products.csv' already exists

 

Feel free to share your ways of exporting mysql tables created on fly

No responses yet

Leave a Reply