php - 选择3个数组字段中的3个表1个mysql查询

Question

首先是我的表格

我已将产品的格式/模板数据存储在第一个表中。一种格式/模板可以被许多产品使用。

Format meta table. Primary Key is format_id. (has some more other columns)
+-----------+-------+
| format_id | title |
+-----------+-------+
|     1     |  f.a  |
|     2     |  f.b  |
|     3     |  f.c  |
|     4     |  f.d  |
+-----------+-------+

在第二个表中，我存储产品数据。每个产品都有一个唯一的 ID，并且仅使用一种唯一的格式/模板。该表包含产品价格、产品数量等数据。每个产品的详细信息都保存在另一个表中。

Product meta table. Primary Key is product_id. (has some more other columns)
+-----------+------------+-------+
| format_id | product_id | title |
+-----------+------------+-------+
|     1     |      1     |  p.a  |
|     1     |      2     |  p.b  |
|     1     |      3     |  p.c  |
|     1     |      4     |  p.d  |
+-----------+------------+-------+

这是我保存产品详细信息的最终表。这里的值存储在 cell_id => 值对中。

Details table. No primary key. (has some more other columns)
+-----------+------------+---------+-------+
| format_id | product_id | cell_id | value |
+-----------+------------+---------+-------+
|     1     |      1     |    1    |  d.a  |
|     1     |      1     |    2    |  d.b  |
|     1     |      1     |    3    |  d.c  |
|     1     |      1     |    4    |  d.d  |
+-----------+------------+---------+-------+

现在是我想要的结果

$result => ['format_meta']  => ['format_id'] = 1
                            => ['title']     = f.a

           ['product_meta'] => ['format_id']  = 1
                            => ['product_id'] = 1
                            => ['title']      = p.a

           ['details']      => [0]   => ['format_id']  = 1
                                     => ['product_id'] = 1
                                     => ['cell_id']    = 1
                                     => ['value']      = d.a

                            => [1]   => ['format_id']  = 1
                                     => ['product_id'] = 1
                                     => ['cell_id']    = 2
                                     => ['value']      = d.b

                            => [2]   => ['format_id']  = 1
                                     => ['product_id'] = 1
                                     => ['cell_id']    = 3
                                     => ['value']      = d.c

我完全不知道如何做到这一点。

Answer 1

首先，无论您如何编写 SQL 语句，据我所知和所见，您都将获得您请求的所有列的单行/值数组；它不会以该多维数组的格式返回。这是您必须根据收到的 SQL 响应对象制作的东西。要获取所有列数据，您需要使用 JOINS 编写 SQL 语句

SELECT f.title as formatTitle, p.*, d.cellid, d.value FROM Product as p JOIN  Format as f ON f.format_id = p.format_id JOIN Details as d ON p.product_id = d.product_id WHERE p.product_id = ?

此后，您可以根据结果创建上述数组