ive been searching through the internet and cant find a solution for this. I have this table from a query

it comes from this query

$orderTable = 'SELECT DISTINCT so.`ordernumber`, sod.`articleID`, scc.`id` AS "customergroupID" FROM `s_order` so
                                            INNER JOIN `s_order_details` sod
                                            ON so.`id` = sod.`orderID`
                                            INNER JOIN `s_order_attributes` soa
                                            ON so.`id` = soa.`orderID`
                                            INNER JOIN `s_user` su
                                            ON so.userID = su.`id`
                                            INNER JOIN `s_core_customergroups` scc
                                            ON su.`customergroup` = scc.`groupkey`
                                            WHERE soa.`twp_insert_article` = 0 
                                            AND so.ordernumber != 0
                                            AND so.ordertime > DATE_SUB(NOW(), INTERVAL 21 day)'

dumping the query will output the following array: $orderTable

array(4) {
  [0]=>
  array(3) {
    ["ordernumber"]=> int(1111)
    ["articleID"]=> int(50)
    ["customergroupID"]=> int(1)
  }
  [1]=>
  array(3) {
    ["ordernumber"]=> int(2222)
    ["articleID"]=> int(61)
    ["customergroupID"]=> int(2)
  }
  [2]=>
  array(3) {
    ["ordernumber"]=> int(2222)
    ["articleID"]=> int(62)
    ["customergroupID"]=> int(2)
  }
  [3]=>
  array(3) {
    ["ordernumber"]=> int(3333)
    ["articleID"]=> int(50)
    ["customergroupID"]=> int(1)
  }
}

now what i would want my array to look like the following :

   array(3) {
        ['0']=> array(3) {
            ["ordernumber"]=> int(1111)
            ["articleID"]=> array(1){
                [0]=> int(50)
                }
            ["customergroupID"]=> int(1)
            }
        [1]=> array(3) {
            ["ordernumber"]=> int(2222)
            ["articleID"]=> array(2) {
                [0]=> int(61)
                [1]=> int(62)
                }
            ["customergroupID"]=> int(1)
            }
        [2]=> array(3) {
            ["ordernumber"]=> int(3333)"
            ["articleID"]=> array(1){
                        [0]=> int(50)
                        }
            ["customergroupID"]=> int(3)
            }
    }

i made the array as php so u dont have to make it:

$orderTable =  [0 => ["ordernumber" => 1111, "articleID" => 50, "customergroupID" => 1],
          1 => ["ordernumber" => 2222, "articleID" => 61, "customergroupID" => 2],
          2 => ["ordernumber" => 2222, "articleID" => 62, "customergroupID" => 2],
          3 => ["ordernumber" => 3333, "articleID" => 50, "customergroupID" => 1]
          ];

Basically want to put "articleID" in same array that belong to same "ordernumber"

Anything would help, thanks.

CodePudding user response：

In SQL that would be using a group_concat and group_by.

SELECT 
    so.`ordernumber`, 
    GROUP_CONCAT(sod.`articleID`) as articleID, 
    scc.`id` AS "customergroupID" 
FROM `s_order` so
INNER JOIN `s_order_details` sod ON so.`id` = sod.`orderID`
INNER JOIN `s_order_attributes` soa ON so.`id` = soa.`orderID`
INNER JOIN `s_user` su ON so.userID = su.`id`
INNER JOIN `s_core_customergroups` scc ON su.`customergroup` = scc.`groupkey`
WHERE 
    soa.`twp_insert_article` = 0 
    AND so.ordernumber != 0
    AND so.ordertime > DATE_SUB(NOW(), INTERVAL 21 day)
GROUP BY so.`ordernumber`

That would give you a comma seperated result of articleID's. In php you can use explode() function to convert that to an array.