Export a mysql table to a local file

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
fastfingertips
Forum Contributor
Posts: 242
Joined: Sun Dec 28, 2003 1:40 am
Contact:

Export a mysql table to a local file

Post 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
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post 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
fastfingertips
Forum Contributor
Posts: 242
Joined: Sun Dec 28, 2003 1:40 am
Contact:

Post 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.
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post 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

?>
fastfingertips
Forum Contributor
Posts: 242
Joined: Sun Dec 28, 2003 1:40 am
Contact:

Post by fastfingertips »

But where i'm sendig the sql instructions?
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post 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...
Post Reply