How to fetch results in while loop

Question

I have the following 2 tables.

| ID | Name        | Category |
|----|-------------|----------|
|  1 | Foo bar     | 3        |
|  2 | Bar foo     | 2        |
|  3 | Baz Foo     | 3        |
|  4 | Baz Foo2    | 1        |
|  5 | Baz Foo3    | 1        |
|  3 | Baz Foo     | 1        |

| ID | Category_name |
|----|---------------|
|  1 | Cat 111       | 
|  2 | Cat 222       | 
|  3 | Cat 3333      |

I want to display all categories with counter, example:

Cat111 - 3
Cat222 - 2
Cat333 - 2

I tried to do it by the following way, but its not working:

$query = mysqli_query('SELECT * FROM gallery');

while($row = mysqli_fetch_assoc($query)) {
    $query_cat = mysqli_query($conn, "SELECT * FROM `pics_cat` WHERE id = '".$row['category']."' GROUP BY category_name"); 
    $rowCat = mysqli_fetch_assoc($query_cat);
    echo $rowCat['category_name'];
    echo $rowCat['cnt'];
}

set an empty array outside of the while loop and push the results into the array inside the while loop. Afterward you can display the results with a foreach loop. — Joe Alvini
– Joe Alvini, Commented Nov 19, 2018 at 0:16
FYI mysqli_query() requires the mysqli connection object as the first argument — Phil
– Phil, Commented Nov 19, 2018 at 0:21

Andrea Olivato · Accepted Answer · 2018-11-19 01:05:38Z

2

You are not sharing the names of the tables, but I assume the first one is Gallery and the second one is pics_cat

If your tables are not going to be very large, I suggest you to solve everything with a single join query, which simplifies the logic of your script.

$query = mysqli_query($conn, 'SELECT p.Category_name,COUNT(g.ID) AS cnt FROM `gallery` AS g LEFT JOIN `pics_cat` AS p ON p.ID = g.Category GROUP BY p.ID');

while($row = mysqli_fetch_assoc($query)) {
    echo $rowCat['Category_name'];
    echo $rowCat['cnt'];
}

If you prefer to do this with 2 queries in a loop, it's much easier to start from the Category table and then move to the gallery

$query = mysqli_query($conn, 'SELECT * FROM `pics_cat` ORDER BY ID');

while($row = mysqli_fetch_assoc($query)) {
    $query_count = mysqli_query('SELECT COUNT(ID) AS cnt FROM `gallery` WHERE Category = '.$row['ID'].'');
    $row_count = mysqli_fetch_assoc($query_count);
    echo $row['Category_name'];
    echo $row_count['cnt'];
}

edited Nov 19, 2018 at 1:05

answered Nov 19, 2018 at 0:19

Andrea Olivato

2,5452 gold badges19 silver badges30 bronze badges

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

7 Comments

Phil Over a year ago

The second example would be much better with a prepared statement created once outside the loop then executed with parameters in the loop

Nick Over a year ago

You might want to use a LEFT JOIN in case there are categories which have no entries in gallery, that way you can return a count of 0.

Phil Over a year ago

@FunkFortyNiner prepared statements aren't only good for security. The same statement prepared once and executed multiple times is much more efficient than executing a bespoke query each time. Also, blindly trusting the data in your database is almost as bad as blindly trusting user input. See "Second-order SQL injection"

Jeff Over a year ago

@FunkFortyNiner isn't it faster (in database) beeing once prepared and executed several times with just different values?

Jeff Over a year ago

@AndreaOlivato mysqli_query() still needs a mysqli $link as first param (as Phil has stated).

|

Collectives™ on Stack Overflow

How to fetch results in while loop

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related