another nested loops php + mysql query

Question

I have searched this for hours and read numerous q/a's on foreach loops and while loops for the answer to my question but have yet to find a response that resembled my query. Mostly categorized menues...

I have a mysql table setup like this

cat | product | link  | status
1   | milk    | https | in stock
1   | eggs    | https | in stock
2   | butter  | https | out of stock
2   | bread   | https | in stock
3   | bananas | https | in stock 

and would like to group the data in a php looped table like this;

         Category 1
 milk    | https | in stock
 eggs    | https | in stock
         Category 2
 butter  | https | out of stock
 bread   | https | in stock
         Category 3
 bananas | https | in stock 

What sort of nested loop would I need? would I need to call a second mysqli query in the nested loop grouping rows by cat ?

Thanks :)

PHP code added Edit

$stmt = $con->prepare("SELECT * FROM wp_products ORDER BY cat");
$stmt->execute();

$results = $stmt->get_result();
echo "<p><center><h4>Master List</h4></center></p>";
echo "<table>
<tr>
<th>Product</th>
<th>Link</th>
<th>Status</th>
</tr>";
if (mysqli_num_rows($results)>0){
while($row = $results->fetch_assoc())
{
  echo "<tr><td>" . $row['product'] ."</td>";
  echo "<td>". $row['link'] ."</td>;
  echo "<td>". $row['status'] ."</td></tr>";
}
}
echo "</table>";

Answer 1

Mostly it will be like the below.

The idea is

1. Sort the rows with category. [you have done this part.]
2. Declare the category with a default value.
3. If there is any change, update the category value with new one. [as it is sorted, it will always grouped by default. ]

The code will be like this.

<?php

$stmt = $con->prepare("SELECT * FROM wp_products ORDER BY cat");
$stmt->execute();

$category = '';  # Default value for category. 

$results = $stmt->get_result();
echo "<p><center><h4>Master List</h4></center></p>";
echo "<table>
<tr>
<th>Product</th>
<th>Link</th>
<th>Status</th>
</tr>";
if (mysqli_num_rows($results) > 0) {
   while ($row = $results->fetch_assoc()) {

      # Category will be updated in this query 
      if($category != $row['cat']) { 
         $category = $row['cat']; 
         echo '<tr><td colspan="3"> Category ' . $category .' </td></tr>'; 
      }

      echo "<tr><td>" . $row['product'] . "</td>";
      echo "<td>" . $row['link'] . "</td>";
      echo "<td>" . $row['status'] . "</td></tr>";
   }
}
echo "</table>";

Check on. :)

Answer 2

No code included since you did not provide any to start, but the concept is sound IMHO.

• Select the highest cat in the database SELECT MAX(cat) FROM ...
• Loop from 1 to that highest cat. In each loop, select the entries that match the current loop index SELECT cat,product,link,status FROM ....

• Loop on the results (foreach here with DB results) and build the `

  <table>
    <theader>
      <tr><th colspan="3">Category $loopindex</th><tr>
    </theader>
    <tbody>
      <tr>
        <td>$result[product]</td>
        <td>$result[link]</td>
        <td>$result[status]</td>
      </tr>
    </tbody>
  </table>
  

• increment +1 the loop index

• go on to next category

There are many details to work out (proper PHP/HTML code, CSS for your table, how to access a DB with PHP, ...), but the principle would be ok.

---

Ok you added code after my answer.

• The problem with the SELECT * is that you are getting the entire table at once. It might be ok for small databases, but this is a habit you should avoid. One day you will get large data sets (or this one will grow).
• Also you must cycle through all the results and figure in which table each result goes to. You will have to store that in an array (?) and from these arrays output your html table code ?
• your ORDER BY value is not listed in your result (by name).
• If you did your ORDER BY cat, at least you would know that each entry is the proper order to output your <table> html directly to the page without some buffer mechanism in the middle.
• This way each time you see a new cat value, you could start a new table, or add a <tr><td colspan="3">Category 1</td></tr> divider line.

---

Response to the comment asking about the loop on category.

Lets assume this table:

name product link status
 1   p1      l1    s1
 2   p2      l2    s2
 1   p3      l3    s3
 1   p4      l4    s4

The query SELECT cat,product,link,status FROM table ORDER BY cat,product will give you this result set:

1,p1,l1,s1
1,p3,l3,s3
1,p4,l4,s3
2,p2,l2,s2

You cna therefore loop on these results, one line at a time. Each loop must keep the cat value for the next loop. If it changes, this is where you put a header to identify the category. Then keep printing lines until they are all done, or until a new category is found. and on and on.