我试图用不同的下拉过滤器(年级、学校、团队、学生)实现搜索,但我的代码出现了严重的问题。当我加载页面"retrieve1.php"时,我只能看到一个框,即Grade,它也是空的。如果有任何帮助,我将不胜感激。下面是我的代码-
<?php
require 'account1.php';
echo "<body style='background-color:#DCDCDC'>";
$sql= "SELECT * FROM bpi_registration LEFT JOIN bpi_schoolInfo on
bpi_registration.id_school = bpi_schoolInfo.id_school ";
$query=$db->query($sql);
function grade() {
$result = $db->query('SELECT * FROM bpi_classInfo');
while($row = $result->fetchALL(PDO::FETCH_ASSOC))
{
echo "<option value='" . $row['class_name'] . "'>" . $row['class_name'] . "</option>";
}
}
function school() {
$result = $db->query('SELECT * FROM bpi_schoolInfo');
while($row = $result->fetchALL(PDO::FETCH_ASSOC))
{
echo "<option value='" . $row['school_name'] . "'>" . $row['school_name'] . "</option>";
}
}
function team() {
$result = $db->query('SELECT * FROM bpi_teamProfile');
while($row = $result->fetchALL(PDO::FETCH_ASSOC))
{
echo "<option value='" . $row['team_name'] . "'>" . $row['team_name'] . "</option>";
}
}
function students() {
$result = $db->query('SELECT * FROM bpi_registration');
while($row = $result->fetchALL(PDO::FETCH_ASSOC))
{
echo "<option value='" . $row['id']. "'>" . $row['first_name'].' '.$row['last_name']. "</option>";
}
}
?>
<form action="retrieve1.php" method="GET">
<select name="Grade">
<option value="" selected="selected">Choose Grade</option>
<?php grade() ?>
</select>
<select name="School">
<option value="" selected="selected">Choose School</option>
<?php school() ?>
</select>
<select name="Team">
<option value="" selected="selected">Choose Team</option>
<?php team() ?>
</select>
<select name="Students">
<option value="" selected="selected">Choose Students</option>
<?php students() ?>
</select>
<input type="submit" value="Find" />
</form>
<table width="600" border="2">
<tr>
<th width="198"> <div align="center">Email </div></th>
<th width="97"> <div align="center">City </div></th>
<th width="97"> <div align="center">State </div></th>
<th width="59"> <div align="center">Country </div></th>
<tr>
<?php
if (isset($_GET['Students']))
{
//echo $_GET['Students'];
$userQuery = "{$sql} WHERE bpi_registration.id = :user_id";
$user = $db->prepare($sql);
$user->execute(['user_id' => $_GET['Students']]);
$selectedUser=$user->fetch(PDO::FETCH_ASSOC);
}
?>
<?php
if(isset($selectedUser))
{
echo $selectedUser['email'];
echo $selectedUser['address_city'];
echo $selectedUser['address_state'];
echo $selectedUser['address_country'];
}
?>
account1.php
<?php
$db = new PDO('mysql:host=localhost;dbname=test',$user, $pass);
?>
我得到错误
Fatal error: Call to a member function query() on null
in public_html/retrieve1.php on line 24
您报告的错误是由于变量作用域问题引起的。换句话说,函数不知道$db
是什么,因为它超出了范围,$db
存在于全局范围中,但不存在于每个函数范围中。
此外,->FetchAll()
将返回一个数组,如果您使用while循环来处理查询的结果集,则应使用->fetch()
,因为它每次调用将返回一行,并且在while循环中可用。
此外,您运行的第一个查询,以及后来尝试修改的搜索条件将不起作用。一旦调用了->query()
,就不能修改查询。所以我把代码移到了实际使用的地方,并删除了对->query()
的不必要调用
我还在输出中添加了一些表行HTML,这样数据就可以适应表结构。
下面修改后的代码应该会让你朝着目标前进,否则就会出现下一个错误。
<?php
require 'account1.php';
echo "<body style='background-color:#DCDCDC'>";
function grade($db) {
$result = $db->query('SELECT * FROM bpi_classInfo');
while($row = $result->fetch(PDO::FETCH_ASSOC))
{
echo "<option value='" . $row['class_name'] . "'>" . $row['class_name'] . "</option>";
}
}
function school($db) {
$result = $db->query('SELECT * FROM bpi_schoolInfo');
while($row = $result->fetch(PDO::FETCH_ASSOC))
{
echo "<option value='" . $row['school_name'] . "'>" . $row['school_name'] . "</option>";
}
}
function team($db) {
$result = $db->query('SELECT * FROM bpi_teamProfile');
while($row = $result->fetch(PDO::FETCH_ASSOC))
{
echo "<option value='" . $row['team_name'] . "'>" . $row['team_name'] . "</option>";
}
}
function students($db) {
$result = $db->query('SELECT * FROM bpi_registration');
while($row = $result->fetch(PDO::FETCH_ASSOC))
{
echo "<option value='" . $row['id']. "'>" . $row['first_name'].' '.$row['last_name']. "</option>";
}
}
?>
<form action="retrieve1.php" method="GET">
<select name="Grade">
<option value="" selected="selected">Choose Grade</option>
<?php grade($db) ?>
</select>
<select name="School">
<option value="" selected="selected">Choose School</option>
<?php school($db) ?>
</select>
<select name="Team">
<option value="" selected="selected">Choose Team</option>
<?php team($db) ?>
</select>
<select name="Students">
<option value="" selected="selected">Choose Students</option>
<?php students($db) ?>
</select>
<input type="submit" value="Find" />
</form>
<table width="600" border="2">
<tr>
<th width="198"> <div align="center">Email </div></th>
<th width="97"> <div align="center">City </div></th>
<th width="97"> <div align="center">State </div></th>
<th width="59"> <div align="center">Country </div></th>
</tr>
<?php
if (isset($_GET['Students']))
{
$sql= "SELECT *
FROM bpi_registration
LEFT JOIN bpi_schoolInfo on
bpi_registration.id_school = bpi_schoolInfo.id_school ";
//echo $_GET['Students'];
$userQuery = "{$sql} WHERE bpi_registration.id = :user_id";
$user = $db->prepare($sql);
$user->execute(['user_id' => $_GET['Students']]);
$selectedUser=$user->fetch(PDO::FETCH_ASSOC);
if(isset($selectedUser))
{
echo '<tr>';
echo '<td>' . $selectedUser['email'] . '</td>';
echo '<td>' . $selectedUser['address_city'] . '</td>';
echo '<td>' . $selectedUser['address_state'] . '</td>';
echo '<td>' . $selectedUser['address_country'] . '</td>';
echo '</tr>
}
}
echo '</table>`;
?>