php - 如何从 SQL 获取数组值

Question

我有一个 SQL 表，名称为“table1”，表结构如下。

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...

如何在 PHP 中将它们输出为这样的数组：

$customers = array ("muko"=>[1,2,7], "luma"=>[2,11], "ceki"=>[8,10] );

标签： phpmysqlsqlarrays

Answer 1

例如：

<?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);

?>

输出：

Array
(
    [0] => Array
        (
            [ceki] => Array
                (
                    [0] => 8
                    [1] => 10
                )

            [luma] => Array
                (
                    [0] => 2
                    [1] => 11
                )

            [muko] => Array
                (
                    [0] => 1
                    [1] => 2
                    [2] => 7
                )

        )

)