0

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>";
Improve this question
5
  • 1
    If you aren't interested in performance, just grab the entire table and then loop through it storing the data by category. What have you tried, in your php, already? Have you tried any code for this yet at all? Commented Jun 20, 2018 at 2:14
  • performance is not super imperative since this table will only be <1000 rows. I can call each row right now no problem, but I would like to be able to display it in a fancier way than having category running down the left side @ChrisCousins Commented Jun 20, 2018 at 2:20
  • 1
    I can provide a nice answer, but so you can copy/paste it - can you please include you mysqli code too? Just so it is complete in terms of your access/calls to the db? Commented Jun 20, 2018 at 2:23
  • added my basic php code for running through each row of the mysql database Commented Jun 20, 2018 at 2:30
  • @ChrisCousins if you can comment it out too so I can learn from it, that would be awesome. Commented Jun 20, 2018 at 2:55

2 Answers 2

Reset to default
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. :)

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

You are a legend. My if category was always messing up. This makes sense, so every time a new cat is found, it will restart the while loop.
I think I am trying to play with fire now ... but I would like to try and insert counts() of product in the category header of the small tables... would I need to run a second query inside the while loop?
I think it will be something like this. But you need to try. :) SELECT p.*, count(p.cat) FROM wp_products p LEFT OUTER JOIN wp_products wp ON wp.cat = p.cat GROUP BY p.id ORDER BY p.cat
funny enough, I was reading up on LEFT JOIN last night . I don't know how the different row amounts will be handled though after a sum and count is done.
0

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.

Improve this answer

2 Comments

Thanks for the response! I guess my main question is the loop here; how would I loop the category if I placed them in order? Im thinking of doing this with name based categories too so a simple mathematical max(cat) wouldn't always work... I can order them in aphabetical order, but how would I know the rows have all been displayed? I will also get in the habit of not always using * ... its just too easy at the start of learning php/mysql
If you put an ORDER BY statement, the database will return all entries, in the order you specify. Any database that does not return a full data set on a query should not be used. So i would not be too worried of getting all the rows. If the database is relatively small, this would be the best solution actually. It is always a balancing act between the number of queries vs the volume of results the query returns. I will add something in the answer for your loop question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.