How to loop rows with PHP using GROUP_CONCAT MySQL

Question

I am triyng to loop the products coming from different categories as shown below:

 $sql = query("SELECT c.name AS 'cat_name', c.image AS 'image', 
                GROUP_CONCAT(DISTINCT p.title ORDER BY p.title DESC SEPARATOR ' ') AS 'product_name' 
                FROM products p 
                INNER JOIN categories c 
                ON p.category_id = c.id 
                GROUP BY p.category_id");
  confirm($sql);

  while($category = mysqli_fetch_array($sql)) {
  $content = <<<DELIMITER


       <li class="col-md-3 col-sm-6">
                <ul>
                  <li class="dropdown-header">{$category['cat_name']}</li>
                  <li><a title="Title" href="#">{$category['product_name']}</a></li>                    

                </ul>
              </li>



 DELIMITER;

 echo $content;
 }

The script above produces following result:

Category name
Product 1 Product 2...

I would like to have the products listed like this:

Category name
Product 1
Product 2
...

What I am doing wrong?

Um, so don't use GROUP_CONCAT... GROUP BY

Strawberry
– Strawberry

2015-11-23 12:25:18 +00:00
Commented Nov 23, 2015 at 12:25 — Strawberry
– Strawberry, Commented Nov 23, 2015 at 12:25

dkg · Accepted Answer · 2015-11-23 15:59:59Z

2

The function GROUP_CONCAT with get you a response of only one line for each category. It will concatenate all your lines with the same category in only one.

You have two possibilities :

Using GROUP_CONCAT : each line will contain your Category with all your products concatenated. Then you should split the concatenation of your "Product name's list" to display them correctly.
Do not use GROUP_CONCAT : you group your result by the category name. In your loop, you check when the category name change and then change your html accordingly.

Edit:

Your variable $category['product_name'] contains the names of your products concatenated by the function GROUP_CONCAT using the delimiter space. If you write

echo $category['product_name']

it will print

Product 1 Product 2...

You can use split() (obsolete) or explode() to separate the products names. And then, you will need to iterate over the array explode() returns to get each Product.

But if your products contains spaces in their names, using GROUP_CONCAT with space as delimiter and split with space as delimiter, you'll get too much results. I advise you tu use another delimiter that can't be use in the product name.

edited Nov 23, 2015 at 15:59

answered Nov 23, 2015 at 12:22

dkg

1,78515 silver badges34 bronze badges

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

4 Comments

Robert D. Over a year ago

Hi, thanks for your answer. I did not quite understand the poin 1: Then you should split the concatenation of your "Product name's list" to display them correctly. What do you mean exactly?

dkg Over a year ago

The field product_name of your query will contain the product names concatenated by a space. If you split your string by the concatenation character, you will get a set of elements. (Note that if your are using spaces in your product name, you will get much more products, hence you should choose an appropriate concatenation character like a pipe | or something else.

Robert D. Over a year ago

Yes, but the products are always displayed horizontally and not vertically as a list. I do not understand how to loop them as list (vertically)

user5051310 Over a year ago

@Robert explode would work. Just change the seperator for GROUP_CONCAT from ' ' to something that will never be in a product name (like ;?) Than explode on product name in your while loop and loop through the resulting array

Strawberry · Accepted Answer · 2015-11-23 12:27:00Z

0

You could use this instead:

SELECT DISTINCT c.name cat_name
              , c.image
              , p.title product_name
           FROM products p   
           JOIN categories c 
             ON p.category_id = c.id;

answered Nov 23, 2015 at 12:27

Strawberry

34k14 gold badges43 silver badges57 bronze badges

1 Comment

Robert D. Over a year ago

Hi, thanks for your answer. Unfortunatelly your suggestion shows twice the category name

Collectives™ on Stack Overflow

How to loop rows with PHP using GROUP_CONCAT MySQL

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related