I have a SQL table and the name is 'table1' and table structure is below.
ID|customer_name|orders_id|
---------------------------
1 | muko | 1 |
2 | luma | 2 |
3 | muko | 2 |
4 | ceki | 8 |
5 | ceki | 10 |
6 | muko | 7 |
7 | luma | 11 |
---------------------------
etc...
How can I output them as array like this in PHP:
$customers = array ("muko"=>[1,2,7], "luma"=>[2,11], "ceki"=>[8,10] );
E.g.:
<?php
/*
DROP TABLE IF EXISTS table_a;
CREATE TABLE table_a
(id SERIAL PRIMARY KEY
,customer_name VARCHAR(12)
,order_id INT NOT NULL
);
INSERT INTO table_a VALUES
(1,'muko',1),
(2,'luma',2),
(3,'muko',2),
(4,'ceki',8),
(5,'ceki',10),
(6,'muko',7),
(7,'luma',11);
*/
require('path/to/connection/stateme.nts');
$query = "
SELECT a.id
, a.customer_name
, a.order_id
FROM table_a a
ORDER
BY a.customer_name
, a.order_id;
";
$result = mysqli_query($db,$query);
$old_array = array();
while($row = mysqli_fetch_assoc($result)){
$old_array[] = $row;
}
$new_array = array();
foreach ($old_array as $row) {
$new_array['customer_name'][$row['customer_name']][] = $row['order_id'];
}
$new_array = array_values($new_array); // reindex
print_r($new_array);
?>
Outputs:
Array
(
[0] => Array
(
[ceki] => Array
(
[0] => 8
[1] => 10
)
[luma] => Array
(
[0] => 2
[1] => 11
)
[muko] => Array
(
[0] => 1
[1] => 2
[2] => 7
)
)
)
use group by clause to get the result:
SELECT customer_name, orders_id =
STUFF((SELECT ', ' + orders_id
FROM table1 b
WHERE b.customer_name = a.customer_name
FOR XML PATH('')), 1, 2, '')
FROM table1 a
GROUP BY customer_name
