mysql to associate arrays to csv file

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
alex_gmail
Forum Newbie
Posts: 4
Joined: Mon May 25, 2009 5:41 pm

mysql to associate arrays to csv file

Post by alex_gmail »

Hi:

This is what I am trying to do..

I got a mysql query that give me the data in the following format:

name|qty|week
abc|60|1
abc|20|2
abc|80|3
abc|22|4
bfg|23|1
bfg|45|2
bfg|633|3
bfg|90|4
jhg|34|1
jhg|23|2
jhg|20|3
jhg|2|4

--- Given the above result from the query how can I generate a CSV file of the following format?


name|week_1|week_2|week_3|week_4
abc|60|20|80|22
bfg|23|45|633|90
jhg|34|23|20|2

I have tried to use the following code to generate an associative array but I am not sure how to print it to generate the CSV file:

while ($row=mysql_fetch_array($result))
{
$name=$row["name"];
$qty = $row['qty'];
$week = $row["week"];
$row=array($name => array($week=>$qty));
}


Can someone please help me a little bit on how to use the $row associative array to generate the CSV file?

Thanks
Alex B
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: mysql to associate arrays to csv file

Post by requinix »

There's no need for an associative array.

Code: Select all

$query = "SELECT..."; // make sure it's sorted by name!
$handle = fopen("output file.csv", "w");
 
$previous = ""; // previous name from the query
$array = array(); // array with the name and week quantities
while ($line = mysql_fetch_assoc($query)) {
    $current = $line["name"]; // current name
    if ($current != $previous) { // if it changed
        if ($array) fputcsv($handle, $array); // write the array
        $array = array($current, 0, 0, 0, 0); // initialize a new array
    }
    $array[$line["week"]] = $line["qty"]; // set the quantity
    $previous = $current; // set the previous name
}
if ($array) fputcsv($handle, $array); // last one from the query wasn't written
 
fclose($handle);
alex_gmail
Forum Newbie
Posts: 4
Joined: Mon May 25, 2009 5:41 pm

Re: mysql to associate arrays to csv file

Post by alex_gmail »

Thank you very much! This worked like a charm. However the requirement has changed a bit:

name|qty|week
abc|60|1
abc|20|2
abc|80|3
abc|22|4
bfg|23|1
bfg|45|2
bfg|633|3
bfg|90|4
jhg|34|1
jhg|23|2
jhg|20|3
jhg|2|4


I need to generate the csv file like this:

week,abc,bfg,jhg
1,60,23,34
2,20,45,23
3,80,633,20

(also it may be possible that all names will not have some quantity in every week)


Thanks
Alex B
Post Reply