PHP和MySQL连接2个数据表

Question

我有表 tb_satker

kode_propinsi | nama_satker  |  kode_satker
500           | A            |  1
500           | B            |  2
500           | C            |  3
500           | D            |  4

还有表 tb_upload

kode_propinsi | kode_satker  |  month
500           | A            |  1
500           | A            |  2
500           | B            |  3

我想创建 php 和 mysql 代码来生成这样的表

No | UPT | Jan | Feb | Mar | Apr | Mei | Jun | Jul | Ags | Sep | Okt | Nov | Des
1  | A   | 1   | 2   | 0   | 0   | 0   | 0   | 0   |  0  | 0   | 0   | 0   | 0
2  | B   | 0   | 0   | 3   | 0   | 0   | 0   | 0   |  0  | 0   | 0   | 0   | 0
3  | C   | 0   | 0   | 0   | 0   | 0   | 0   | 0   |  0  | 0   | 0   | 0   | 0
4  | D   | 0   | 0   | 0   | 0   | 0   | 0   | 0   |  0  | 0   | 0   | 0   | 0

这是我的 php 代码，它可以创建主表，实时代码位于 link

但是如何制作带有非事件图像的表格，以显示是否没有值(0)和事件图像，如果月份填充了值(1/2/3/4...等)

<table class="table table-hover">
<thead>
    <tr>
        <th>No</th>
        <th>UPT</th>
        <th>Jan</th>
        <th>Feb</th>
        <th>Mar</th>
        <th>Apr</th>
        <th>Mei</th>
        <th>Jun</th>
        <th>Jul</th>
        <th>Ags</th>
        <th>Sep</th>
        <th>Okt</th>
        <th>Nov</th>
        <th>Des</th>
    </tr>
</thead>
<tbody>
<?
$view = "SELECT * FROM $tb_satker WHERE kode_propinsi='$propinsi'";
$result = mysql_query($view);
$jumlah=mysql_num_rows($result);
$nomor=0;
while
($baris=mysql_fetch_array($result))
{
$namasatker=$baris['nama_satker'];
$kodesatker=$baris['kode_satker'];
$kodepropinsi=$baris['kode_propinsi'];
$nomor=$nomor+1;
?>
    <tr>
        <th><? echo $nomor;?></th>
        <th><? echo $namasatker;?></th>
        <th><img src="images/inactive.png"></th>
        <th><img src="images/inactive.png"></th>
        <th><img src="images/inactive.png"></th>
        <th><img src="images/inactive.png"></th>
        <th><img src="images/inactive.png"></th>
        <th><img src="images/inactive.png"></th>
        <th><img src="images/inactive.png"></th>
        <th><img src="images/inactive.png"></th>
        <th><img src="images/inactive.png"></th>
        <th><img src="images/inactive.png"></th>
        <th><img src="images/inactive.png"></th>
        <th><img src="images/inactive.png"></th>
<? }?>
    </tr>
</tbody>
</table>

Answer 1

您可以使用此查询:

SELECT a.id,a.code,a.name,
max(case when b.month=1 then 1 else "-" end) as month1,
max(case when b.month=2 then 1 else "-" end) as month2,
max(case when b.month=3 then 1 else "-" end) as month3
FROM `table_a` as a
left join table_b as b on a.name= b.name
group by a.name