PHP MYSQL链接式下拉菜单如何显示数据

首先有几个建议：

• mysql贬值，您应该转到PDO准备的语句或mysqli。您可以查阅php手册
• 注释您的代码，除非您阅读从上到下

现在进入您的代码：

<?php
 // Start document with includes needed, header, connection, functions.
 require_once('../includes/header.php');
 require_once('../includes/connection.php');
 include('../includes/get_username.php');   
 include('sanitise.php');
// Start with the month variable posted from $_GET, this will begin the initial display.
$month = sanitise($_GET['month']);
?>
<!-- Begin Table Display and Layout -->
<table id="contentbox">
<tr>
<td>
<?php // Open PHP Tag to get variables to display in the table
$color="1";
// Initial Query to get the data for the month passed by GET, ordering by dv_id.
$qry = mysql_query("SELECT * FROM tbl_dv WHERE monthname(date_added) = '$month' ORDER BY dv_id DESC");
// Format the table rows for display
echo "<table class='dvr_table' id='alternatecolor' width='100%'>
<tr>
<th>DATE ADDED</th>
<th>CT</th>
<th>PAYEE</th>
<th>PARTICULAR</th>
<th>PM</th>
<th>VOUCHER NO.</th>
<th>NET</th>
<th>OBR NO.</th>
<th>";
// Fill the SELECT option with all the RESPO/Office data
$sql = "SELECT respo FROM tbl_dv WHERE monthname(date_added) = '$month' GROUP BY respo";
$result = mysql_query($sql);
// Display the select with the newly populated content
echo "<select name='respo'>
<option value=' ' disabled='disabled' selected='selected'>Select a RESPO/Office</option>";
// While the result contains data we will display respo as a select option.
while ($row = mysql_fetch_array($result)) {
echo "<option value='" . $row['respo'] . "'>" . $row['respo'] . "</option>";
}
// Finish the table header
echo "</th>
<th>ACCOUNT CODE</th>
<th>FPP CODE</th>
<th>DEDUCTION</th>
</tr>";
// While we have data from the original query we will display it
while ($row = mysql_fetch_array($qry))  {
$dv_id = $row['dv_id']; // grab the dv_id
if($color==1){ // Setup color scheme
echo "<tr bgcolor='#ffffff'> // Color for the row
// Let's begin filling the table with data
<td style='text-align:center;'>" .date_format(date_create($row['date_added']), 'm/d/y')." </td>
<td style='text-align:center;'>" .$row['cashtype']."</td>
etc.......
$color="2";
}   else {
echo "<tr bgcolor='#ebeaea'>
<td style='text-align:center;'>" .date_format(date_create($row['date_added']), 'm/d/y')." </td>
<td style='text-align:center;'>" .$row['cashtype']."</td>
etc......
$color="1";
}
}
echo "</tr>";
?>

查看以上代码后，很明显，您还没有设置查询来根据所选选项筛选显示的数据。

您的第一个查询：

// Initial Query to get the data for the month passed by GET, ordering by dv_id.
$qry = mysql_query("SELECT * FROM tbl_dv WHERE monthname(date_added) = '$month' ORDER BY dv_id DESC");

您可以为所选选项附加一个新变量，展开WHERE语句。WHERE monthname(date_added) = '$month' && respo = '$respo'，并将此查询移动到select选项之后，以便它可以访问select选项选择的值。最后，除非您要使用jQuery来处理选择框更改值，否则您需要一个提交按钮来显示选择选项的状态更改。

伪代码：

• 检查提交

• 更改查询WHERE子句中部门的值

• 显示请求的数据

  这应该会让你朝着你需要的方向前进，以达到预期的结果。

编辑：

在你发布的第三个答案中：

$viewrecord = "SELECT * FROM tbl_dv WHERE respo='".mysql_real_escape_string($respo)."' AND year(date_added)='$year' GROUP BY date_added  ";

您说它不是按传入月份进行筛选，也没有将它添加到查询中，特别是WHERE子句中。所以应该是：

$viewrecord = "SELECT * FROM tbl_dv WHERE respo='".mysql_real_escape_string($respo)."' AND month(month_added)='$month' AND year(date_added)='$year' GROUP BY date_added  ";

最终版本：

您的第一个问题源于没有将$_GET['date_added']值发送到视图页面，因此您永远不会显示任何内容正确格式化选择框，并从数据库中添加RESP和Date数据的数据:

echo "<option value='". $row['respo'] . "+". $row['date_added'] . "'>" . $row['respo'] . "  (".$row['count(*)'].")</option>";

现在我们正在传递respo和日期字符串，我们可以在下一页上处理数据：

/* UNCOMMENT NEXT LINE FOR ERROR DETECTION */
//error_reporting( E_ALL );
/* GET THE ENTIRE STRING PASSED FROM THE SELECT OPTION */
$respo = $_GET['respo'];
/* EXPLODE THE DATA BY + SYMBOL TO GET THE DATA */
$data = explode("+", $respo);
/* FORMAT THE DATETIME FROM THE STRING TO A FRIENDLY FORMAT */
$month = date("m", strtotime($data[1])) . "<br />";
$year = date("Y", strtotime($data[1])) . "<br />";

这就是我选择处理数据并获得所需信息的方式，所以现在的查询是：

$viewrecord = "SELECT * FROM tbl_dv WHERE respo='".mysql_real_escape_string($data[0])."' && year(date_added)='$year' && month(date_added)='$month' ";

这将只显示通过$_GET传递到页面中的RESPO and the date by year and month的记录。

<?php require_once('../includes/header.php');?>
<?php
$respo = $_GET['respo'];
$month = (int) (!empty($_GET['date_added']) ? $_GET['date_added'] : date('m'));
$year = (int)  (!empty($_GET['date_added']) ? $_GET['date_added'] : date('Y'));
//database call here
$viewrecord = "SELECT * FROM tbl_dv WHERE respo='".mysql_real_escape_string($respo)."' AND year(date_added)='$year' GROUP BY date_added  ";
$result = mysql_query($viewrecord, $db) or die (mysql_error());
$num_result = mysql_num_rows($result);
{
echo "<table border='1' width='100%' style='border:1px solid silver' cellpadding='5px' cellspacing='0px'>
<tr bgcolor='#666666' style='color:#FFFFFF'>
<th>Date Encoded</th>
etc...... (header here)
while ($row = mysql_fetch_row($result)) {
echo '<tr>';
echo "<tr ><td align='center'>" .date_format(date_create($row[17]), "m/d/y")."</td>
etc...
}
mysql_close($db);
?>

这是我想出的代码。这只是一个替代方案。但这个解决方案的问题在于。我不会将MONTH中的记录显示为调用。当我选择respo时，respo正在工作，它将显示我询问的所有respo，但问题是它包括了我选择的respo之前的所有月份。当我只选择特定月份时。。