Page 1 of 1

I think, it's about nested loop, help me...

Posted: Tue Aug 05, 2014 2:42 am
by brainanda
Here is my problem
I'd like to make a report using web and this is the concept of the report (it's a dummy data)

Image

the data gets from database.
the "Merk" is dynamic data, I mean, it's because the Date Range is from August 5 until August 10, if the range is from August 10 until August 15, the list of Merk may be different, I mean the shown Merk in range date of 5-10 is : Merk 1 until 4, but if the range date start from 10 until 15, the list may be : Merk 1, 2, 3, 5, and 6

I know if the used query just for make a group like on the screen-shot, but how I can add the "Total" in each the end of the group?

Thanks, and

I hope my English understandable :)

Re: I think, it's about nested loop, help me...

Posted: Tue Aug 05, 2014 3:44 am
by requinix
It's easiest to build the table using PHP instead of trying to make one query that will return the table as you want it.

Can you write a query that gets the merk ("Merk 1", etc), the date, a color (one per row), and the count for that color? Like

Code: Select all

Merk 1 | 8/5/2014 | Red  | 5
Merk 1 | 8/5/2014 | Blue | 8
...
Merk 2 | 8/5/2014 | Red  | 4
...
Merk 3 | 8/8/2014 | Red  | 6
...
Merk 4 | 8/8/2014 | Red  | 7
Then the PHP can "pivot" that data into the rows and columns you want.

Re: I think, it's about nested loop, help me...

Posted: Tue Aug 05, 2014 6:56 am
by brainanda
hmm, if there's no Total, I can do myself for the code, but the problem is how to Add the Total in every "Merk" as the total of each "Color". Some answer from the Google by using dual query and looping, like

Code: Select all

First SQL to make the output like this :
Merk1
Total
Merk2
Total
Merk3
...

And the second SQL :
Merk1
Merk1
....
Total
Merk2
Merk2
...
Total
...
Somehow I can understand the algorithm but I just can't implement into PHP code

Re: I think, it's about nested loop, help me...

Posted: Tue Aug 05, 2014 7:15 am
by Celauran
Use multidimensional arrays? merkx and colour as your keys, and you increment as you iterate over the results.

Re: I think, it's about nested loop, help me...

Posted: Tue Aug 05, 2014 1:16 pm
by requinix
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

Code: Select all

...ORDER BY merk ASC, date ASC
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.