PHP下拉选择年份生成报表功能无输出问题排查求助
2026-9-24
按年份筛选报表无返回数据问题修复
问题背景
需实现通过下拉菜单选择年份生成对应统计报表功能，未加筛选逻辑时可正常输出所有年份的统计报表，新增年份筛选逻辑后，选择年份提交无对应数据返回。
相关代码
表单部分代码
<?php include('_header.php'); $conn = mysqli_connect("localhost","root","","ea"); $sql = "SELECT distinct YEAR(mohon_tarikh) AS tahun FROM tpermohonan order by tahun"; $result = mysqli_query($conn,$sql); ?> <tr bgcolor="#FFFFFF" align="center" valign="top"> <td bordercolor="#0c52cb"><p> </p> <table bgcolor="#2F72B9" cellpadding="0" cellspacing="1" align="center" width="72%" > <tr bgcolor="#FFFFFF" > <form class ="" action ="" name="menufrmLaporanStatTransak" method="post" target= "_blank"> <td width="12%">Pilih Tahun<font class="font18red">*</font>: </td> <td width="9%"><select class = "" name = "y"> <?php while($rows = mysqli_fetch_array($result)){ echo '<option value = "'.$rows["tahun"].'">'.$rows["tahun"].'</option>'; }?></select></td> </form> <td width="8%"><button type= "submit" name= "button" > Cari</button></td> <td width="8%"><p align="right"><a href = "#" onclick = "window.print();"><img src="img/cetak.png" alt="cetak" width="40" height="40"><br> </a><a href = "#" onclick = "window.print();">Cetak</a></td> <td width="34%" align="right"><?php date_default_timezone_set("Asia/Kuala_Lumpur"); echo "Tarikh : " .date('d-m-Y H:i:s'); //Returns IST ?></td> </tr> </table>
报表查询渲染代码
<tr> <td width="33" rowspan="3"><div align="center"><strong>BIL</strong></div></td> <td width="550" rowspan="3"><div align="center"><strong>JAWATAN</strong></div></td> </tr> <tr> <td colspan="12"><div align="center"><strong>BILANGAN TRANSAKSI </strong></div></td> <td width="136" rowspan="2"><div align="center"><strong>JUMLAH</strong></div></td> </tr> <tr> <td width="82"><div align="center"><strong>JANUARI</strong></div></td> <td width="88"><div align="center"><strong>FEBRUARI</strong></div></td> <td width="50"><div align="center"><strong>MAC</strong></div></td> <td width="60"><div align="center"><strong>APRIL</strong></div></td> <td width="51"><div align="center"><strong>MEI</strong></div></td> <td width="41"><div align="center"><strong>JUN</strong></div></td> <td width="57"><div align="center"><strong>JULAI</strong></div></td> <td width="60"><div align="center"><strong>OGOS</strong></div></td> <td width="119"><div align="center"><strong>SEPTEMBER</strong></div></td> <td width="100"><div align="center"><strong>OCTOBER</strong></div></td> <td width="117"><div align="center"><strong>NOVEMBER</strong></div></td> <td width="106"><div align="center"><strong>DISEMBER</strong></div></td> </tr> <?php if(isset($_POST['y'])) { $conn = mysqli_connect("localhost","root","","ea"); $thn = $_POST['y']; $sql = ("SELECT ROW_NUMBER() OVER(ORDER BY t.jawatan_nama) Bil, t.jawatan_nama Jawatan, COUNT(IF(month_id = 1, 1, NULL)) Jan, COUNT(IF(month_id = 2, 1, NULL)) Feb, COUNT(IF(month_id = 3, 1, NULL)) March, COUNT(IF(month_id = 4, 1, NULL)) April, COUNT(IF(month_id = 5, 1, NULL)) May, COUNT(IF(month_id = 6, 1, NULL)) June, COUNT(IF(month_id = 7, 1, NULL)) July, COUNT(IF(month_id = 8, 1, NULL)) August, COUNT(IF(month_id = 9, 1, NULL)) Sep, COUNT(IF(month_id = 10, 1, NULL)) Oct, COUNT(IF(month_id = 11, 1, NULL)) Nov, COUNT(IF(month_id = 12, 1, NULL)) Dis, COUNT(*) Total FROM ( SELECT t1.jawatan_nama, MONTH(t3.mohon_tarikh) month_id FROM kod_jawatan t1 INNER JOIN tmohonjaw t2 ON t1.jawatan_id = t2.jawatan_id INNER JOIN tpermohonan t3 ON t3.mohon_id = t2.mohon_id WHERE t3.mohon_tarikh = '{$thn}' ) t GROUP BY t.jawatan_nama"); $result=mysqli_query($conn,$sql); $count=mysqli_num_rows($result); echo "$count rekod dijumpai<br/>"; while($row=mysqli_fetch_assoc($result)){ ?> <tr> <td width="33"><div align="center"><?php echo $row['Bil']?></td> <td width="550"><?php echo $row['Jawatan']?></td> <td width="82"><div align="center"><?php echo $row['Jan']?></div></td> <td width="88"><div align="center"><?php echo $row['Feb']?></div></td> <td width="50"><div align="center"><?php echo $row['March']?></div></td> <td width="60"><div align="center"><?php echo $row['April']?></div></td> <td width="51"><div align="center"><?php echo $row['May']?></div></td> <td width="41"><div align="center"><?php echo $row['June']?></div></td> <td width="57"><div align="center"><?php echo $row['July']?></div></td> <td width="60"><div align="center"><?php echo $row['August']?></div></td> <td width="119"><div align="center"><?php echo $row['Sep']?></div></td> <td width="100"><div align="center"><?php echo $row['Oct']?></div></td> <td width="117"><div align="center"><?php echo $row['Nov']?></div></td> <td width="106"><div align="center"><?php echo $row['Dis']?></div></td> <td width="136"><div align="center"><?php echo $row['Total']?></div></td> </tr> <?php } } ?>
问题原因
- 表单结构错误：提交按钮放在
</form>标签外部，点击按钮无法触发表单提交，$_POST['y']无传值，筛选逻辑不会执行 - 筛选条件逻辑错误：
WHERE t3.mohon_tarikh = '{$thn}'是将完整日期格式的mohon_tarikh与年份数值直接对比，无法匹配到任何符合条件的记录 - 表单设置了
target="_blank"，提交后结果会在新标签页打开，原页面不会刷新展示数据
修复方案
1. 调整表单结构
将</form>移动到提交按钮所在的td之后，确保提交按钮在表单范围内：
<form class ="" action ="" name="menufrmLaporanStatTransak" method="post"> <td width="12%">Pilih Tahun<font class="font18red">*</font>: </td> <td width="9%"><select class = "" name = "y"> <?php while($rows = mysqli_fetch_array($result)){ echo '<option value = "'.$rows["tahun"].'">'.$rows["tahun"].'</option>'; }?></select></td> <td width="8%"><button type= "submit" name= "button" > Cari</button></td> </form>
2. 修正年份筛选条件
将子查询中的WHERE条件修改为提取日期的年份进行匹配：
WHERE YEAR(t3.mohon_tarikh) = '{$thn}'
可选优化
如果不需要在新标签页打开报表，删除form标签的target="_blank"属性即可。
内容的提问来源于stack exchange，提问作者NAS BS
相关产品推荐
相关产品推荐
