How to current snapshot of MySQL Table and store it into CSV file(after creating it) ?

Question

I have large database table, approximately 5GB, now I wan to getCurrentSnapshot of Database using "Select * from MyTableName", am using PDO in PHP to interact with Database. So preparing a query and then executing it

        // Execute the prepared query
        $result->execute();
        $resultCollection = $result->fetchAll(PDO::FETCH_ASSOC);

is not an efficient way as lots of memory is being user for storing into the associative array data which is approximately, 5GB.

My final goal is to collect data returned by Select query into an CSV file and put CSV file at an FTP Location from where Client can get it.

Other Option I thought was to do:

SELECT * INTO OUTFILE "c:/mydata.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM my_table;

But I am not sure if this would work as I have cron that initiates the complete process and we do not have an csv file, so basically for this approach,

1. PHP Scripts will have to create an CSV file.
2. Do a Select query on the database.
3. Store the select query result into the CSV file.

What would be the best or efficient way to do this kind of task ?

Any Suggestions !!!

Answer 1

You can use the php function fputcsv (see the PHP Manual) to write single lines of csv into a file. In order not to run into the memory problem, instead of fetching the whole result set at once, just select it and then iterate over the result:

$fp = fopen('file.csv', 'w');

$result->execute();
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    // and here you can simply export every row to a file:
    fputcsv($fp, $row);
}
fclose($fp);