put value from a key of an array that has same certain key values

Question

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

ordernumber	articleID	customergroupID
1111	50	1
2222	61	2
2222	62	2
3333	72	3

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.

If you have to get php to iterate the data set anyhow to form subarrays in each group, I'd probably not ask SQL to use more processing resources to compact data into comma-separated strings (which may not work if the string length gets too large). — mickmackusa, Jan 13 '23 at 01:59

score 0 · Accepted Answer · answered Jan 12 '23 at 11:38

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.

put value from a key of an array that has same certain key values

1 Answers1