Create search query with optional fields - Not Working, need suggestions pls

Report · Oct 28, 2010

Hi, need help please asap.

I am trying to search/filter records from a DB table and have used the code from the link below -

http://cookbooks.adobe.com/post_Create_search_query_with_optional_fields-16245.html

However when I run the search, the filtered results are not returned, rather I get back all the table records. Also on the view results page I get the following error:

Notice: Undefined variable: query_search in C:\wamp\www\CIWProject\Bugs\ViewSearchedBugs.php on line 91

relating to the following line of code:

$query_search .= ' WHERE ';

Ani ideas/suggestions/solutions greatly appreciated.

The page code in its entirety:

<?php require_once('../Connections/Project1.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 = "../index.html";

if ($logoutGoTo) {

header("Location: $logoutGoTo");

exit;

}

?>

<?php

if (!function_exists("GetSQLValueString")) {

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

{

if (PHP_VERSION < 6) {

$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;

}

mysql_select_db($database_Project1, $Project1);

$query_ViewSearchedBugs = "SELECT BugID, ContractName, Username, LogDate, Priority, `Description`, Status FROM tblbugs";

$ViewSearchedBugs = mysql_query($query_ViewSearchedBugs, $Project1) or die(mysql_error());

$row_ViewSearchedBugs = mysql_fetch_assoc($ViewSearchedBugs);

$totalRows_ViewSearchedBugs = mysql_num_rows($ViewSearchedBugs);

mysql_select_db($database_Project1, $Project1);

$expected = array('ContractName' => 'text', 'Username' => 'text', 'Priority' => 'text', 'Status' => 'text');

$query_ViewSearchedBugs = "SELECT BugID, ContractName, Username, LogDate, Priority, `Description`, Status FROM tblbugs";

// Set a flag to indicate whether the query has a WHERE clause

$where = false;

// Loop through the associatiave array of expected search values

foreach ($expected as $var => $type) {

if (isset($_GET[$var])) {

$value = trim(urldecode($_GET[$var]));

if (!empty($value)) {

// Check if the value begins with > or <

// If so, use it as the operator, and extract the value

if ($value[0] == '>' || $value[0] == '<') {

$operator = $value[0];

$value = ltrim(substr($value, 1));

} elseif (strtolower($type) != 'like') {

$operator = '=';

}

// Check if the WHERE clause has been added yet

if ($where) {

$query_search .= ' AND ';

} else {

$query_search .= ' WHERE ';

$where = true;

}

// Build the SQL query using the right operator and data type

$type = strtolower($type);

switch($type) {

case 'like':

$query_search .= "`$var` LIKE " . GetSQLValueString('%' .

$value . '%', "text");

break;

case 'int':

case 'double':

case 'date':

$query_search .= "`$var` $operator " .

GetSQLValueString($value, "$type");

break;

default:

$query_search .= "`$var` = " . GetSQLValueString($value,

"$type");

}

$ViewSearchedBugs = mysql_query($query_ViewSearchedBugs, $Project1) or die(mysql_error());

$row_ViewSearchedBugs = mysql_fetch_assoc($ViewSearchedBugs);

$totalRows_ViewSearchedBugs = mysql_num_rows($ViewSearchedBugs);

?>

<?php

if (!isset($_SESSION)) {

session_start();

}

$MM_authorizedUsers = "Developer,Project Manager";

$MM_donotCheckaccess = "false";

// *** 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 == "") && false) {

$isValid = true;

}

return $isValid;

}

$MM_restrictGoTo = "../LoginUnsuccessful.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($_SERVER['QUERY_STRING']) && strlen($_SERVER['QUERY_STRING']) > 0)

$MM_referrer .= "?" . $_SERVER['QUERY_STRING'];

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

header("Location: ". $MM_restrictGoTo);

exit;

}

?>

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

<head>

<title>Untitled Document</title>

</head>

<body>

<div id="Banner"> <img src="../Images/Green_bug2.JPG" alt="Banner Image" width="100" class="FloatLeft"/>BugMaster</div>

<?php if ($_SESSION['MM_UserGroup'] == 'Project Manager') { ?>

<div class="LinkBox"><a href="../Contracts/ViewContracts.php">CONTRACTS</a></div>

<div class="LinkBox"><a href="../Users/ViewUsers.php">USERS</a></div>

<div class="LinkBox"><a href="<?php echo $logoutAction ?>">LOGOUT</a></div>

</div>

<h2>View Searched Bugs.</h2>

<p>Search criteria: </p>

<?php if ($totalRows_ViewSearchedBugs == 0) { // Show if recordset empty ?>

<p>No matching records found; <a href="FindBugs.php">search again</a>?</p>

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

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

<tr>

<th scope="col">Contract Name</th>

<th scope="col">Username</th>

<th scope="col">Priority</th>

<th scope="col">Description</th>

<th scope="col">Status</th>

</tr>

<?php do { ?>

<tr>

</tr>

<?php } while ($row_ViewSearchedBugs = mysql_fetch_assoc($ViewSearchedBugs)); ?>

</table>

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

</div>

<div class="LinkBox"><a href="../Contracts/ViewContracts.php">CONTRACTS</a></div>

<div class="LinkBox"><a href="../Users/ViewUsers.php">USERS</a></div>

<div class="LinkBox"><a href="<?php echo $logoutAction ?>">LOGOUT</a></div>

</div>

<br/>

Image 'Green_Bug2'©Mushii@Wikipedia, used under licence <a href="http://creativecommons.org/licenses/by-sa/3.0/deed.en">http://creativecommons.org/licenses/by-sa/3.0/deed.en</a> , site layout and content©Cian O' Regan 2010.

<br/>

</div>

<?php } else { ?>

<div class="LinkBox"><a href="../LoggedIn.php">DEVELOPER HOME</a></div>

<div class="LinkBoxActive"><a href="FindBugs.php">SEARCH BUGS</a></div>

<div class="LinkBox"><a href="<?php echo $logoutAction ?>">LOGOUT</a><a href="Links.php"></a></div>

</div>

<h2>View Searched Bugs.</h2>

<p>Search criteria: </p>

<?php if ($totalRows_ViewSearchedBugs == 0) { // Show if recordset empty ?>

<p>No matching records found; <a href="FindBugs.php">search again</a>?</p>

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

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

<tr>

<th scope="col">Contract Name</th>

<th scope="col">Username</th>

<th scope="col">Priority</th>

<th scope="col">Description</th>

<th scope="col">Status</th>

</tr>

<?php do { ?>

<tr>

</tr>

<?php } while ($row_ViewSearchedBugs = mysql_fetch_assoc($ViewSearchedBugs)); ?>

</table>

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

</div>

<div class="LinkBox"><a href="../LoggedIn.php">DEVELOPER HOME</a></div>

<div class="LinkBoxActive"><a href="FindBugs.php">SEARCH BUGS</a></div>

<div class="LinkBox"><a href="<?php echo $logoutAction ?>">LOGOUT</a></div>

</div>

<?php } ?>

<br/>

Image 'Green_Bug2'©Mushii@Wikipedia, used under licence <a href="http://creativecommons.org/licenses/by-sa/3.0/deed.en">http://creativecommons.org/licenses/by-sa/3.0/deed.en</a> , site layout and content©Cian O' Regan 2010.

<br/>

</div>

</body>

</html>

<?php

mysql_free_result($ViewSearchedBugs);

?>

Report · Oct 28, 2010

sidenote: I always get a chuckle out of "please help asap".

Your supplied code doesn't match the code you linked to. Their query variable is named $query_search but your varaible is named $query_ViewSearchedBugs hence the error message and failure to complete the query because the code is calling the variable query_search later in the code, but it's undefined because you named the query variable something else.

Look at the code you linked to then look at your supplied code. The differences in the code are the reasons why your code isn't working.

Report · Oct 28, 2010

Hi there,

Thanks very much for the reply, however what you suggested only partially worked.

Changes all variables as you suggested and I am getting zero records returned but the error is gone - progress at last!

Now the problem is the form where the search criteria are specified. The first option in each of the dynamic menus is a static item with a value of 0 (i.e. item 1 in the list says 'Choose an Option', item 2 is the dynamic menu). Now when I search no records are returned because none of them have any fields populated with zeros.

Should I start a new thread?!

Report · Oct 28, 2010

Change default form fields to a value of null instead of a value of 0 to correct the problem you've metioned like this:

value=""

instead of this:

value="0"

If that doesn't work then provide the URL of the search result page after you've submitted the search form. Also please list your databse table schema to determine if your database matches your query.

Report · Oct 28, 2010

I just tried setting option values to null rather than zero (e.g. <option value=""> -- Select User -- </option>) but that didn't work unfortunately.

Path of query results is C:\wamp\www\CIWProject\Bugs\ViewSearchedBugs.php

I'm sure the query is accessing the correct table as when i populate the search options with data that I know matches a record, it returns that record.

Database name Project1, tables are:

otblRoles (fields: RoleID, Role)

tblUsers (fields: UserID, Username, FName, LName, Password, Email, Role)

otblContracts (fields: ContractID, ContractName)

otblStatus (fields: StatusID, Status)

otblPriority (fields: PriorityID, Priority)

tblBugs (fields: BugID, ContractName, Username, LogDate, Priority, Description, Status)

Report · Oct 28, 2010

Your query results URL is most likely the problem. There isn't any URL parameters being sent to the search results page to filter the query. Use GET method on your search form.

Report · Oct 28, 2010

Got it.

The submit query page is divided in two for administrators and regular users. I changed the option values to null only in the administrator portion of the page and was checking as a user - silly mistake, apologies.

Thanks so much for your time, I actually couldn't have done it without you.

Thanks again!