mysql select all, group by orderid

I was wondering if anyone could help me with selecting information from my table, but grouping the results depending on the order id. I'm sure this is quite simple, but I can't seem to get the code working.

Here's my attempt - which is only showing 1 result, instead of 6:

• 4 results with orderid 55542
• 2 results with orderid 55543

SQL:

SELECT *
FROM #__users_orders
WHERE userid = 22 
GROUP BY orderid 
ORDER BY date DESC

Any help would be appreciated :)

EDIT:

I'd like to acheive this (or something similar)

Array[55542]
(
    [0] => stdClass Object
        (
            [id] => 6
            [userid] => 66
            [orderid] => 55542
            [date] => 2011-08-05 16:30:24
            [code] => 121021
            [title] => 7 STAR CHICKEN A/KING 71198 1.3KG
            [units] => 2
            [ctns] => 
        )

    [1] => stdClass Object
        (
            [id] => 1
            [userid] => 66
            [orderid] => 55542
            [date] => 2011-08-05 16:06:12
            [code] => 302371
            [title] =>  ANCHOVY FILL 730GM
            [units] => 2
            [ctns] => 
        )

    [2] => stdClass Object
        (
            [id] => 6
            [userid] => 66
            [orderid] => 55542
            [date] => 2011-08-05 16:30:24
            [code] => 121021
            [title] => 7 STAR CHICKEN A/KING 71198 1.3KG
            [units] => 2
            [ctns] => 
        )

    [3] => stdClass Object
        (
            [id] => 1
            [userid] => 66
开发者_开发技巧            [orderid] => 55542
            [date] => 2011-08-05 16:06:12
            [code] => 302371
            [title] =>  ANCHOVY FILL 730GM
            [units] => 2
            [ctns] => 
        )

)

Array[55543]
(
    [0] => stdClass Object
        (
            [id] => 6
            [userid] => 66
            [orderid] => 55543
            [date] => 2011-08-05 16:30:24
            [code] => 121021
            [title] => 7 STAR CHICKEN A/KING 71198 1.3KG
            [units] => 2
            [ctns] => 
        )

    [1] => stdClass Object
        (
            [id] => 1
            [userid] => 66
            [orderid] => 55543
            [date] => 2011-08-05 16:06:12
            [code] => 302371
            [title] =>  ANCHOVY FILL 730GM
            [units] => 2
            [ctns] => 
        )

)

---

SELECT *
FROM #__users_orders
WHERE userid = 22 
ORDER BY orderid DESC

Just select your items like this and create your object/array hierarchy in the frontend by iterating over the results and creating a new array for every new orderid that comes by.

---

SELECT orderid, COUNT(*)
FROM #__users_orders
WHERE userid = 22 
GROUP BY orderid 
ORDER BY date DESC

Normally, you have to use an aggregate (eg COUNT, SUM) and GROUP BY matched. So that columns in the SELECT but not in the COUNT or SUM are in the GROUP BY

Only MySQL allows you to not follow this rule. Other DB engines would give an error.

---

the query seems ok, maybe your extracting the results the wrong way

SELECT * FROM table GROUP BY field1;

should return same number of rows than

SELECT field1, field2 FROM table GROUP BY field1;

but different number than

SELECT * FROM table;

---

You should not select "*" in this query.

When you group by "some columns". You can only select "some columns" or some_aggregate_function(other columns).

e.g. If you want to get the aggregate ordersize and latest date for each order id, you would do something like -

SELECT orderid, sum(ordersize), max(date) FROM #__users_orders WHERE userid = 22 GROUP BY orderid ORDER BY max(date) DESC