You can add the totals and spread the colors out into columns using code.
As long as there's not
too much data (the ceiling is pretty high actually so you'll probably be fine), read all the data and store it into a multidimensional array. The query should return the merk and dates in the order you want them - it's easier to do that part in the query than in code. Such as
Then you output the array. Goes something like this:
Code: Select all
1. Start with one empty array for the data. Each element in the array will be an array of Merk groups (the key is the "Merk X" value), each element in that will be an array of dates (the key is the date), and each element in that will be a color and its count.
2. Start with another empty array for the different colors possible. This way you can make sure you use the same colors in the same order later You'll add to it as go.
3. Read a row.
4. Grab the Merk value. Look in the first array to see if you've already added a group for it; if not then initialize it with an empty array.
5. Grab the date value. Look in the Merk array to see if you've already added a row for the date; if not then initialize it with an empty array.
6. Grab the color and count. Add it to the Merk's date array.
7. Check the second array to see if it already has the color; if not then add it.
8. Continue reading rows until you've reached the end.
Code: Select all
// 1. empty array of data
$data = array();
// 2. empty array of colors
$allcolors = array();
// 3. fetch rows
while ($row = /* fetch row */) {
// assuming $row is array("merk" => "Merk 1", "date" => "8/5/2014", "color" => "Red", "count" => 5)
// 4. look for an existing merk value
$merk = $row["merk"];
if (!isset($data[$merk])) {
$data[$merk] = array();
}
// 5. look for an existing date value
$date = $row["date"];
if (!isset($data[$merk][$date])) {
$data[$merk][$date] = array();
}
// 6. add color and count to the array
$color = $row["color"];
$count = $row["count"];
$data[$merk][$date][$color] = $count;
// 7. may need to add color to colors array
if (!in_array($color, $allcolors)) {
$allcolors[] = $color;
}
}
When outputting, loop over:
1. The first array of data to get a Merk value and group of dates, then
2. The group of dates to get a date and set of colors, then
3. The second array of colors to get a possible color
Check if the set of colors has an entry for the possible color: if so then show the value, otherwise show 0 or empty or whatever. For the totals, keep a running tally as you're outputting.
Code: Select all
echo "<table>";
echo "<tr><th></th><th>Tanggal</th>";
foreach ($allcolors as $color) {
echo "<th>{$color}</th>";
}
echo "</tr>";
// 1. merk and group of dates
foreach ($data as $merk => $dates) {
// total per merk. we can initialize this array very easily:
$totals = array_fill_keys($allcolors, 0);
// 2. date and set of colors
foreach ($dates as $date => $colors) {
echo "<tr>";
echo "<td>{$merk}</td>";
echo "<td>{$date}</td>";
// 3. set of possible colors
foreach ($allcolors as $color) {
echo "<td>";
if (isset($colors[$color])) {
echo $colors[$color];
// add to the running total
$totals[$color] += $colors[$color];
} else {
echo "0";
}
echo "</td>";
}
echo "</tr>";
}
// show the totals
echo "<tr>";
echo "<td>Total</td>";
echo "<td>-</td>";
foreach ($allcolors as $color) {
echo "<td>";
echo $totals[$color];
echo "</td>";
}
echo "</tr>";
}
echo "</table>";
Untested but I'm fairly confident.