Logan Voss Logan Voss - 1 month ago 5
MySQL Question

Is my loop logic wrong or is it my query?

I have a couple nested loops in php to produce something like this:

Cost table screenshot

My problem lies either with this query:

$cost_inner_query = "SELECT Cost_Center_Category.Cost_Center_Category_Name,
Cost_Centers.Cost_Center_Number AS l, Cost_Centers.Cost_Center_Name,
Locations.Location_Abbrev, Locations.Location, Cost_Category.Cost_Category_Name, Cost_Planning.*
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
LEFT JOIN Cost_Planning
ON Cost_Centers.Cost_Center_Number = Cost_Planning.Cost_Center_Number
LEFT JOIN Cost_Category
ON Cost_Planning.Cost_Category = Cost_Category.Cost_Category_ID
WHERE Cost_Centers.Location = '$cost_current_location'
AND Cost_Category.Cost_Category_ID = $cost_current_ccr
AND Cost_Centers.Cost_Center_Category = $cost_current_ccc_id;";


or my loop structure:

foreach($cost_loc_results as $cost_loc){ //loop to populate data table with locations
$cost_CC_results = array();
echo '<tr align="right" class="cost_loc"><td class="plus_minus" width="20" align="center" bordercolor="#000000"
style="cursor:pointer;font-size:10pt;font-weight:bold;border-style:solid;border-width:1pt">+</td>
<td width="20" align="left" bordercolor="#000000" bgcolor="#00FFFF"
style="font-size:8pt;border-style:solid;border-width:1pt">'.$cost_loc['Location_Abbrev'].'</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" >'.$cost_loc['Location'].'</td>
<td width="50" 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" >'.$cost_loc['Hours'].'</td>
<td width="50" 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" >'.$cost_loc['LC'].'</td>
<td width="50" 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" >'.$cost_loc['FX'].'</td>';
for($i=0; $i<10; $i++){
if($i%2 == 0){
echo '<td id="Sum'.$cost_loc['Location_Abbrev'].''.$i.'" bgcolor="#e3e4e5" width="20" style="font-size:8pt;border-style:solid;border-width:1pt"></td>';
}
else {
echo '<td id="Sum'.$cost_loc['Location_Abbrev'].''.$i.'" bgcolor="#b2b2b2" width="20" style="font-size:8pt;border-style:solid;border-width:1pt"></td>';

}
}
echo '<td bgcolor="#FFFFFF"></td>';
for($i=10; $i<20; $i++){
if($i%2 == 0){
echo '<td id="Sum'.$cost_loc['Location_Abbrev'].''.$i.'" bgcolor="#e3e4e5" width="20" style="font-size:8pt;border-style:solid;border-width:1pt"></td>';
}
else {
echo '<td id="Sum'.$cost_loc['Location_Abbrev'].''.$i.'" bgcolor="#b2b2b2" width="20" style="font-size:8pt;border-style:solid;border-width:1pt"></td>';

}
}
foreach ($cost_category_results as $ccr){
echo '<tr align="right" class="ccr" style="display:none;"><td class="plus_minus" width="20" align="center" bordercolor="#000000"
style="cursor:pointer;font-size:10pt;font-weight:bold;border-style:solid;border-width:1pt">-</td>
<td colspan="5" width="20" align="left" bordercolor="#000000" bgcolor="#a8fff3"
style="font-size:8pt;border-style:solid;border-width:1pt">'.$ccr['Cost_Category_Name'].'</td>';
for($i=0; $i<10; $i++){
if($i%2 == 0){
echo '<td id="Sum'.$cost_loc['Location_Abbrev'].''.$ccr['Cost_Category_Name'].''.$i.'" bgcolor="#e3e4e5" width="20" style="font-size:8pt;border-style:solid;border-width:1pt"></td>';
}
else {
echo '<td id="Sum'.$cost_loc['Location_Abbrev'].''.$ccr['Cost_Category_Name'].''.$i.'" bgcolor="#b2b2b2" width="20" style="font-size:8pt;border-style:solid;border-width:1pt"></td>';
}
}
echo '<td bgcolor="#FFFFFF"></td>';
for($i=10; $i<20; $i++){
if($i%2 == 0){
echo '<td id="Sum'.$cost_loc['Location_Abbrev'].''.$ccr['Cost_Category_Name'].''.$i.'" bgcolor="#e3e4e5" width="20" style="font-size:8pt;border-style:solid;border-width:1pt"></td>';
}
else {
echo '<td id="Sum'.$cost_loc['Location_Abbrev'].''.$ccr['Cost_Category_Name'].''.$i.'" bgcolor="#b2b2b2" width="20" style="font-size:8pt;border-style:solid;border-width:1pt"></td>';
}
}

foreach($cost_CCC_results as $cost_ccc){
$cost_inner_results = array();
$cost_current_location = $cost_loc['Location_Abbrev'];
$cost_current_ccc_id = $cost_ccc['Cost_Center_Category_ID'];
$cost_current_ccr = $ccr['Cost_Category_ID'];
echo '<tr align="right" class="cost_ccc" style="display:none;"><td width="20" align="center"
style="font-size:10pt;font-weight:bold;"></td>
<td colspan="5"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-left-style:solid;border-left-width:
1pt;border-bottom-width:1pt">'.$cost_ccc['Cost_Center_Category_Name'].'</td>';
for($i=0; $i<10; $i++){
if($i%2 == 0){
echo '<td id="Sum'.$cost_loc['Location_Abbrev'].''.$cost_ccc['Cost_Center_Category_Name'].''.$i.'" bgcolor="#e3e4e5" width="20" style="font-size:8pt;border-style:solid;border-width:1pt"></td>';
}
else {
echo '<td id="Sum'.$cost_loc['Location_Abbrev'].''.$cost_ccc['Cost_Center_Category_Name'].''.$i.'" bgcolor="#b2b2b2" width="20" style="font-size:8pt;border-style:solid;border-width:1pt"></td>';
}
}
echo '<td bgcolor="#FFFFFF"></td>';
for($i=10; $i<20; $i++){
if($i%2 == 0){
echo '<td id="Sum'.$cost_loc['Location_Abbrev'].''.$cost_ccc['Cost_Center_Category_Name'].''.$i.'" bgcolor="#e3e4e5" width="20" style="font-size:8pt;border-style:solid;border-width:1pt"></td>';
}
else {
echo '<td id="Sum'.$cost_loc['Location_Abbrev'].''.$cost_ccc['Cost_Center_Category_Name'].''.$i.'" bgcolor="#b2b2b2" width="20" style="font-size:8pt;border-style:solid;border-width:1pt"></td>';
}
}
$cost_inner_query = "SELECT Cost_Center_Category.Cost_Center_Category_Name,
Cost_Centers.Cost_Center_Number AS l, Cost_Centers.Cost_Center_Name,
Locations.Location_Abbrev, Locations.Location, Cost_Category.Cost_Category_Name, Cost_Planning.*
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
LEFT JOIN Cost_Planning
ON Cost_Centers.Cost_Center_Number = Cost_Planning.Cost_Center_Number
LEFT JOIN Cost_Category
ON Cost_Planning.Cost_Category = Cost_Category.Cost_Category_ID
WHERE Cost_Centers.Location = '$cost_current_location'
AND Cost_Category.Cost_Category_ID = $cost_current_ccr
AND Cost_Centers.Cost_Center_Category = $cost_current_ccc_id;";

$cost_inner_result = $mysqli->query($cost_inner_query);

while($row = $cost_inner_result->fetch_assoc()){
$cost_inner_results[] = $row;
}
foreach($cost_inner_results as $cost_inner){
echo '<tr class="inner" style="display:none;"><td> </td><td bordercolor="#000000" style="font-size:8pt;border-style:solid;border-width:1pt">
'.$cost_inner['l'].'</td>
<td bordercolor="#000000" colspan="4" style="font-size:8pt;border-style:solid;border-width:1pt">'.$cost_inner['Cost_Center_Name'].'</td>';
echo '<td name="LC'.$cost_loc['Location_Abbrev'].''.$cost_inner['Cost_Category_Name'].''.$cost_ccc['Cost_Center_Category_Name'].''.$cost_inner['Cost_Center_Name'].'2015Q1" id="LC'.$cost_loc['Location_Abbrev'].''.$cost_inner['Cost_Category_Name'].''.$cost_ccc['Cost_Center_Category_Name'].''.$cost_inner['Cost_Center_Name'].'2015Q1" bgcolor="#FFFFFF" align="right" width="20" style="font-size:8pt;border-style:solid;border-width:1pt;display:none" contenteditable="true">'.$cost_inner['2015_Q_1'].'</td>';
//past this point it just continues doing this for all <td>'s in the table.


I know it's a lot of code I just posted there but the gist is that I use nested loops to form Location rows, then Cost Category rows, Cost Center Category rows, and then Cost Center rows. (I know, the naming of these is confusing)
What happens with my current query above is that in the innermost loop that forms the Cost Center rows (the rows with all the data), the only rows that show up are the ones that match the query, the ones where
Cost_Category.Cost_Category_ID = $cost_current_ccr
.
Illustrated here in the bottom table:

help me

which is technically correct, but what I want to happen is similar to the the top table displayed: I want the rows that match to populate the data table to the right, but if there is no match, I still want the cost center to be displayed.

I've tried omitting the first AND clause from my
$cost_inner_query
, that populates all the cost centers but doesn't match the data correctly.

Is my query the thing that needs to be changed or is it the loop structure, or something else like the database structure?

Answer

There's a slight flaw in your query, as I mentioned in my comment.

Try this instead:

SELECT x.Cost_Center_Category_Name
     , y.Cost_Center_Number 
     , y.Cost_Center_Name
     , l.Location_Abbrev
     , l.Location
     , z.Cost_Category_Name
     , p.*  
  FROM Cost_Centers y
  LEFT 
  JOIN Cost_Center_Category x
    ON c.Cost_Center_Category_ID = y.Cost_Center_Category 
  LEFT 
  JOIN Locations l
    ON l.Location_Abbrev = y.Location
  LEFT 
  JOIN Cost_Planning p
    ON p.Cost_Center_Number = y.Cost_Center_Number
  LEFT 
  JOIN Cost_Category z
    ON z.Cost_Category_ID = p.Cost_Category
   AND z.Cost_Category_ID = $cost_current_ccr
 WHERE y.Location = '$cost_current_location'
   AND y.Cost_Center_Category = $cost_current_ccc_id

Because your code beginning with the line foreach($cost_CCC_results as $cost_ccc){ is incomplete, it's hard to identify further errors.

That said, I'm glad I don't have to maintain a database employing this naming policy - nor manage variables as subtly different as '$cost_CC_results' and '$cost_CCC_results'

Comments