Reputation: 51
I have two tables in db: item table (goods1) and order table (kB). In kB each order item equals a single row meaning an order of 2 items = 2 rows with the same bill number in the appropriate column. IU want to group the ordered items to each bill number I got the following:
<?
...
$sql = $conn->query("
SELECT
kB.BillNum AS B, goods1.*,
GROUP_CONCAT(goods1.ArtName) AS bestArts,
GROUP_CONCAT(goods1.ArtNrAttach) AS ArtNrAttachs,
GROUP_CONCAT(kB.Menge) AS Menges,
FROM
kB, goods1
WHERE kB.bestArt = goods1.id
AND kB.ordDate LIKE '%$datum_seek%'
GROUP BY kB.BillNum");
if(mysqli_error($conn)){ echo " ERROR";}
ELSE IF(!mysqli_error($conn)){
echo'<table colspan="6" style="border:1px solid black;">
<tr>
<th colspan="1">bill number</th>
<th colspan="1">item</th>
<th colspan="1">item nummer</th>
</tr>';
while($row = $sql->fetch_assoc()) {
$bestArts = split(",",$row["bestArts"]);
$ArtNrAttachs = split(",",$row["ArtNrAttachs"]);
$Menges = split(",",$row["Menges"]);
echo '<tr><td style="background-color:lightblue;">'. $row["B"].'</td>';
?>
<td><?php echo $bestArts[0]; array_shift($bestArts); ?></td>
<td><?php echo $ArtNrAttachs[0]; array_shift($ArtNrAttachs); ?></td>
<td><?php echo $Menges[0]; array_shift($Menges); ?></td>
</tr>
<tr>
<td></td>
<?php
foreach($bestArts as $bestArt):
echo '<td>' . $bestArt .'</td>';
endforeach;
foreach($ArtNrAttachs as $ArtNrAttach):
echo '<td>' . $ArtNrAttach .'</td>';
endforeach;
foreach($Menges as $Menge):
echo '<td>' . $Menge .'</td>';
endforeach;
...
This prints all right:
| B | ArtName | ArtNr | Menge |
| ArtName | ArtNr | Menge |
| B | ArtName | ArtNr | Menge |
| ArtName | ArtNr | Menge |
but if I have more than 2 lines for each 'B'(items for 1 order) then it prints like this:
| B | ArtName | ArtNr | Menge |
| ArtName | ArtNr | Menge |
| B | ArtName | ArtNr | Menge |
| ArtName | ArtNr | Menge |
| ArtName | ArtName |ArtNr | ArtNr | Menge | Menge |
The expected output:
| B | ArtName | ArtNr | Menge |
| ArtName | ArtNr | Menge |
| B | ArtName | ArtNr | Menge |
| ArtName | ArtNr | Menge |
| ArtName | ArtNr | Menge |
I tried, but I cannot find a solution to this. Can someone help?
Upvotes: 0
Views: 44
Reputation: 13601
If the expected output is:
| B | ArtName | ArtNr | Menge |
| ArtName | ArtNr | Menge |
| B | ArtName | ArtNr | Menge |
| ArtName | ArtNr | Menge |
| ArtName | ArtNr | Menge |
| ArtName | ArtNr | Menge |
You could change your code to: (assuming $bestArts
, $bestArts
, $ArtNrAttachs
have the same size)
<?php for ($i = 0; $i < count($bestArts); $i++) { ?>
<td><?php echo $bestArts[$i];?></td>
<td><?php echo $ArtNrAttachs[$i];?></td>
<td><?php echo $Menges[$i];?></td>
<?php } ?>
And remove the foreach's.
Proposing minimal change, but sure you need to rethink about your code structure.
Upvotes: 1