title: Is my loop logic wrong or is it my query?
body: <p>I have a couple nested loops in php to produce something like this:</p>
  
  <p><a href="https://i.stack.imgur.com/CH2fQ.png" rel="nofollow"><img src="https://i.stack.imgur.com/CH2fQ.png" alt="Cost table screenshot"></a></p>
  
  <p>My problem lies either with this query: </p>
  
  <pre><code>$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;";
  </code></pre>
  
  <p>or my loop structure: </p>
  
  <pre><code>foreach($cost_loc_results as $cost_loc){ //loop to populate data table with locations
          $cost_CC_results = array();
          echo '&lt;tr align="right" class="cost_loc"&gt;&lt;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"&gt;+&lt;/td&gt;
              &lt;td width="20" align="left" bordercolor="#000000" bgcolor="#00FFFF"
              style="font-size:8pt;border-style:solid;border-width:1pt"&gt;'.$cost_loc['Location_Abbrev'].'&lt;/td&gt; 
              &lt;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" &gt;'.$cost_loc['Location'].'&lt;/td&gt;
              &lt;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" &gt;'.$cost_loc['Hours'].'&lt;/td&gt;
              &lt;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" &gt;'.$cost_loc['LC'].'&lt;/td&gt;
              &lt;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" &gt;'.$cost_loc['FX'].'&lt;/td&gt;';
          for($i=0; $i&lt;10; $i++){ 
              if($i%2 == 0){
                  echo '&lt;td id="Sum'.$cost_loc['Location_Abbrev'].''.$i.'" bgcolor="#e3e4e5" width="20" style="font-size:8pt;border-style:solid;border-width:1pt"&gt;&lt;/td&gt;';
              }
              else {
                  echo '&lt;td id="Sum'.$cost_loc['Location_Abbrev'].''.$i.'" bgcolor="#b2b2b2" width="20" style="font-size:8pt;border-style:solid;border-width:1pt"&gt;&lt;/td&gt;';
  
              }
          }
          echo '&lt;td bgcolor="#FFFFFF"&gt;&lt;/td&gt;';
          for($i=10; $i&lt;20; $i++){
              if($i%2 == 0){
                  echo '&lt;td id="Sum'.$cost_loc['Location_Abbrev'].''.$i.'" bgcolor="#e3e4e5" width="20" style="font-size:8pt;border-style:solid;border-width:1pt"&gt;&lt;/td&gt;';
              }
              else {
                  echo '&lt;td id="Sum'.$cost_loc['Location_Abbrev'].''.$i.'" bgcolor="#b2b2b2" width="20" style="font-size:8pt;border-style:solid;border-width:1pt"&gt;&lt;/td&gt;';
  
              }
          }
          foreach ($cost_category_results as $ccr){
              echo '&lt;tr align="right" class="ccr" style="display:none;"&gt;&lt;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"&gt;-&lt;/td&gt;
              &lt;td colspan="5" width="20" align="left" bordercolor="#000000" bgcolor="#a8fff3"
              style="font-size:8pt;border-style:solid;border-width:1pt"&gt;'.$ccr['Cost_Category_Name'].'&lt;/td&gt;';
              for($i=0; $i&lt;10; $i++){
                  if($i%2 == 0){
                      echo '&lt;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"&gt;&lt;/td&gt;';
                  }
                  else {
                      echo '&lt;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"&gt;&lt;/td&gt;';
                  }
              }
              echo '&lt;td bgcolor="#FFFFFF"&gt;&lt;/td&gt;';
              for($i=10; $i&lt;20; $i++){
                  if($i%2 == 0){
                      echo '&lt;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"&gt;&lt;/td&gt;';
                  }
                  else {
                      echo '&lt;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"&gt;&lt;/td&gt;';
                  }
              } 
  
              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 '&lt;tr align="right" class="cost_ccc" style="display:none;"&gt;&lt;td width="20" align="center"
                      style="font-size:10pt;font-weight:bold;"&gt;&lt;/td&gt;
                      &lt;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"&gt;'.$cost_ccc['Cost_Center_Category_Name'].'&lt;/td&gt;';
                  for($i=0; $i&lt;10; $i++){
                      if($i%2 == 0){
                          echo '&lt;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"&gt;&lt;/td&gt;';
                      }
                      else {
                          echo '&lt;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"&gt;&lt;/td&gt;';
                      }
                  }
                  echo '&lt;td bgcolor="#FFFFFF"&gt;&lt;/td&gt;';
                  for($i=10; $i&lt;20; $i++){
                      if($i%2 == 0){
                          echo '&lt;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"&gt;&lt;/td&gt;';
                      }
                      else {
                          echo '&lt;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"&gt;&lt;/td&gt;';
                      }
                  }
                  $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-&gt;query($cost_inner_query); 
  
                  while($row = $cost_inner_result-&gt;fetch_assoc()){
                      $cost_inner_results[] = $row;
                  }
                  foreach($cost_inner_results as $cost_inner){
                      echo '&lt;tr class="inner" style="display:none;"&gt;&lt;td&gt; &lt;/td&gt;&lt;td bordercolor="#000000" style="font-size:8pt;border-style:solid;border-width:1pt"&gt;
                      '.$cost_inner['l'].'&lt;/td&gt;
                      &lt;td bordercolor="#000000" colspan="4" style="font-size:8pt;border-style:solid;border-width:1pt"&gt;'.$cost_inner['Cost_Center_Name'].'&lt;/td&gt;';
                      echo '&lt;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"&gt;'.$cost_inner['2015_Q_1'].'&lt;/td&gt;';
  //past this point it just continues doing this for all &lt;td&gt;'s in the table.
  </code></pre>
  
  <p>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  <code>Cost_Category.Cost_Category_ID = $cost_current_ccr</code>. 
  Illustrated here in the bottom table:</p>
  
  <p><a href="https://i.stack.imgur.com/dWU4F.png" rel="nofollow"><img src="https://i.stack.imgur.com/dWU4F.png" alt="help me"></a></p>
  
  <p>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. </p>
  
  <p>I've tried omitting the first AND clause from my <code>$cost_inner_query</code>, that populates all the cost centers but doesn't match the data correctly.</p>
  
  <p>Is my query the thing that needs to be changed or is it the loop structure, or something else like the database structure? </p>