时间戳

首页 > 解决方案 > 加入多个表并添加符号

php - 加入多个表并添加符号

问题描述

我有 2 个查询来从多个表中获取行。这些查询已经测试并返回我想要的行。

这是第一个查询。

select mst_province.provinsi as provinsi, mst_kab.kabupaten as kabupaten, form_kuesioner_pengelola.namasite as nama 
from form_kuesioner_pengelola 
join form_kuesioner_surveyor on form_kuesioner_surveyor.namalokasi = form_kuesioner_pengelola.namasite 
join mst_province on mst_province.id_prov = form_kuesioner_pengelola.idprov 
join mst_kab on mst_kab.id_prov = form_kuesioner_pengelola.idprov and mst_kab.id_kab = form_kuesioner_pengelola.idkab 
group by form_kuesioner_pengelola.namasite 
order by form_kuesioner_pengelola.idprov, form_kuesioner_pengelola.idkab

我在表格中显示它们并添加符号

echo "<tr> <th>No.</th> <th>Provinsi</th> <th>Kabupaten</th> <th>Namasite</th> <th>Form Manfaat</th> <th>Form Pengelola</th> <th>Form Surveyor</th> <th>status</th>";

while($row = mysqli_fetch_array($rdata)) {
    echo "<tr>";
    echo '<td>' . $no . '</td>';
    echo '<td>' . $row['provinsi'] . '</td>';
    echo '<td>' . $row['kabupaten'] . '</td>';
    echo '<td>' . $row['nama'] . '</td>';
    echo '<td>&#9989;</td>';
    echo '<td>&#9989;</td>';
    echo '<td>&#9989;</td>';
    echo '<td>complete</td>';

    echo "</tr>";
    $no++;
}

第二个

select mst_province.provinsi as provinsi, mst_kab.kabupaten as kabupaten, form_kuesioner_pengelola.namasite as nama 
from form_kuesioner_pengelola 
join mst_province on mst_province.id_prov = form_kuesioner_pengelola.idprov 
join mst_kab on mst_kab.id_prov = form_kuesioner_pengelola.idprov and mst_kab.id_kab = form_kuesioner_pengelola.idkab 
group by form_kuesioner_pengelola.namasite 
order by form_kuesioner_pengelola.idprov, form_kuesioner_pengelola.idkab

我在下表中显示它们 

echo "<tr> <th>No.</th> <th>Provinsi</th> <th>Kabupaten</th> <th>Namasite</th> <th>Form Manfaat</th> <th>Form Pengelola</th> <th>Form Surveyor</th> <th>status</th>";

while($rowtable = mysqli_fetch_array($rdatatable)) {
    echo "<tr>";
    echo '<td>' . $notable . '</td>';
    echo '<td>' . $rowtable['provinsi'] . '</td>';
    echo '<td>' . $rowtable['kabupaten'] . '</td>';
    echo '<td>' . $rowtable['nama'] . '</td>';  
    echo '<td>&#9989;</td>';
    echo '<td>&#10006;</td>';
    echo '<td>&#10006;</td>';

    echo '<td>visited</td>';

    echo "</tr>";
    $notable++;
}

问题是他们用自己的表格打印，并以这种方式返回我 2 个表格。如何将它们连接在一起，以便我有 1 个基于他们自己的表的具有不同符号的表。
谢谢

标签： phpmysqlsql

解决方案

您可以使用组合这两个查询UNION。

SELECT *
FROM (
    select 'complete' as which, mst_province.provinsi as provinsi, mst_kab.kabupaten as kabupaten, form_kuesioner_pengelola.namasite as nama, form_kuesioner_pengelola.idprov, form_kuesioner_pengelola.idkab
    from form_kuesioner_pengelola 
    join form_kuesioner_surveyor on form_kuesioner_surveyor.namalokasi = form_kuesioner_pengelola.namasite 
    join mst_province on mst_province.id_prov = form_kuesioner_pengelola.idprov 
    join mst_kab on mst_kab.id_prov = form_kuesioner_pengelola.idprov and mst_kab.id_kab = form_kuesioner_pengelola.idkab 
    group by form_kuesioner_pengelola.namasite 

    UNION ALL

    select 'visited' as which, mst_province.provinsi as provinsi, mst_kab.kabupaten as kabupaten, form_kuesioner_pengelola.namasite as nama, form_kuesioner_pengelola.idprov, form_kuesioner_pengelola.idkab
    from form_kuesioner_pengelola 
    join mst_province on mst_province.id_prov = form_kuesioner_pengelola.idprov 
    join mst_kab on mst_kab.id_prov = form_kuesioner_pengelola.idprov and mst_kab.id_kab = form_kuesioner_pengelola.idkab 
    group by form_kuesioner_pengelola.namasite 
) AS u
order by u.idprov, u.idkab

然后你可以用它$row['which']来知道是放在最后两列&#9989;还是&#10006;放在最后两列。

echo "<tr> <th>No.</th> <th>Provinsi</th> <th>Kabupaten</th> <th>Namasite</th> <th>Form Manfaat</th> <th>Form Pengelola</th> <th>Form Surveyor</th> <th>status</th>";

while($row = mysqli_fetch_array($rdata)) {
    echo "<tr>";
    echo '<td>' . $no . '</td>';
    echo '<td>' . $row['provinsi'] . '</td>';
    echo '<td>' . $row['kabupaten'] . '</td>';
    echo '<td>' . $row['nama'] . '</td>';
    echo '<td>&#9989;</td>';
    echo '<td>' . ($row['which'] == 'complete' ? '&#9989;' : '&#10006;') . '</td>';
    echo '<td>' . ($row['which'] == 'complete' ? '&#9989;' : '&#10006;') . '</td>';
    echo '<td>' . $row['which'] . '</td>';

    echo "</tr>";
    $no++;
}

如果您只想返回所有内容一次，Complete或者Visited取决于它们是否在form_kuesioner_surveyor表中，您应该使用 aLEFT JOIN与该表。然后您可以测试表中的列是否为空，以了解是否存在匹配项。

select IF(form_kuesioner_surveyor.namalokasi IS NULL), 'visited', 'complete') as which, mst_province.provinsi as provinsi, mst_kab.kabupaten as kabupaten, form_kuesioner_pengelola.namasite as nama
from form_kuesioner_pengelola 
join mst_province on mst_province.id_prov = form_kuesioner_pengelola.idprov 
join mst_kab on mst_kab.id_prov = form_kuesioner_pengelola.idprov and mst_kab.id_kab = form_kuesioner_pengelola.idkab 
left join form_kuesioner_surveyor on form_kuesioner_surveyor.namalokasi = form_kuesioner_pengelola.namasite     
group by form_kuesioner_pengelola.namasite 
order by form_kuesioner_pengelola.idprov, form_kuesioner_pengelola.idkab

推荐阅读