create multiple csv files using mysql & php while

Question

i'm trying to generate multiple progressive csv file (with name DOC_n°.csv) using mysql & php while.

//query 1
$query2 ="SELECT id_order FROM ps_orders";
$result2= mysql_query($query2); 

$i=1;

while ($riga2 = mysql_fetch_array($result2)){
//query 2
$query = "SELECT ps_order_detail.product_name,ps_orders.id_order,ps_orders.date_add,ps_order_detail.unit_price_tax_incl,ps_order_detail.id_order_detail,ps_order_detail.product_quantity,ps_order_detail.product_reference,ps_order_detail.product_weight,ps_order_detail.unit_price_tax_incl,ps_address.id_customer,ps_address.firstname,ps_address.lastname,ps_address.address1,ps_address.address2,ps_address.postcode,ps_address.city
FROM ps_orders
JOIN ps_order_detail ON  ps_orders.id_order = ps_order_detail.id_order
JOIN ps_address ON ps_orders.id_customer = ps_address.id_customer
WHERE ps_orders.id_order=$i;";

$result = mysql_query($query);  
    //second while to write document and values inside of it
    while ($riga = mysql_fetch_array($result)){
//useful variable to write files
        $path ="TMPIN/";
        $doc=$path."DOC_".$i.".csv";
        $myfile = fopen($doc, "w");
...
...
information inside the csv
...
..          
fwrite($myfile, $testo."\n");
fclose($myfile);
};
$i++;
};

My code works, but in the csv file generated i find all the loops generate before. There is a way to see only the LAST loop generat for cycle?

If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jul 15 '15 at 14:17
The problem is probably within the missing code. `$testo` isn't defined in the given code, and probably needs to be reset at the start of a new loop. — Jessica, Jul 15 '15 at 14:18
Quite a few good answers here. Small point is that your code seems to select all the id_order fields, then get all the records where id_order = $i . This will only work if id_order starts at 1 and has no gaps. — Kickstart, Jul 15 '15 at 14:27

score 0 · Answer 1 · answered Jul 15 '15 at 14:19

Your loop is set up wrong, and the core logic is highly inefficient as well. You should learn how to use JOINs.

In any case, to fix your code

while(... main query loop) {
    $file = fopen(...);
    $subquery = mysql_query(...);
    while($row = mysql_fetch_array($subquery)) {
        fputcsv($file, $subquery);
    }
    fclose($file);
}

Note how the fopen/close are OUTSIDE the inner loop.

Random · Accepted Answer · 2015-07-15T15:42:17.077

0

You have 2 while loop. You change file name only in the master loop, so in the child loop, you always write in the same csv (and massively open/close this file by the way !)

So you should try something like this :

$query = "SELECT ps_order_detail.product_name,ps_orders.id_order,ps_orders.date_add,ps_order_detail.unit_price_tax_incl,ps_order_detail.id_order_detail,ps_order_detail.product_quantity,ps_order_detail.product_reference,ps_order_detail.product_weight,ps_order_detail.unit_price_tax_incl,ps_address.id_customer,ps_address.firstname,ps_address.lastname,ps_address.address1,ps_address.address2,ps_address.postcode,ps_address.city
    FROM ps_orders
    JOIN ps_order_detail ON  ps_orders.id_order = ps_order_detail.id_order
    JOIN ps_address ON ps_orders.id_customer = ps_address.id_customer
    WHERE 1=1;";

$result = mysql_query($query);  
//second while to write document and values inside of it 
while ($riga = mysql_fetch_array($result)){
    $testo = "";
    $testo .= "stuff to write\n";
    ...
    ...
    information inside the csv
    ...
    ...

    // once you know what to write, open file.
    //useful variable to write files
    $path ="TMPIN/";
    $doc=$path."DOC_".$riga['id_order'].".csv";
    $myfile = fopen($doc, "w");
    fwrite($myfile, $testo."\n");
    fclose($myfile);
}

EDIT : (Thanks to @Kickstart)

Deleted the first loop which is useless... You can do 1 only query

