Well, I need to add a few more parameters to the search fields, and so far I'm not having much luck. I need to add 2 dynamic drop down list to the search form. I've tried just starting with one, but I can't seem to get it to pull any results. I attached and image of the general table structure.
The pcms2_users.small_group column contains and integer the references small_group.id and small_group.small_group contains a list of names of Small Groups. The small_group.dc column contains an integer that matches the dc.id column and the dc.dc column contains the actual names of DC's that I want to populate the dropdown.
The end result is to select a DC from the dropdown and have the search return a list of users from the pcms2_users table that have an integer in the pcms2_users.small_group column which matches a row in the small_group table containing an integer in small_group.dc that matches the dc.id of the dc.dc selected in the dropdown, and display the dc.dc in the DC column of the table
This is what I did, and all I'm getting is a blank dropdown menu.
<?php require_once('Connections/local_i.php'); ?>
<?php require_once('webassist/mysqli/rsobj.php'); ?>
<?php
$DC = new WA_MySQLi_RS("DC",$local_i,1);
$DC->setQuery("SELECT dc.id, dc.dc FROM dc");
$DC->execute();
?>
<?php
$Day = new WA_MySQLi_RS("Day",$local_i,1);
$Day->setQuery("SELECT day.id, day.day FROM day");
$Day->execute();
?>
<?php
$DCQuery = new WA_MySQLi_RS("DCQuery",$local_i,1);
$DCQuery->setQuery("SELECT small_group.* FROM small_group WHERE small_group.dc = ? AND small_group.day = ?");
$DCQuery->bindParam("i", "".($DC->getColumnVal("dc")) ."", "-1"); //WAQB_Param1
$DCQuery->bindParam("i", "".($Day->getColumnVal("day")) ."", "-1"); //WAQB_Param2
$DCQuery->execute();
?>
<?php
$ServingQuery = new WA_MySQLi_RS("ServingQuery",$local_i,0);
$ServingQuery->setQuery("SELECT pcms2_users.UserID, pcms2_users.UserFirstName, pcms2_users.UserLastName, pcms2_users.SmallGroup FROM pcms2_users WHERE (pcms2_users.UserFirstName = ? OR ? = -1) AND (pcms2_users.UserLastName = ? OR ? = -1) AND (pcms2_users.SmallGroup = ? OR ? = -1)");
$ServingQuery->bindParam("s", "".(isset($_GET['UserFirstName'])?$_GET['UserFirstName']:"") ."", "-1"); //paramFirstName1
$ServingQuery->bindParam("s", "".(isset($_GET['UserFirstName'])?$_GET['UserFirstName']:"") ."", "-1"); //paramFirstName2
$ServingQuery->bindParam("s", "".(isset($_GET['UserLastName'])?$_GET['UserLastName']:"") ."", "-1"); //paramLastName1
$ServingQuery->bindParam("s", "".(isset($_GET['UserLastName'])?$_GET['UserLastName']:"") ."", "-1"); //paramLastName2
$ServingQuery->bindParam("s", "".(isset($_GET['SmallGroup'])?$_GET['SmallGroup']:"") ."", "-1"); //paramSmallGroup1
$ServingQuery->bindParam("s", "".(isset($_GET['small_group'])?$_GET['small_group']:"") ."", "-1"); //paramSmallGroup2
$ServingQuery->execute();
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Untitled Document</title>
</head>
<body>
<form method="get">
<input type="search" name="UserFirstName" id="UserFirstName">
<input type="search" name="UserLastName" id="UserLastName">
<select name="smallgroup" id="smallgroup">
<?php
while(!$DCQuery->atEnd()) { //dyn select
?>
<option value="<?php echo($DCQuery->getColumnVal("id")); ?>"><?php echo($DCQuery->getColumnVal("small_group")); ?></option>
<?php
$DCQuery->moveNext();
} //dyn select
$DCQuery->moveFirst();
?>
</select>
<input type="submit" name="search" value="search">
<?php if((isset($_GET['UserFirstName']) && $_GET['UserFirstName'] != "") OR (isset($_GET['UserLastName']) && $_GET['UserLastName'] != "") OR (isset($_GET['small_group']) && $_GET['small_group'] != "")) { ?>
<?php
while(!$ServingQuery->atEnd()) {
$_GET['UserID'] = $ServingQuery->getColumnVal("UserID");
?>
<table width="100%" border="1">
<tbody>
<tr>
<td align="center">First Name</td>
<td align="center">Last Name</td>
<td width="20%" align="center">DC</td>
<td width="20%" align="center">Serving Areas</td>
</tr>
<tr>
<td align="center"><a href="pcms2_users_detail.php?UserID=<?php echo($ServingQuery->getColumnVal("UserID")); ?>"><?php echo($ServingQuery->getColumnVal("UserFirstName")); ?></a></td>
<td align="center"><?php echo($ServingQuery->getColumnVal("UserLastName")); ?></td>
<td width="20%" align="center"><?php echo($DCQuery->getColumnVal("SmallGroup")); ?></td>
<?php
$CleaningTeamQuery = new WA_MySQLi_RS("CleaningTeamQuery",$local_i,1);
$CleaningTeamQuery->setQuery("SELECT serving_team.*, cleaning_team_shifts.* FROM serving_team INNER JOIN cleaning_team_shifts ON serving_team.id = cleaning_team_shifts.cleaning_team_id WHERE cleaning_team_shifts.cleaning_team_volunteer = ?");
$CleaningTeamQuery->bindParam("s", "".($ServingQuery->getColumnVal("UserID")) ."", "-1"); //WAQB_Param1
$CleaningTeamQuery->execute();
?>
<?php
$KidsProgramQuery = new WA_MySQLi_RS("KidsProgramQuery",$local_i,1);
$KidsProgramQuery->setQuery("SELECT serving_team.*, kids_program_shifts.* FROM serving_team LEFT OUTER JOIN kids_program_shifts ON serving_team.id = kids_program_shifts.kids_program_team_id WHERE kids_program_shifts.kids_program_volunteer = ?");
$KidsProgramQuery->bindParam("s", "".($ServingQuery->getColumnVal("UserID")) ."", "-1"); //WAQB_Param1
$KidsProgramQuery->execute();?>
<?php
$YouthProgramQuery = new WA_MySQLi_RS("YouthProgramQuery",$local_i,1);
$YouthProgramQuery->setQuery("SELECT serving_team.*, youth_program_shifts.* FROM serving_team INNER JOIN youth_program_shifts ON serving_team.id = youth_program_shifts.yp_team_id WHERE youth_program_shifts.yp_volunteer = ?");
$YouthProgramQuery->bindParam("s", "".($ServingQuery->getColumnVal("UserID")) ."", "-1"); //WAQB_Param1
$YouthProgramQuery->execute();?>
<?php
$ITTeamQuery = new WA_MySQLi_RS("ITTeamQuery",$local_i,1);
$ITTeamQuery->setQuery("SELECT serving_team.*, it_team_shifts.* FROM serving_team INNER JOIN it_team_shifts ON serving_team.id = it_team_shifts.it_team_id WHERE it_team_shifts.it_volunteer = ?");
$ITTeamQuery->bindParam("i", "".($ServingQuery->getColumnVal("UserID")) ."", "-1"); //WAQB_Param1
$ITTeamQuery->execute();?>
<?php
$ClericalTeamQuery = new WA_MySQLi_RS("ClericalTeamQuery",$local_i,1);
$ClericalTeamQuery->setQuery("SELECT serving_team.*, clerical_team_shifts.* FROM serving_team INNER JOIN clerical_team_shifts ON serving_team.id = clerical_team_shifts.clerical_team_id WHERE clerical_team_shifts.clerical_volunteer = ?");
$ClericalTeamQuery->bindParam("i", "".($ServingQuery->getColumnVal("UserID")) ."", "-1"); //WAQB_Param1
$ClericalTeamQuery->execute();?>
<?php
$WorshipTeamQuery = new WA_MySQLi_RS("WorshipTeamQuery",$local_i,1);
$WorshipTeamQuery->setQuery("SELECT serving_team.*, worship_team_shifts.* FROM serving_team INNER JOIN worship_team_shifts ON serving_team.id = worship_team_shifts.worship_team_id WHERE worship_team_shifts.worship_volunteer = ?");
$WorshipTeamQuery->bindParam("i", "".($ServingQuery->getColumnVal("UserID")) ."", "-1"); //WAQB_Param1
$WorshipTeamQuery->execute();?>
<?php
$HospitalityTeamQuery = new WA_MySQLi_RS("HospitalityTeamQuery",$local_i,1);
$HospitalityTeamQuery->setQuery("SELECT serving_team.*, hospitality_team_shifts.* FROM serving_team INNER JOIN hospitality_team_shifts ON serving_team.id = hospitality_team_shifts.hospitality_team_id WHERE hospitality_team_shifts.hospitality_volunteer = ?");
$HospitalityTeamQuery->bindParam("i", "".($ServingQuery->getColumnVal("UserID")) ."", "-1"); //WAQB_Param1
$HospitalityTeamQuery->execute();?>
<td width="20%" align="center"><?php echo($CleaningTeamQuery->getColumnVal("team")); ?> <br><?php echo($KidsProgramQuery->getColumnVal("team")); ?> <br><?php echo($YouthProgramQuery->getColumnVal("team")); ?> <br><?php echo($ITTeamQuery->getColumnVal("team")); ?> <br><?php echo($ClericalTeamQuery->getColumnVal("team")); ?> <br><?php echo($WorshipTeamQuery->getColumnVal("team")); ?> <br><?php echo($HospitalityTeamQuery->getColumnVal("team")); ?>
</td>
</tr>
</tbody>
</table>
<?php
$ServingQuery->moveNext();
}
$ServingQuery->moveFirst(); //return RS to first record
?>
<?php } ?>
</form>
</body>
</html>
