Php to create multiple csv(s) files from one mysql query

Question

UPDATE #1 I have one php file with multiple queries that create multiple csv files. I want to combine the queries.

My mysql table has 5 columns: ID, currency, rate, date, temptime

Create the table table and insert some records

    CREATE TABLE IF NOT EXISTS `table` ( `id` INT NOT NULL AUTO_INCREMENT , `currency` VARCHAR(254) NOT NULL , `rate` FLOAT NOT NULL , `date` VARCHAR(254) NOT NULL , `temptime` DATE NOT NULL , PRIMARY KEY (`id`), UNIQUE `UNIQUE` (`currency`, `temptime`)) ENGINE = MyISAM;

INSERT INTO `table` 
(currency, rate, date, temptime)
VALUES 
('USD', '1.232', '1521212400', '2018-03-16'),
('USD', '1.258', '1521126000', '2018-03-15'),
('JPY', '133.82', '1521212400', '2018-03-16'),
('JPY', '131.88', '1521126000', '2018-03-15'),
('EUR', '1.99', '1521212400', '2018-03-16'),
('EUR', '1.85', '1521126000', '2018-03-15'),
('BRL', '1.6654', '1521212400', '2018-03-16'),
('BRL', '1.5498', '1521126000', '2018-03-15'),
('ZAR', '1.99654', '1521212400', '2018-03-16'),
('ZAR', '2.0198', '1521126000', '2018-03-15'),
('RUB', '19.9654', '1521212400', '2018-03-16'),
('RUB', '20.0198', '1521126000', '2018-03-15');

These are the 2 queries I want to combine for the column "currency" USD and EUR :

    $result = mysql_query("SELECT temptime, rate FROM `table` WHERE currency='USD' ORDER BY date asc"); 
if (!$result) die('Couldn\'t fetch records'); 
$num_fields = mysql_num_fields($result); 
$headers = array(); 
for ($i = 0; $i < $num_fields; $i++) 
{     
       $headers[] = mysql_field_name($result , $i); 
} 
$fp = fopen('csv/USD.csv', 'w'); 


if ($fp && $result) 
{     
       fputcsv($fp, $headers); 
       while ($row = mysql_fetch_row($result)) 
       {
          fputcsv($fp, array_values($row)); 
       }
} 

$result = mysql_query("SELECT temptime, rate FROM `table` WHERE currency='EUR' ORDER BY date asc"); 
if (!$result) die('Couldn\'t fetch records'); 
$num_fields = mysql_num_fields($result); 
$headers = array(); 
for ($i = 0; $i < $num_fields; $i++) 
{     
       $headers[] = mysql_field_name($result , $i); 
} 
$fp = fopen('csv/EUR.csv', 'w'); 


if ($fp && $result) 
{     
       fputcsv($fp, $headers); 
       while ($row = mysql_fetch_row($result)) 
       {
          fputcsv($fp, array_values($row)); 
       }
}

Thank you

Warning: Do note use mysql_ functions. They are deprecated since PHP 5.5 and entirely removed in PHP 7. — Parfait
– Parfait, Commented Mar 17, 2018 at 16:34

Nigel Ren · Accepted Answer · 2018-03-17 16:01:46Z

1

You can use the currency field in each row to decide which file to write to, I've used an array of file handles so rather than having any conditions around which file, you just use the currency as the index to the file handle...

// Create an array of files, each associated with currency
$fp = array( 'EUR' => fopen('csv/EUR.csv', 'w'),
        'USD' => fopen('csv/USD.csv', 'w'));

$result = mysql_query("SELECT currency, temptime, rate FROM `table` ORDER BY date asc");
if (!$result) die("Couldn't fetch records");
$num_fields = mysql_num_fields($result);
$headers = array();
for ($i = 0; $i < $num_fields; $i++)
{
    $headers[] = mysql_field_name($result , $i);
}
// Write out headers to each file.
foreach ( $fp as $output )  {
    fputcsv($output, $headers);
}

while ($row = mysql_fetch_row($result))     {
    // Use first field to decide which file to write to.
    $currency = array_shift($row);
    fputcsv($fp[$currency], $row);
}
// Close all of the files.
foreach ( $fp as $output )  {
    fclose($output);
}

As I don't have your database, I can't test this, but if you have any problems, then let me know.

edited Mar 17, 2018 at 16:01

answered Mar 17, 2018 at 11:38

Nigel Ren

57.3k11 gold badges49 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Julius Barra Over a year ago

Thanks, it doesn't work, maybe there is something missing in WHERE... This the table: CREATE TABLE IF NOT EXISTS table ( id INT NOT NULL AUTO_INCREMENT , currency VARCHAR(254) NOT NULL , rate FLOAT NOT NULL , date VARCHAR(254) NOT NULL , temptime DATE NOT NULL , PRIMARY KEY (id), UNIQUE UNIQUE (currency, temptime)) ENGINE = MyISAM;

Nigel Ren Over a year ago

Sorry - I'd taken the condition out and left the WHERE in, removed it in the code,can you try again.

Julius Barra Over a year ago

Hi, it writes the csv(s), but gives this warning: Warning: fclose() expects parameter 1 to be resource, array given in /home/curr/public_html/1.php on line 41 Line 41: ` fclose($fp);`

Julius Barra Over a year ago

I have updated the question with some lines to create the table and insert some records.

Nigel Ren Over a year ago

Again - appologies, should be fclose($output);

|

Parfait · Accepted Answer · 2018-03-17 17:04:26Z

Consider also using no loops and exporting to CSV directly from MySQL with its SELECT ... INTO OUTFILE (the complement of LOAD DATA INFILE).

Specifically, execute both action queries separately below in PHP (be sure to escape quotes). While not combined, this process should be a faster read/write IO process than multiple loops. Plus, you can swap the app layer (i.e., use Python, Java) without changing csv export code!

SELECT temptime, rate 
INTO OUTFILE '/path/to/csv/USD.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM `table` 
WHERE currency='USD' 
ORDER BY `date` asc;

SELECT temptime, rate 
INTO OUTFILE '/path/to/csv/EUR.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM `table` 
WHERE currency='EUR' 
ORDER BY `date` asc;

And for one call, placed both in MySQL stored procedure:

DELIMITER $$
DROP PROCEDURE IF EXISTS export_csv$$

CREATE PROCEDURE export_csv
BEGIN
    -- SELECT ... INTO OUTFILE COMMANDS
END$$

DELIMITER ;

Then in PHP run it with a one-liner (using mysqli API):

$result = mysqli_query($conn, "CALL export_csv")

Do note: headers are not passed with above commands. To resolve adjust query with a union requiring type conversion. And CSVs do not store types or unicodes metadata to have issue here.

SELECT 'temptime' as col1, 'rate' as col2
UNION ALL
SELECT CAST(temptime, CHAR(10) as col1, 
       CAST(ROUND(rate,4) AS CHAR(3)) as col2
INTO OUTFILE '/path/to/csv/USD.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM `table` 
WHERE currency='USD' ;

Collectives™ on Stack Overflow

Php to create multiple csv(s) files from one mysql query

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related