Solved

Exports Multiple MySQL Tables To CSV File

Posted on 2011-03-22
2
560 Views
Last Modified: 2012-05-11
I've found these queries...

http://www.electrictoolbox.com/mysql-export-data-csv/

http://ariejan.net/2008/11/27/export-csv-directly-from-mysql/

So I'm trying to use left join etc to build a query to export multiple tables.

What I need to state every column from every table with the SELECT rather than using select all * ?

Just I'm having some issues getting it working and want to clear some things up so I'm not on an impossible path.

Thanks for your help
0
Comment
Question by:Ryan Bayne
2 Comments
 
LVL 5

Accepted Solution

by:
tygrus2 earned 125 total points
ID: 35196276
If you join tables then the common index fields will be repeated & renamed for each occurance. Use the tablename.fieldname references to explicitly list the fields to SELECT.

eg.
SELECT Order.OrderID, OrderLine.ProductID, OrderLine.Qty
INTO OUTFILE '/tmp/orderitems.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM Order LEFT JOIN OrderLine ON Order.OrderID=OrderLine.OrderID

Open in new window

Example is a bad choice for LEFT join but you get the idea.
0
 
LVL 2

Author Comment

by:Ryan Bayne
ID: 35197574
Yes so they would that had not entered my mind yet. This is for a Wordpress plugin. So I need to provide the user with a way to select the tables they want to export plus select the columns.

The problem I'm having must be syntax then because I've tried something like what your suggesting. At least I know I was on the right path that is all I wanted really, will just dump and execute some queries in the MySQL gui.

I must remember to check the users input for any duplicate index selections.

Thanks for clearing this up
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question