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

Im only getting one subCat_name being outputted, there should be another three being displayed, it seems that it isn't iterating over that specific category correctly. Im not sure if my query is right or its the PHP. I'll try put up a screen shot so you can understand — Urban
– Urban, Commented Jan 23, 2014 at 15:08
So not everything that should be output is being output? Try running your sql in mysql (either using mysql workbench or the command line) and see what this gives you. — elitechief21
– elitechief21, Commented Jan 23, 2014 at 15:09
Personally, I would look at what information the SQL statement is returning first and then determine how I'm going to use it. I use phpmyadmin all the time to run my queries so that I know what data is being retrieved and then I use that to build what I need to build. — Vince
– Vince, Commented Jan 23, 2014 at 15:12
Im getting what I want with the query ok, but some reason it is not repeating secondary row information when I do GROUP BY category_name. This is the sqlfiddle sqlfiddle.com/#!2/faae9/4 — Urban
– Urban, Commented Jan 23, 2014 at 15:20

Marc B · Accepted Answer · 2014-01-23 15:09:24Z

2

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 ...
}

answered Jan 23, 2014 at 15:09

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

Comments

mcendon · Accepted Answer · 2014-01-23 15:13:13Z

1

Try with Mysql GROUP_CONCAT function

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

answered Jan 23, 2014 at 15:13

mcendon

111 bronze badge

2 Comments

Barmar Over a year ago

How would that produce his desired output? His question talks about putting multiple rows in one column, but his example doesn't actually do that.

Urban Over a year ago

dropbox.com/s/ubybm3y8bq8uj5n/… - thats what im currently getting and this is what im trying for dropbox.com/s/wc2mprjg0oi7e1l/…

Community · Accepted Answer · 2017-05-23 11:56:04Z

0

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

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Jan 23, 2014 at 15:11

Phillip

4571 gold badge4 silver badges13 bronze badges

6 Comments

Phillip Over a year ago

No problem, glad to help! If this is a solution you're going to use, don't forget to click the check mark and up vote the answer. If any other answer was helpful, up vote it as well (if/when you can).

Phillip Over a year ago

Updated for MySQL, let me know if it helps.

Urban Over a year ago

Will try this shortly Phil, just reading around it at the minute, thanks for the extended help!

Urban Over a year ago

I was getting a generated error too. Error* right syntax to use near '|| GROUP_CONCAT((sub_category.subCat_name || subcat_description separa' at line 3

Phillip Over a year ago

Hmmm, try removing seperator CHAR(13,10). It'll separate the items by a comma. Change the line to Replace(Group_Concat(...), ',', CHAR(13,10)). If char(13,10) also gives you trouble, use '\n' or '\n\r'. You might have to do this in your editor instead of SQLFiddle. Or maybe an MySQL expert can clarify some of this for you.

|

Collectives™ on Stack Overflow

Display multiple rows in one column Mysql and PHP

3 Answers 3

Comments

2 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related