CODEWITHSUNDEEP
phpsqlarrays
Saedawke
Saedawke

Reputation: 471

How to group PHP array result

I have table which have these fields patchno, Project_no Item_Desc Quantity Store Action And currently it looks like this:

enter image description here

I expect to be as this:

enter image description here

Every patch_no must have unique project_no and store other two columns are variety. My array variable which is PHP if I use print_r($orders) it outputs this.

Array ( [0] => stdClass Object ( [ID] => 1 [patchno] => 1 [item_id] => 1 [Quantity] => 10 [store_id] => 1 [project_id] => 1 [user_id] => 1 [order_date] => 2015-10-22 14:55:51 [Desc] => Jiingado [Cost] => 6.60 [store_name] => Nuux [Telephone] => 565656 [email] => [email protected] [address] => Gacanlibaax [Project_No] => 466 [Site] => A [owner] => Gaboose [foreman] => Axmed Diiriye ) [1] => 
 stdClass Object ( [ID] => 1 [patchno] => 1 [item_id] => 3 [Quantity] => 2 [store_id] => 1 [project_id] => 1 [user_id] => 1 [order_date] => 2015-10-22 14:55:51 [Desc] => Marmar [Cost] => 1.00 [store_name] => Nuux [Telephone] => 565656 [email] => [email protected] [address] => Gacanlibaax [Project_No] => 466 [Site] => A [owner] => Gaboose [foreman] => Axmed Diiriye ) [2] => stdClass Object ( [ID] => 1 [patchno] => 1000002 [item_id] => 2 [Quantity] => 21 [store_id] => 1 [project_id] => 1 [user_id] => 1 [order_date] => 2015-10-24 15:34:52 [Desc] => Masaabiir [Cost] => 2.00 [store_name] => Nuux [Telephone] => 565656 [email] => [email protected] [address] => Gacanlibaax [Project_No] => 466 [Site] => A [owner] => Gaboose [foreman] => Axmed Diiriye ))

the query that outputs these value is this:

SELECT orders.*, items.*, stores.*, projects.*
            FROM orders
            join items on items.ID=orders.item_id
            join stores on stores.ID=orders.store_id
            join projects on projects.ID=orders.project_id

Upvotes: 0

Views: 93

Answers (1)

FuzzyTree
FuzzyTree

Reputation: 32392

Use group_concat to get a comma separated string of all the "variety" values in the group and the unique values of the group should be specified in your group by:

select patchno, Project_no, group_concat(Item), group_concat(Quantity), store
from orders
join items on items.ID=orders.item_id
join stores on stores.ID=orders.store_id
join projects on projects.ID=orders.project_id
group by patchno, project_no, store

Upvotes: 1

Related Questions