我有三张桌子。
base_income_tble：
id base_income
------------------------
1 Grants from government
2 other grants
3 Taxes
sub_income_tble：
id base_id sub_income
---------------------------------------
1 1 Special Grants
2 1 General Grants
3 2 Local authorities
4 2 private
5 3 Professional tax
6 3 garbage tax
7 3 light tax
inner_subincome_tble：
id sub_id inner_subincome
-------------------------------------
1 1 matching grant
2 1 XIIIth finance commission
3 1 GIA members salary Grant
4 2 Grants
5 2 Grants in Lieu
我想从三个表中获取与每个表相关的ID的值。
我尝试了以下查询：
select base_income, sub_income,inner_subincome from base_income_tble left join
sub_income_tble ON base_income_tble.id = sub_income_tble.base_id left join
inner_subincome_tble
on sub_income_tble.id = inner_subincome_tble.sub_id OR sub_income_tble.id =
inner_subincome_tble.sub_id;
获得价值：
我想以html格式显示以下数据：
Grants from Government
special Grants
Matching grant
XIIIth finance commission
GIA members salary Grant
General Grants
Grants
Grants in Lieu
other grants
Local authorities
private
如果可能的条件是，可以使用嵌套的foreach来按上述格式呈现数据。
最佳答案
我想到的第一种方法-
1）从数据库中获取所有数据的列表，包括id数字
select b.id as base_id, s.id as sub_id, i.id as inner_id, b.base_income, s.sub_income, i.inner_subincome from base_income_tble b left join sub_income_tble s on b.id = s.base_id left join inner_subincome_tble i on s.id = i.sub_id
+---------+--------+----------+------------------------+-------------------+--------------------------+
| base_id | sub_id | inner_id | base_income | sub_income | inner_subincome |
+---------+--------+----------+------------------------+-------------------+--------------------------+
| 1 | 1 | 1 | Grants from government | Special Grants | Matching grant |
| 1 | 1 | 2 | Grants from government | Special Grants | XIIIth finance comission |
| 1 | 1 | 3 | Grants from government | Special Grants | GIA members |
| 1 | 2 | 4 | Grants from government | General Grants | Grants |
| 1 | 2 | 5 | Grants from government | General Grants | Grants in Lieu |
| 2 | 3 | NULL | Other grants | Local authorities | NULL |
| 2 | 4 | NULL | Other grants | Private | NULL |
| 3 | 5 | NULL | Taxes | Professional tax | NULL |
| 3 | 6 | NULL | Taxes | Garbage tax | NULL |
| 3 | 7 | NULL | Taxes | Light tax | NULL |
+---------+--------+----------+------------------------+-------------------+--------------------------+
2）将其构建到递归数组中
$data = array();
while($r = mysqli_fetch_assoc($rows)){
$bId = 'id' . $r['base_id'];
$sId = 'id' . $r['sub_id'];
$iId = 'id' . $r['inner_id'];
if( !array_key_exists($bId, $data) )
$data[$bId] = array('title' => $r['base_income'], 'values' => array());
if( $r['sub_id'] && !array_key_exists($sId, $data[$bId]['values']) )
$data[$bId]['values'][$sId] = array('title' => $r['sub_income'], 'values' => array());
if( $r['sub_id'] && $r['inner_id'] )
$data[$bId]['values'][$sId]['values'][$iId] = array('title' => $r['inner_subincome']);
}
此后
$data数组的内容-Array
(
[id1] => Array
(
[title] => Grants from government
[values] => Array
(
[id1] => Array
(
[title] => Special Grants
[values] => Array
(
[id1] => Array
(
[title] => Matching grant
)
[id2] => Array
(
[title] => XIIIth finance comission
)
[id3] => Array
(
[title] => GIA members
)
)
)
[id2] => Array
(
[title] => General Grants
[values] => Array
(
[id4] => Array
(
[title] => Grants
)
[id5] => Array
(
[title] => Grants in Lieu
)
)
)
)
)
[id2] => Array
(
[title] => Other grants
[values] => Array
(
[id3] => Array
(
[title] => Local authorities
[values] => Array
(
)
)
[id4] => Array
(
[title] => Private
[values] => Array
(
)
)
)
)
[id3] => Array
(
[title] => Taxes
[values] => Array
(
[id5] => Array
(
[title] => Professional tax
[values] => Array
(
)
)
[id6] => Array
(
[title] => Garbage tax
[values] => Array
(
)
)
[id7] => Array
(
[title] => Light tax
[values] => Array
(
)
)
)
)
)
title元素存储收入的名称，而values存储应该显示在收入下方的条目。您现在只需要一个递归函数即可，该递归函数接受一个数组，输出标题并遍历各个值，并为每个值进行调用。 （您可能还可以使用嵌套的foreach来执行此操作）一种替代方法是将所有数据存储在单个表中。仅具有
id，parent_id和income字段，顶级条目的parent_id字段为NULL，其他所有内容都应位于相关的id之下。但是，有效显示此代码的代码与上述代码完全不同。