PDA

View Full Version : Creating Search Query with Optional Fields


sonofwillie
10-28-2010, 08:07 AM
Hi all,

I have used the linked Adobe cookbook to attempt to create a search query with optional fields:

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

However when I run the search all the table results are returned, rather than just the desired results, and I'm getting an error message:

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

The line that is causing the error is
$query_search .= ' WHERE ';

This is the code:

<?php require_once('../Connections/Project1.php'); ?>
<?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);
?>

davidj
11-01-2010, 09:42 AM
Please use code tags when posting code in the forum