Filter Recordset by Form List Menu

Report · Mar 12, 2013

Hi all,

I have a Recordset created in Dreamweaver that lists all jobs that our team have to complete... I currently have a list menu (drop-down) called 'OfficerSelect' which includes the following values:

Andy

Dave

Matt

Tom

All

I have used the SQL below (see attached screen shot) to get the list menu to filter through the jobs for the staff name selected, however I would like it to list all jobs for all staff when the value 'All' is selected.

Does anyone have any ideas how this can be done?

Thanks,

Tom

Report · Mar 12, 2013

You will need to modify the code and evaluate the value of the dropdown item - then create a different WHERE clause if 'ALL' was selected. DW won't do this for you - you'll need to hand code this.

Report · Mar 12, 2013

Hi...

I agree, this is where I got stuck. I dont know enough about php/sql etc to do this.. here is what I have tried but it didn't work...

SELECT *

FROM tms

WHERE

(if colname = 'All' then *

else responsible_officer = colname)

AND task_type = 'O-Track Set Up'

AND signed_off = 'N'

Could you help me write this code please?

Cheers,

Tom

Report · Mar 12, 2013

You're trying evaluate the value within the SQL, which won't work. You need to adapt your scripting code for this. Please attach the script for this page.

Report · Mar 12, 2013

Hi,

I have pasted the entire script below. I hope this helps (it looks horrendous to me, I can only do things through the wizards and WYSIWYG features!)...

Thanks so much for looking at this.

Tom

------

<?php require_once('../Connections/Optimum.php'); ?>

<?php

//initialize the session

if (!isset($_SESSION)) {

session_start();

}

// ** Logout the current user. **

$logoutAction = $_SERVER['PHP_SELF']."?doLogout=true";

if ((isset($_SERVER['QUERY_STRING'])) && ($_SERVER['QUERY_STRING'] != "")){

$logoutAction .="&". htmlentities($_SERVER['QUERY_STRING']);

}

