How can I export into an CSV file a whole database MYSQL?

Question

I have a DB named demo_db with the following tables:

user
  Id
  Name

history
  Id
  userId
  report

start_test
  Id
  userId
  score

final_test
  Id
  userId
  score

How can I export all the tables into a CSV file using INTO OUTFILE in MySQL? So I can have the column name and below the data, i want to be a able to add filters, if there's maybe a way to add each table to a new tab in the same file....or if I can just display all the data in a single line for each user??

Can't I do something like: SELECT user., history., start_test.*, final_test.* INTO OUTFILE FROM user, history, start_test, final_test

I did it with mysqldump but it doesnt present the data as I want.

I'm doing this with PHP.

you can't export all the table data in just one csv file. One table for one file. Or you can build a query and write the result in a file and separate the data with ; — Nagasaki, May 10 '13 at 14:59
Can't I do something like: SELECT user.*, history.*, start_test.*, final_test.* INTO OUTFILE FROM user, history, start_test, final_test — joseagaleanoc, May 10 '13 at 15:11
I don't think INTO OUTFILE will works, you can create a view and dump this view in a csv file. And you need to join your table in your SQL query — Nagasaki, May 10 '13 at 15:17

Erik Nedwidek · Answer 1 · 2013-05-10T15:44:07.867

There isn't a command to do this directly, you'll need to emulate the behavior of mysqldump and output the CSV the way you want it.

$FILE = fopen("output.csv", "w");
mysql_connect($server, $login, $password);
$res = mysql_query("SHOW TABLES FROM $db");
$tables = array();
while($row = mysql_fetch_array($res, MYSQL_NUM)) {
    $tables[] = "$row[0]";
}
foreach($tables as $table) {
    $columns = array();
    $res = mysql_query("SHOW COLUMNS FROM $table");
    while($row = mysql_fetch_array($res, MYSQL_NUM)) {
        $columns[] = "$row[0]";
    }
    fwrite($FILE, implode(",", $columns); fwrite("\n");
    $resTable = mysql_query("SELECT * FROM $table");
    while($row = mysql_fetch_array($resTable, MYSQL_NUM)) {
        fwrite($FILE, implode(",", $row)); fwrite("\n");
    }
}

MYSQL_NUM is used to give you a numeric indexed array.

If you don't want to output to a file, but rather directly download it from a website to your computer, add the following lines to the beginning of the script:

header('Content-Type: text/csv; name="filename.csv"');
header('Content-Disposition: attachment; filename="filename.csv"');

Change the fwrites to echo or print.

Oh, and I added some newlines to the output since I'm sure you wouldn't want one big long line.

"At this point do a "SELECT * FROM $table" and process the data to the file." — joseagaleanoc, May 10 '13 at 15:34
And the question/comment is? Do you need the code on how to do a select query and output it? — Erik Nedwidek, May 10 '13 at 15:37

score 0 · Answer 2 · answered May 10 '13 at 15:02

0

If you are using phpmyadmin

Then Go to Export -> Click on Custom - display all possible options

below you will find checkbox: Put columns names in the first row. Check that to get column names in the first row and below you will get data.

answered May 10 '13 at 15:02

Akaash

171
1
2
12

You can't. phpMyAdmin is a web based admin tool for MySQL that is written in PHP. – Erik Nedwidek May 10 '13 at 15:16
Only thing you can do is if you want to use it in php as an array, then do Format as PHP array and View output as text – Akaash May 10 '13 at 15:19

score 0 · Answer 3 · answered May 10 '13 at 15:20

CREATE VIEW MY_VIEW
AS
SELECT user.*, history.*, start_test.*, final_test.*
FROM
user, history, start_test, final_test
WHERE user.id = history.userId
AND user.id = start.userId
AND user.id = final.userId

Dump the content of the view use export tools in phpmyadmin

score 0 · Answer 4 · answered May 10 '13 at 15:57

0

This snippet taken from http://dev.mysql.com/doc/refman/5.6/en/select-into.html

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

answered May 10 '13 at 15:57

SpacedMonkey

2,725
1
16
17

How can I export into an CSV file a whole database MYSQL?

4 Answers4

Linked