I am trying to implement search with different dropdown filters (Grade, School, Team, Students) but something is going terribly wrong with my code. When i load the page "retrieve1.php", i am able to see only one box i.e. Grade and that is also empty. I would appreciate any help. Below is my code -
<?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);
?>
I get the error
Fatal error: Call to a member function query() on null
in public_html/retrieve1.php on line 24
The error you report is due to a variable scoping issue. In other words the functions do not know what $db is because it is out of scope, $db exists in the global scope but not in each functions scope.
Also a ->FetchAll() will return an array, if you are using a while loop to process the result set from a query you should use ->fetch() as this will return one row per call and is usable in a while loop.
Also the first query you ran, and then later attempted to amend the search criteria on will not work. Once a ->query() has been called the query cannot be amended. So I moved that code to where it was actually being used and removed the unnecessary call to ->query()
I also added some table line HTML to your output so the data will fit into the table structure.
The amended code below should move you towards your goal, or the next error.
<?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>`;
?>