mysqldump already does this, no point in writing a script

Indeed it does and you can even add a WHERE condition so you have some control over which rows get dumped, but it always dumps all fields in your table. Also you cannot join tables or databases in your query when using mysqldump.

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

Here you go, just use DBI; while making sure you have DBD::mysql installed.

You can connect to any host that has mysql installed (if you're allowed to access it from outside)

use DBI;
# connect to localhost or remote host by specifying its ip address or 
+hostname
my $HOST = 'x.x.x.x';
# port , usually 3306 
my $PORT = 3306;
# database name 
my $DB = 'somedb';
my $dsn = "DBI:mysql:database=$DB;host=$HOST;port=$PORT";
[download]

You can try looking through various wings of the Monastery where people post code, to see what's sitting around: an example Super Search query

That. combined with the info in previous replies (esp. the part about constructing the parameters for the DBI->connect() call), should get you to the point where you can try something out. If you have trouble, post what you've tried (but don't expose actual connection data, of course, or else your database is likely to be raided/abused).

Make the query from the local host, not from a session on the remote host

Please excuse my previous inept attempt at explaining what I want.

I am able to log on to the remote database and I can do anything I want on that database so I dont need any help in the logging on process.

When I logged on to the remote mysql server I tried using the mysql statement "into outfile '/tmp/Sep' fields terminated by ','" but that only saves the csv file on the remote host not the local host.

The solution is to use perl to take the mysql query that I get from the remote mysql server and put that data into a csv file on the local mysql client.

In short I require a perl script that takes the result of a mysql query and puts it into a csv file WITHOUT using the mysql statement "into outfile".

I have checked the monastry wings without any luck I guess because the mysql statement "into outfile" is used 99% of the time there is no need for such a script but it cannot be used in this situation.

Mysqldump is not suitable, I don't want to import the whole database everytime I want to make a simple query on it

As someone has already mentioned, you don't need a Perl script (I know; it's pure blasphemy to say that here, but...) The thing you're looking for is the 'mysqldump' command line syntax needed to do this. Here's an example:

mysqldump -h [remote_host] -u [user] -p [database] > out.sql
[download]

This will connect to the remote_host with the specified user name, and dump the database after you provide the password (you'll be prompted for one because of the '-p' option.) If you run this command on your local computer, you'll end up with a file called 'out.sql' containing all the mysql commands necessary to recreate that database.

Even with your changed requirements, Perl is still neither necessary nor the easiest solution; once you've designed your query, 'echo $query|mysql -h host -u user -p database > Sep.csv' on the local machine will do what you're asking for. If you want to do it in Perl, it'll be at least somewhat more complex - and given that (if I recall correctly from your original request) you're not very familiar with Perl, it's not a solution that I'd suggest, since you wouldn't find it very maintainable.

use strict;
use warnings;
use DBI;
use DBD::mysql;

my $DB='database';
my $HOST='remote_host_ip_address';
my $user='user_name';
my $pass='password';
my $table='table_to_be_dumped';

my $file_to_be_written_to="c:/temp/output.txt";

# Connect to database.
my $dbh=DBI->connect( "DBI:mysql:database=$DB;host=$HOST;", $user, $pa
+ss) or die "$!\n";
my $sth=$dbh->prepare("SELECT * FROM $table") or die "$!\n";
$sth->execute();
open(FH, ">$file_to_be_written_to") or die "$!\n";
while (my @row=$sth->fetchrow_array()) {
    print FH join(",", @row)."\n";
}
close FH;
$sth->finish();
$dbh->disconnect();
[download]

Thanks so much I will be able to work it out from there I thought it would be much more complicated. Thanks again