CODEWITHSUNDEEP
phpmysql
Peter
Peter

Reputation: 1916

Mysql query to join two table and group by matching id

Am trying to group user orders by the order id, but am not getting it right i know this will be first get done in the SQL query then organise it well in PHP and HTML but i don't know how to get it done.

orderinfo

oid  | userid  | total  | payid
-----|---------|--------|----------
oi10 | peter   | 650    | VCC-100
oi12 | john    | 30     | VCC-500
oi15 | peter   | 60     | COD-500

itemorder

pid | ioid  | userid  | price  | qty | itemname
----| ------|---------|--------|-----|-----------
p10 | oi10  | peter   | 200    | 1   | lexus
p20 | oi10  | peter   | 150    | 1   | Toyota
p15 | oi10  | peter   | 300    | 1   | Myvi
p66 | oi15  | peter   | 25     | 2   | BMW
p67 | oi15  | peter   | 10     | 1   | Saga
p67 | oi12  | john    | 10     | 3   | Saga

My current Code

$handler->prepare('
    SELECT * FROM itemorder io
    LEFT JOIN orderinfo oi
    ON io.oid = oi.ioid
    WHERE io.userid = 'peter' 
    GROUP BY io.oid
    ORDER BY oi.pid DESC
');
$handler->execute();
$RecentOrder = $handler->getAll();
$handler->free();
    if(!empty($RecentOrder)){
        foreach($RecentOrder as $row){

        }
    }

Expected Result

I want the result to be sorted according to the order id all item that has same order id will be listed (list according to order id).

oid: oi10
  --------
    lexus
    Toyota
    Myvi
---------------------
oid: oi15 
   --------
    BMW
    Saga

Upvotes: 0

Views: 75

Answers (2)

Smit Mehta
Smit Mehta

Reputation: 196

You can try this :

SELECT io.ioid,GROUP_CONCAT("",io.itemname) as order_items FROM itemorder as io
        LEFT JOIN orderinfo as oi
        ON io.ioid = oi.oid
        WHERE io.userid = 'peter' 
        GROUP BY io.ioid
    ORDER BY io.pid DESC

Please not the columns on which join is done.

Then in PHP you can use explode function to get the array of names for each order.

Hope this helps!

Upvotes: 0

winkbrace
winkbrace

Reputation: 2711

The desired output can be retrieved with just ORDER BY.

SELECT *
...
ORDER BY oi.oid DESC, io.pid DESC

And then do the specific formatting in PHP. Easiest way is probably to remember the order_id of the last row.

$lastOrderId = null;
foreach ($result as $row) {
    if ($lastOrderId != $row['oid']) {
        echo 'oid: ' . $row['oid'] . PHP_EOL;
        echo '  -----------' . PHP_EOL;
    }
    echo '     ' . $row['itemname'] . PHP_EOL;

    $lastOrderId = $row['oid'];
}

Upvotes: 1

Related Questions