Page 1 of 1

Export a mysql table to a local file

Posted: Tue Jan 20, 2004 5:06 am
by fastfingertips
How can i export a mysql table located on a webserver into a local folder?
I made something like

select user,"The company" as memo into outfile 'data.txt'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
from member
inner join member on balance.memberid=member.memberid where balance.status=1

I would like to know if it will appear to an user that save/open screen

Posted: Tue Jan 20, 2004 8:43 am
by Stoker
if you have network access to the mysqlserver just run a local mysqldump client with the -h option (and perhaps --compress to speed up the transfer)

mysqldump -u username --password=mypass -h my.server.address --compress --opt databasename

Posted: Tue Jan 20, 2004 10:55 am
by fastfingertips
I want to start the export procedure from the browser, something like phpmyadmin exports, but unfortunatelly my php skills are not so good so i could not understant what was there.

Is not important for me to save the files compressed because i will export max 20 rows.

Posted: Tue Jan 20, 2004 11:31 am
by Stoker
something like this perhaps

Code: Select all

<?php

    // Make the browser save as a file
    header('Content-type: text/plain');
    header('Content-Disposition: attachment; filename=datadump-'.date('Ymd').'.sql');

   // Change these
    $db = 'database';        
    $user = 'username';
    $pass = 'password';

    $mysqldump = '/usr/bin/mysqldump'  // This may differ, some systems may use /usr/local/mysql/bin/mysqldump

    passthru($mysqldump.' -u '.escapeshellarg($user)
        .' --password='.escapeshellarg($pass)
        .' --opt '.escapeshellarg($db)
     );

    die();  // prevent any additional output

?>

Posted: Tue Jan 20, 2004 2:14 pm
by fastfingertips
But where i'm sendig the sql instructions?

Posted: Tue Jan 20, 2004 3:30 pm
by Stoker
hmm I cant think of an easy (lazy) way to do that, I would just do the query in PHP and step thru the result lines and output them...