使用mysql和php创建多个csv文件

问题描述：

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."
");
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?

答

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
";
    ...
    ...
    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."
");
    fclose($myfile);
}

EDIT : (Thanks to @Kickstart)

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

答

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.

答

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."
");
    fclose($myfile);
}

使用mysql和php创建多个csv文件

相关推荐