Display multiple rows in one column Mysql and PHP

Question

Im trying to display multiple row values in one column. Im using 4 tables which consist of a one to many relationship. I have built a query with the data I will need, Im not sure if the issue lyes here or actually on the PHP side. I have included some code so you can get an idea. I honestly think I may need to use GROUP BY or include a foreach loop after the while to get the result I want. If someone could point me in the right direction or guide me to a solution it would be much appreciated.

I can stop Category duplication by using GROUP BY category_name, but Im having problems with the subCat_name not showing all the results. Only displays the first row. It should show all subCat_name under the main category_name

Currently getting this

Ideas (category_name)

Shop More( subCat_name)
Have a better wardrobe(subCat_description)

But it should be like

Ideas (category_name)

Shop More( subCat_name)
Have a better wardrobe(subCat_description)

Build a Blog( subCat_name)
Share Ideas(subCat_description)

Travel to NYC( subCat_name)
Book a holiday(subCat_description)

MYSQL QUERY

$query = 'SELECT project_users.*, project.project_name, category.category_name,     sub_category.subCat_name, sub_category.subCat_description 
FROM project_users 
JOIN project 
ON project.project_id = project_users.project_id 
JOIN category 
ON category.project_id = project.project_id 
JOIN sub_category 
ON sub_category.category_id = category.category_id';

PHP

$result = mysqli_query($link, $query);

if($result) {

    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {

        echo '<!-- category container for tasks-->
              <div class="col-lg-4 category">
               <section class="panel">
                <div class="panel-body">
                 <header class="to-do-head">';
            echo '<h3>' . $row['category_name'] . '</h3>';
            echo ' <a class="to-do pull-right"><i class="icon-plus"></i> Add Task</a>';
            echo '</header>';
            echo '<div class="col-lg-12 task"><!--open task-->';
            echo '<div class="panel-body">';
            echo '<h4>' . $row['subCat_name'] . '</h4>';
            echo '<p>' . $row['subCat_description'] . '</p>';
            echo '</div><!--close panel-body-->';
            echo '<div class="task-foot">';
            echo '<a href=""><i data-original-title="Add Action" class="icon-legal tooltips"></i></a>
                                 <p class="pull-right"><i class="icon-calendar"></i> 16th Jan 2014</p>
                                   </div><!--close task foot-->';
            echo '</div><!--close-lg-12 task-->';
            echo '</div><!--close panel body-->
                  </section><!--close panel-->
                  </div><!--col lg 4 close container for tasks-->'; 

    }//end while

Answer 1

EDIT: Updated for MySQL

Sorry, missed the note that this was MySQL. Certainly GROUP_CONCAT would be the alternative to LISTAGG (and it's easier). I was having trouble with getting the line break character to work (from what I can gather, '\r\n' or CHAR(13,10) should give a line break, but I haven't had any luck in SQLFiddle).

Line Breaks: http://www.askingbox.com/tip/mysql-line-breaks-in-mysql

GROUP_CONCAT Separator Example: Aggregate function in MySQL - list (like LISTAGG in Oracle)

Here's what I came up with:

SELECT
    category.category_name || CHAR(13, 10)
    || GROUP_CONCAT((sub_category.subCat_name || CHAR(13,10) || subcat_description 
         separator CHAR(13,10)) -- if the separator gives trouble, remove it, use a REPLACE() function around the GROUP_CONCAT, and replace ',' with CHAR(13, 10)
    as list

FROM 
    project_users 
    JOIN project ON project.project_id = project_users.project_id 
    JOIN category ON category.project_id = project.project_id
    JOIN sub_category ON sub_category.category_id = category.category_id

Group By 
    category.category_name

I'm not sure if this will work. The ouput I was getting was an error when trying to use the separator function, or '0' for the rows when using any type of line break. Report back and let me know so I can delete or change the answer as needed.

---

(PREVIOUS ANSWER, good for ORACLE)

For showing multiple rows of column_X in a single row, the below might be a useful workaround when using SQL. Using LISTAGG and the newline character chr(10) will be your friend.

Table

Column Y      Column X
 1             a
 1             b
 1             c
 1             d
 2             foo
 2             bar

Code

SELECT column_Y, LISTAGG(column_X, chr(10)) WITHIN GROUP (ORDER BY column_X) 
FROM table
GROUP BY column_Y

Output

Column Y      Column X
 1             a
               b
               c
               d
 2             foo
               bar

Here's a site with additional examples to get you started if you like this method:

http://www.techonthenet.com/oracle/functions/listagg.php

EDIT:

I thought I would also mention the use of XMLELEMENT and XMLAGG. It was designed for XML, but can also be used with HTML since they both use tags. I don't use it often, so I can't give you a great example, but the documentation (see the following link) does a good job of showing you some of the power of XMLAGG. You can also look for other examples online:

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions215.htm

Answer 2

Try with Mysql GROUP_CONCAT function

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Answer 3

You need some basic logic to detect when a category changes, and output appropriate html for that. e.g.

$old_cat = null;
while($row = mysqli_fetch_array(...)) {
   if ($row['category'] != $old_cat) {
       ... output category header ...
       $old_cat = $row['category'];
   }
   ... output subcategory stuff here ...
}