Displaying all items from mysql database

Question

I have table product and table category in my database. I want to display all category dynamically in a table and inside the table of each category I am displaying all item belonged to that category too.

Those categories and items should be displayed like this :

And here is my coding to do the work :

$fetch_cat = "SELECT * FROM tblcat"; //fetch from table category
$result = $conn->query($fetch_cat);
if ($result->num_rows > 0)
{
    while($cat_row = mysqli_fetch_assoc($result))
    {
        $cat_title = $cat_row['catName'];
        echo '<table>';
        echo '<tr>';
        echo '<td><img src="category/'.$cat_row['catImg'].'" /></td>';
        echo '<td>';
        echo '<ul class="content_init">';

        $stmt = "SELECT * FROM tblproduct WHERE prodCat = '".addslashes($cat_title)."' LIMIT 4"; //fetch from table product
        $result = $conn->query($stmt);
        if ($result->num_rows > 0)
        {
            while($row = mysqli_fetch_assoc($result))
            {
                echo '<li>';
                echo '<a href="#"><img style="height: 188px; width: 188px;" src="user_images/'.$row['prodImg'].'" />';
                echo '<br /><br />';
                echo '<h4>'.$row['prodName'].'</h4>';
                echo '<label><span>RM </span>'.$row['prodPrice'].'</label></a>';
                echo '</li>';
            }
        }

        echo '</ul>';
        echo '</td>';
        echo '</tr>';
        echo '</table>';
    }
}

Problem :

So the problem with my coding is, it can only display Category 1 with items belonged to it. The rest categories unable to be displayed. I guess my coding might not loop properly because of bad programming as it unable to display the expected output.

Using `$result` everywhere is a bad approach( You set new value to it in a subquery, so when you're back to outer query `$result` is __not__ what you expect. — u_mulder, Apr 24 '17 at 08:21
Just rename the $result variable to $result1 or something elase in the nested loop with a different name. Anyways your code is not good/optimized. You should go with one query only where you would join the two tables. — Nedret Recep, Apr 24 '17 at 08:22
@u_mulder Ahh yes! I never notice that until you mention it. Thank you so much! — Emerald, Apr 24 '17 at 08:23

score 0 · Answer 1 · answered Apr 24 '17 at 08:24

$result1 = $conn->query($stmt);
    if ($result1->num_rows > 0)
    {
        while($row = mysqli_fetch_assoc($result1))
        {

Your approach is not good/optimal. You have query for the categories and then one query per each category,so if you have 10 categories your code would execute 11 queries. You should do it with one query only using INNER JOIN

score 0 · Accepted Answer · edited Apr 24 '17 at 10:42

0

The $result variable was reused by the inner query thus the first result override.

Change the variable name either of the $result variable.

edited Apr 24 '17 at 10:42

David

1,147
4
17
29

answered Apr 24 '17 at 08:26

mario caseres

16
2

score 0 · Answer 3 · answered Apr 24 '17 at 08:26

0

You can select all the values left joining the category table for the names and group them by their category ID.

See Group array by subarray values

Afterwards, you can traverse each product for each category by accessing the subarrays.

answered Apr 24 '17 at 08:26

Keith

21
5

Displaying all items from mysql database

3 Answers3