Exporting MySQL tables to csv locally

Question

I want to export two tables from my mysql server to a csv file on the webserver.

Both the mysql and webserver are running on the same machine, and will NOT be web-facing. It's merely a small webapp to automate a task I perform on a regular basis, and so security is not an issue.

The way I tried, which would be absolutely ideal, but did not work was:

$exportArticle = 'SELECT *
INTO OUTFILE "article_line.csv"
FIELDS TERMINATED BY ","
ENCLOSED BY "\""
ESCAPED BY "\\"
LINES TERMINATED BY "\\n\\r"
FROM article_line';

and then calling it in my script with:

mysql_query($exportArticle);

But unfortunately this doesn't write anything (it doesn't error out, either, though.)

If someone could help me with this, that would be great! Thanks

EDIT: Just to clarify, I have spent the last 2 and a half hours browsing both google and stackoverflow looking for solutions for this, and have tried so many different ways it's not funny. It's just ending up a muddle in my head that I can't understand, so apologies if anyone believes the answer is already here - it hasn't worked for me so far.

Possible duplicate http://stackoverflow.com/questions/4249432/export-to-csv-via-php — RelevantUsername, May 10 '13 at 14:44
You need to make sure that you have write access to the location where are you trying to write the file. — Sumoanand, May 10 '13 at 14:47
I made the same once....and just like you i thought that doesnt work, but it works....try to copy and paste the query in your mysql client or in phpmyadmin and run it, just to see if trigger some error — Hackerman, May 10 '13 at 14:48
@RelevantUsername - I've already checked that link, and it's either not working for me, or I'm just too novice to get it to work. — Brodie Messenger, May 10 '13 at 14:49
@Sumoanand - It definitely has write access, as other commands in the same script have successfully written to the same location. — Brodie Messenger, May 10 '13 at 14:49
@BrodieMessenger You might be interested to know that if the Database and PHP are on the same server, you could do something like a direct CSV export from a single mysql command, more informations here : http://ariejan.net/2008/11/27/export-csv-directly-from-mysql/ — RelevantUsername, May 10 '13 at 14:51
@RobertRozas- Thanks for the suggestion Robert; Unfortunately I've already done this, and running it directly on the database works fine, that's why I'm stumped as to why it doesn't when executing via my php script. — Brodie Messenger, May 10 '13 at 14:54
@RelevantUsername- Thanks for the suggestion. I have actually already seen this article, and this is pretty much exactly what my script is already doing(just not successfully). — Brodie Messenger, May 10 '13 at 14:55

score 0 · Accepted Answer · answered May 10 '13 at 15:12

0

change your code to

mysql_query($exportArticle) or die(mysql_error());

This will give you an idea if there is any error when executing the query. If you don't do error handling on mysql_query it will continue to the next statement even if there is an error.

answered May 10 '13 at 15:12

DevZer0

13,433
7
27
51

You, sir, are a genius. I didn't even consider that - my mind isn't operating well on a Friday night after hours of coding. I worked it out thanks to your solution - the formatting of my FROM was missing backticks. – Brodie Messenger May 10 '13 at 15:20

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

0

you can just download MysqlWorkbench , then do

SELECT * FROM article_line

you will se then abutton like this enter image description here

just click on it and you will have the csv export option in there

answered May 10 '13 at 15:18

Dima

8,586
4
28
57

Thanks for the suggestion - this is how I was doing it originally, except through SQLYog. The reason I wanted to script it was because A. it saves a step, but B. to make it user-friendly so that non-technical persons can use it. Thanks for the suggestion, though! – Brodie Messenger May 10 '13 at 15:25

score 0 · Answer 3 · answered May 11 '13 at 15:28

I do not know if you managed to do it but I think that this command has a problem with php. Maybe the access to that file is denied. If you want to create the resulting file on some client host other than the server host, you cannot use SELECT ... INTO OUTFILE.

http://dev.mysql.com/doc/refman/5.0/en/select.html

You can use instead something like this.

function sqlQueryToCSV($filename, $query) {
        $result = mysql_query($query);
        $num_fields = mysql_num_fields($result);
        $headers = array();
        for ($i = 0; $i < $num_fields; $i++) {
                $headers[] = mysql_field_name($result , $i);
        }
        $fp = fopen(mysql_real_escape_string(getcwd().'\\'.$filename), 'w');
        if ($fp) {  fputcsv($fp, $headers);  while ($row = mysql_fetch_array($result)) {
         $arrayValues = array();
         foreach ($headers as $header)
         {
             $arrayValues[] = $row[$header];
         }
         fputcsv($fp, $arrayValues);  }
        }
        fclose($fp); }

Source: http://forums.exchangecore.com/topic/907-function-to-convert-mysql-query-to-csv-file-with-php/

Exporting MySQL tables to csv locally

3 Answers3