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
mysql to associate arrays to csv file
Moderator: General Moderators
-
alex_gmail
- Forum Newbie
- Posts: 4
- Joined: Mon May 25, 2009 5:41 pm
Re: mysql to associate arrays to csv file
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
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
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