if ((isset($_GET['doLogout'])) &&($_GET['doLogout']=="true")){

//to fully log out a visitor we need to clear the session varialbles

$_SESSION['MM_Username'] = NULL;

$_SESSION['MM_UserGroup'] = NULL;

$_SESSION['PrevUrl'] = NULL;

unset($_SESSION['MM_Username']);

unset($_SESSION['MM_UserGroup']);

unset($_SESSION['PrevUrl']);

$logoutGoTo = "login.php";

if ($logoutGoTo) {

header("Location: $logoutGoTo");

exit;

}

?>

<?php

if (!isset($_SESSION)) {

session_start();

}

$MM_authorizedUsers = "";

$MM_donotCheckaccess = "true";

// *** Restrict Access To Page: Grant or deny access to this page

function isAuthorized($strUsers, $strGroups, $UserName, $UserGroup) {

// For security, start by assuming the visitor is NOT authorized.

$isValid = False;

// When a visitor has logged into this site, the Session variable MM_Username set equal to their username.

// Therefore, we know that a user is NOT logged in if that Session variable is blank.

if (!empty($UserName)) {

// Besides being logged in, you may restrict access to only certain users based on an ID established when they login.

// Parse the strings into arrays.

$arrUsers = Explode(",", $strUsers);

$arrGroups = Explode(",", $strGroups);

if (in_array($UserName, $arrUsers)) {

$isValid = true;

}

// Or, you may restrict access to only certain users based on their username.

if (in_array($UserGroup, $arrGroups)) {

$isValid = true;

}

if (($strUsers == "") && true) {

$isValid = true;

}

return $isValid;

}

$MM_restrictGoTo = "login.php";

if (!((isset($_SESSION['MM_Username'])) && (isAuthorized("",$MM_authorizedUsers, $_SESSION['MM_Username'], $_SESSION['MM_UserGroup'])))) {

$MM_qsChar = "?";

$MM_referrer = $_SERVER['PHP_SELF'];

if (strpos($MM_restrictGoTo, "?")) $MM_qsChar = "&";

if (isset($QUERY_STRING) && strlen($QUERY_STRING) > 0)

$MM_referrer .= "?" . $QUERY_STRING;

$MM_restrictGoTo = $MM_restrictGoTo. $MM_qsChar . "accesscheck=" . urlencode($MM_referrer);

header("Location: ". $MM_restrictGoTo);

exit;

}

?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

$currentPage = $_SERVER["PHP_SELF"];

mysql_select_db($database_Optimum, $Optimum);

$query_CompanyDetails = "SELECT * FROM website_contacts";

$CompanyDetails = mysql_query($query_CompanyDetails, $Optimum) or die(mysql_error());

$row_CompanyDetails = mysql_fetch_assoc($CompanyDetails);

$totalRows_CompanyDetails = mysql_num_rows($CompanyDetails);

$maxRows_TMS_OutstandingOtrackSetUp = 10;

$pageNum_TMS_OutstandingOtrackSetUp = 0;

if (isset($_GET['pageNum_TMS_OutstandingOtrackSetUp'])) {

$pageNum_TMS_OutstandingOtrackSetUp = $_GET['pageNum_TMS_OutstandingOtrackSetUp'];

}

$startRow_TMS_OutstandingOtrackSetUp = $pageNum_TMS_OutstandingOtrackSetUp * $maxRows_TMS_OutstandingOtrackSetUp;

$colname_TMS_OutstandingOtrackSetUp = "-1";

if (isset($_POST['OfficerSelect'])) {

$colname_TMS_OutstandingOtrackSetUp = $_POST['OfficerSelect'];

}

mysql_select_db($database_Optimum, $Optimum);

$query_TMS_OutstandingOtrackSetUp = sprintf("SELECT * FROM tms WHERE responsible_officer = %s AND task_type = 'O-Track Set Up' AND signed_off = 'N'", GetSQLValueString($colname_TMS_OutstandingOtrackSetUp, "text"));

$query_limit_TMS_OutstandingOtrackSetUp = sprintf("%s LIMIT %d, %d", $query_TMS_OutstandingOtrackSetUp, $startRow_TMS_OutstandingOtrackSetUp, $maxRows_TMS_OutstandingOtrackSetUp);

$TMS_OutstandingOtrackSetUp = mysql_query($query_limit_TMS_OutstandingOtrackSetUp, $Optimum) or die(mysql_error());

$row_TMS_OutstandingOtrackSetUp = mysql_fetch_assoc($TMS_OutstandingOtrackSetUp);

if (isset($_GET['totalRows_TMS_OutstandingOtrackSetUp'])) {

$totalRows_TMS_OutstandingOtrackSetUp = $_GET['totalRows_TMS_OutstandingOtrackSetUp'];

} else {

$all_TMS_OutstandingOtrackSetUp = mysql_query($query_TMS_OutstandingOtrackSetUp);

$totalRows_TMS_OutstandingOtrackSetUp = mysql_num_rows($all_TMS_OutstandingOtrackSetUp);

}

$totalPages_TMS_OutstandingOtrackSetUp = ceil($totalRows_TMS_OutstandingOtrackSetUp/$maxRows_TMS_OutstandingOtrackSetUp)-1;

$colname_TMS_OustandingDataSEF = "-1";

if (isset($_POST['OfficerSelect'])) {

$colname_TMS_OustandingDataSEF = $_POST['OfficerSelect'];

}

mysql_select_db($database_Optimum, $Optimum);

$query_TMS_OustandingDataSEF = sprintf("SELECT * FROM tms WHERE responsible_officer = %s AND task_type = 'dataSEF Analysis' AND signed_off = 'N'", GetSQLValueString($colname_TMS_OustandingDataSEF, "text"));

$TMS_OustandingDataSEF = mysql_query($query_TMS_OustandingDataSEF, $Optimum) or die(mysql_error());

$row_TMS_OustandingDataSEF = mysql_fetch_assoc($TMS_OustandingDataSEF);

$totalRows_TMS_OustandingDataSEF = mysql_num_rows($TMS_OustandingDataSEF);

$queryString_TMS_OutstandingOtrackSetUp = "";

if (!empty($_SERVER['QUERY_STRING'])) {

$params = explode("&", $_SERVER['QUERY_STRING']);

$newParams = array();

foreach ($params as $param) {

if (stristr($param, "pageNum_TMS_OutstandingOtrackSetUp") == false &&

stristr($param, "totalRows_TMS_OutstandingOtrackSetUp") == false) {

array_push($newParams, $param);

}

if (count($newParams) != 0) {

$queryString_TMS_OutstandingOtrackSetUp = "&" . htmlentities(implode("&", $newParams));

}

$queryString_TMS_OutstandingOtrackSetUp = sprintf("&totalRows_TMS_OutstandingOtrackSetUp=%d%s", $totalRows_TMS_OutstandingOtrackSetUp, $queryString_TMS_OutstandingOtrackSetUp);

require_once '../scripts/Mobile_Detect.php';

$detect = new Mobile_Detect;

$deviceType = ($detect->isMobile() ? ($detect->isTablet() ? 'tablet' : 'phone') : 'computer');

$scriptVersion = $detect->getScriptVersion();

?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

<title>Optimum O-Track : Online Pupil Tracking Systems and Software for EYFS, KS1 and KS2</title>

<!--

.style1 {font-size: 14px}

-->

</style>

</head>

<?php

if ($detect->isMobile() || $detect->isTablet() ) {

echo "<body style='width:1000px;'>";

}

else

{

echo "<body>";

}

?>

Need some help? Call <span style="color:#215fa4"><?php echo $row_CompanyDetails['Telephone']; ?></span> <span style="color:#666666">or</span> <a href="../contactus.php">contact us</a> </div>

<ul>

<li><a href="../products.php">Products</a></li>

<li><a href="../support.php">Support</a></li>

<li ><a href="../customer.php" >Customer</a></li>

<li><a href="../contactus.php">Contact us</a></li>

</ul>

</div>

<?php

if ($detect->isMobile() || $detect->isTablet() ) {

}

else

{

echo "<div class='social_right'>

<br />

Find <br />

us on

</div>";

}

?>

</div>

</div>

<p>Welcome to the Optimum Task Management System (TMS).

Below is a list of all outstanding tasks. To view an archive of all 'signed off' tasks click <a href="archive.php">here</a>.</p>

<h3 align="center">Filter by Responsible Officer</h3>

<option value="Andrew">Andrew</option>

<option value="Tommy">Tommy</option>

<option value="select" selected="selected">select</option>

</select>

</p>

</form>

<h2>Outstanding O-Track Set Ups (<?php echo $totalRows_TMS_OutstandingOtrackSetUp ?>)</h2>

<?php if ($totalRows_TMS_OutstandingOtrackSetUp > 0) { // Show if recordset not empty ?>

<?php do { ?>

<tr>

Responsible Officer: <?php echo $row_TMS_OutstandingOtrackSetUp['responsible_officer']; ?><br />

Pick Up Officer: <?php echo $row_TMS_OutstandingOtrackSetUp['otrack_data_pick_up_officer']; ?><br />

Pick Up Date: <?php echo date ('D jS M Y', strtotime ($row_TMS_OutstandingOtrackSetUp['otrack_data_pickup_date'])); ?> </p> </td>

<td width="29%" valign="top"><a href="delete_otrack.php?recordID=<?php echo $row_TMS_OutstandingOtrackSetUp['id']; ?>">Delete</a></td>

</tr>

</table>

<p>

<?php } while ($row_TMS_OutstandingOtrackSetUp = mysql_fetch_assoc($TMS_OutstandingOtrackSetUp)); ?>

<?php } // Show if recordset not empty ?>

<h2>Outstanding dataSEFs (<?php echo $totalRows_TMS_OustandingDataSEF ?>) </h2>

<?php if ($totalRows_TMS_OustandingDataSEF > 0) { // Show if recordset not empty ?>

<?php do { ?>

<tr>

Responsible Officer: <?php echo $row_TMS_OustandingDataSEF['responsible_officer']; ?><br />

Data Received Date: <?php echo date ('D jS M Y', strtotime ($row_TMS_OustandingDataSEF['analysis_data_received_date'])); ?><br />

</p> </td>

<td width="29%" valign="top"><a href="delete_analysis.php?recordID=<?php echo $row_TMS_OustandingDataSEF['id']; ?>">Delete</a></td>

</tr>

</table>

<?php } while ($row_TMS_OustandingDataSEF = mysql_fetch_assoc($TMS_OustandingDataSEF)); ?>

<?php } // Show if recordset not empty ?>

</div>

</div>

</div>

</div>

<span class="style1"><a href="add_otrack.php">O-Track Set Up</a></span></div>

</div>

</div>

<span class="style1"><a href="add_otrackreport.php">O-Track Report</a></span><br />

</div>

</div>

<span class="style1"><a href="add_analysis.php">Analysis Report</a></span><br />

</div>

</div>

</div>

<ul>

<li><a href="../products.php">Products</a></li>

<li><a href="../support.php">Support</a></li>

<li><a href="../customer.php" >Customer</a></li>

<li><a href="../contactus.php" >Contact us</a></li>

</ul>

</div>

<ul>

</ul>

</div>

<a href="../terms.php">Terms</a> | <a href="../privacy.php">Privacy policy</a> | © <?php echo date('Y');?> Optimum Reports

</div>

</body>

</html>

<?php

mysql_free_result($CompanyDetails);

mysql_free_result($TMS_OutstandingOtrackSetUp);

mysql_free_result($TMS_OustandingDataSEF);

?>

Report · Mar 12, 2013

You need to change the current query definition to something like this:

if ($colname_TMS_OustandingDataSEF = "All") {

$query_TMS_OustandingDataSEF = "SELECT * FROM tms WHERE task_type = 'dataSEF Analysis' AND signed_off = 'N'";

} else {

$query_TMS_OustandingDataSEF = sprintf("SELECT * FROM tms WHERE responsible_officer = %s AND task_type = 'dataSEF Analysis' AND signed_off = 'N'", GetSQLValueString($colname_TMS_OustandingDataSEF, "text"));

}

You've got two similar queries so you'll need to make a similar change in the other one.