Cuppy
Cuppy

Reputation: 113

Grouping SQL query by userid

I have a orders table with the following columns:

-userid
-orderid (primary key)
-date
-name
-qty

So I would like to display in tables in a summary page all orders and group them under each userid. So it would be like

Userid: 0001
| Orderid | Name   | QTY |
|  1001   | Item A | 10  |
|  1002   | Item B | 5   |

Userid: 0003
| Orderid | Name   | QTY |
|  1003   | Item C | 6   |
|  1004   | Item C | 7   |

So far I've experimented and got:

  1. display in a single table all the items ordered by userid
  2. with a GROUP BY userid, I've managed to create as many tables as there are different userid

But I can't seem to combine the two into the desired results and would really appreciate some advice on what I am doing wrong.

Thanks in advance and I hope my explanation made sense!

Upvotes: 0

Views: 285

Answers (1)

KevInSol
KevInSol

Reputation: 2644

I think you will need to do this in PHP

Query with

select * from orders order by userid, orderid

In PHP something like this psudo code

$this_user_id=0;

for (each order line)
{
get fields from db result;
if ($this_user_id!=$userid)
{
$this_user_id=$orderid// save change of userid flag
print user heading;
}

print order lines;
}

Upvotes: 1

Related Questions