Display multiple rows as an array

Question

I want to be able to display a table with a list of people in one column and a string of their orders in another.

People
========
person_id
fname
lname

Orders
========
order_id
person_id
product_id

Products
=========
product_id
productname

I have the following code:

SELECT people.fname, people.lname, products.productname
FROM people
INNER JOIN orders ON people.person_id = orders.person_id
INNER JOIN products ON products.product_id = orders.product_id
ORDER BY lname
...
$rows = $result->num_rows;
while($row = $result->fetch_assoc()) {
    echo "<tr><td>Edit</td>
    <td>".$row["lname"].", ".$row['fname']."</td>
    <td>".$row['productname']."</td></tr>";
    }

You're probably looking for GROUP_CONCAT on productname in your query. — Daan
– Daan, Commented Apr 29, 2015 at 13:48
I added the SQL. Could you explain the GROUP_CONCAT a little more? I've having trouble finding out how to use it in this situation. (Thank you) — David Mancini
– David Mancini, Commented Apr 29, 2015 at 14:23

robbmj · Accepted Answer · 2015-04-29 14:30:30Z

1

In MySQL, you have to use the GROUP_CONCAT function. For example something like this:

SELECT CONCAT(LNAME,", ",FNAME), GROUP_CONCAT(PRODUCTNAME)
FROM PEOPLE P, PRODUCTS PR, ORDERS O
WHERE P.PERSON_ID = O.PERSON_ID
AND O.PRODUCT_ID = PR.PRODUCT_ID
GROUP BY (LNAME, FNAME);

edited Apr 29, 2015 at 14:30

robbmj

16.6k8 gold badges41 silver badges63 bronze badges

answered Apr 29, 2015 at 13:52

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

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

4 Comments

David Mancini Over a year ago

I have tried it, but I'm getting a Parse error. It's rejecting the " ," between lname and fname

Ankit Bajpai Over a year ago

I have run this on Mysql it is not giving me an error. Can you tell me wat exact error you are getting.

David Mancini Over a year ago

Ok, I was able to fix the parse error. GROUP_CONCAT is now listing one single row with every product on it. It's not using the loop. In my example, it's giving me: Smith, Bob | Item 1, Item 2, Item 3, Item 2. It's not giving me a row for Jill, and it put her Item 2 attached to Bob's.

David Mancini Over a year ago

With so maneuvering, I was able to get this solution to work. Thanks!

Yoluk · Accepted Answer · 2015-04-29 14:00:32Z

1

Php oriented solution :

You could store your product names in an array, using the person's name as a key :

$rows = $result->num_rows;

$data = array();
while($row = $result->fetch_assoc())
{
     $name = $row["lname"].", ".$row['fname'];
     $data[$name][] = $row['productname'];
}

Then, you'll just have to loop through this array :

foreach($data as $name => $products)
{
     echo "<tr><td>Edit</td><td>".$name."</td>";
     foreach($products as $productname)
     {
         echo "<td>".$productname."</td>";
     }
     echo "<tr>";
}

answered Apr 29, 2015 at 14:00

Yoluk

3101 silver badge10 bronze badges

Collectives™ on Stack Overflow

Display multiple rows as an array

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related