edited Jul 15 '15 at 15:42

answered Jul 15 '15 at 14:21

Random

3,158
1
15
25

thank you, but in the last DOC_N.csv file (let's suppose it is 4) it gives me all the previously order, when i want ONLY the order relative at the corrispetive id_order – Pds Ink Jul 15 '15 at 15:53
didn't you forget the `$testo = "";` at the beginning of the loop ? It resets the string for each file... – Random Jul 16 '15 at 07:24
$testo are more variables concatenated inside while loop where i write "information inside the csv", i don't write down that information, because is not necessary for my question – Pds Ink Jul 16 '15 at 08:07
So here is the problem, you must do something wrong while retrieving these data. All you have to do is to reset all variables at the beginning of the loop... if it still doesn't work, you'll have to post the "information inside the csv" so we can fix it with you... – Random Jul 16 '15 at 08:13
in this case any advice on how i reset? i tried this inside the cycle: if (isset($query) && $i!=$id_ordine){ unset($query); } else{ – Pds Ink Jul 16 '15 at 11:02
The query is ok... you don't have to reset it. The matter is what you write in the CSV... at the beginning of the while, you should unset every variable you are using inside. In this code, you only use `$testo`, so you only have to do `$testo = "";`, or `unset($testo);`... – Random Jul 16 '15 at 11:41
You need to reset the $testo variable after you have processed each id_order. Otherwise you have (for example) populated $testo with all the details for id_order 1, and written that to a file. You then keep on adding the details for id_order 2 on to the end of $testo (which still contains the details for id_order 1), and write that to the file for that id_order. This is why my solution checks for a change in id_order and when that happens it outputs the details AND resets $testo. – Kickstart Jul 16 '15 at 12:53
Note, with details of what it being output it might be possible to write a single piece of SQL that returns a row for each id_order, with the details for $testo for that id_order already calculated. – Kickstart Jul 16 '15 at 12:54

Kickstart · Answer 3 · 2015-07-15T15:57:50.763

Eliminating the extra loop, and only outputting the details for each id_order. Just outputs the file when the id_order changes.

<?php

//query 2
$query = "SELECT ps_order_detail.product_name,ps_orders.id_order,ps_orders.date_add,ps_order_detail.unit_price_tax_incl,ps_order_detail.id_order_detail,ps_order_detail.product_quantity,ps_order_detail.product_reference,ps_order_detail.product_weight,ps_order_detail.unit_price_tax_incl,ps_address.id_customer,ps_address.firstname,ps_address.lastname,ps_address.address1,ps_address.address2,ps_address.postcode,ps_address.city
FROM ps_orders
JOIN ps_order_detail ON  ps_orders.id_order = ps_order_detail.id_order
JOIN ps_address ON ps_orders.id_customer = ps_address.id_customer
ORDER BY ps_orders.id_order";

$result = mysql_query($query);  
    //second while to write document and values inside of it
$last_id_order = 0;
while ($riga = mysql_fetch_array($result))
{
    if ($last_id_order != $riga['id_order'] and $last_id_order != 0)
    {
        output_file($testo, $last_id_order);
        $testo = '';
        $last_id_order = $riga['id_order'];
    }
    //useful variable to write files
    ...
    ...
    information inside the csv
    ...
    ..          
}
if ($last_id_order != 0)
{
    output_file($testo, $last_id_order);
}

function output_file($testo, $last_id_order)
{
    //useful variable to write files
    $path = "TMPIN/";
    $doc = $path."DOC_".$last_id_order.".csv";
    $myfile = fopen($doc, "w");
    fwrite($myfile, $testo."\n");
    fclose($myfile);
}

i try your code, it gives me: Parse error: syntax error, unexpected '$testo' (T_VARIABLE) — Pds Ink, Jul 15 '15 at 15:44
Just a couple of missing semi colons. Can't really test it without the extra code, tables, etc, so probably a couple of other typos in there. — Kickstart, Jul 15 '15 at 15:58

create multiple csv files using mysql & php while

3 Answers3