Logan Voss Logan Voss - 1 month ago 8
MySQL Question

nested foreach loop giving weird incremental result

I am planning on building an html table filled with locations followed by 2 subcategories of things, all pulled from the database and stored in a PHP array. Something weird is going on with my

foreach
loop.

$loc_query = "SELECT Location FROM Locations;";
$loc_result = $mysqli->query($loc_query);

while($row = $loc_result->fetch_assoc()){
$loc_results[] = $row;
}


foreach($loc_results as $loc){
echo '<tr align="right"><td width="20" align="center" onmousedown="show()" bordercolor="#000000"
style="cursor:pointer;font-size:10pt;font-weight:bold;border-style:solid;border-width:1pt">+</td>
<td width="120" align="left"
bgcolor="#00FFFF" style="border-top-style:solid;border-top-width:1pt;border-right-style:solid;
border-right-width:1pt;border-bottom-style:solid;border-bottom-width:1pt" >'.$loc['Location'].'</td></tr>';

$CC_query = "SELECT Cost_Center_Category.Cost_Center_Category_Name, Cost_Center_Name,
Locations.Location_Abbrev, Locations.Location, LC,
FX, RR_In_LC, Hours
FROM Cost_Centers
LEFT JOIN Cost_Center_Category
ON Cost_Centers.Cost_Center_Category = Cost_Center_Category.Cost_Center_Category_ID
LEFT JOIN Locations ON Cost_Centers.Location = Locations.Location_Abbrev;";

$CC_result = $mysqli->query($CC_query);


while($row = $CC_result->fetch_assoc()){
$CC_results[] = $row;
}

foreach ($CC_results as $result){
switch($result['Cost_Center_Category_Name'])
{
case 'Strategy & Performance' :
echo '<tr align="right"><td width="20" align="center" onmousedown="show()" bordercolor="#000000"
style="cursor:pointer;font-size:10pt;font-weight:bold;border-style:solid;border-width:1pt">+</td>
<td width="150" align="left"
bgcolor="#FFCC00"
style="border-top-style:solid;border-top-width:1pt;border-right-style:solid;
border-right-width:1pt;border-bottom-style:
solid;border-bottom-width:1pt"
onmousedown="show()">'.$result['Cost_Center_Category_Name'].'</td></tr>';

break;

}
}
}


It outputs all the locations fine, but once I try to populate each location with a cost center category, it outputs something like


  • Nuremberg

  • Strategy & Performance

  • Regensburg

  • Strategy & Performance

  • Strategy & Performance

  • Sibiu

  • Strategy & Performance

  • Strategy & Performance

  • Strategy & Performance



and it grows each time. I know it must be something to do with my logic, I've tried things like
break 2
and I think I've been staring at it too long, it's probably really obvious.
Is there something wrong with the way I set up the nested loops or is it something else?

Answer

Reset $CC_results to empty array on every iteration of foreach loop:

foreach($loc_results as $loc) {
    $CC_results = array();

    // all other code stays the same

}
Comments