Could someone help me out with a problem I have been working on for days now. I am building a search and results page in Dreamweaver with 4 drop down boxes for search criteria.
The search returns values fine if you select an option from each of the search criteria boxes. The problems start if you only select criteria from 1, 2 or 3 search criteria areas.
I have found many people have had the same issue but have not actually found a diffinitive answer to this common problem.
Following is the SQL Statement:
SELECT *
FROM tblVacancies
WHERE Location = 'varLocation' AND Area = 'varArea' AND Industry = 'varIndustry' AND SubIndustry = 'varSubIndustry'
(Note the same behaviour applies if I substitute = with LIKE)
If I replace the AND statement with OR, then all records get returned.
Following are the Variables as set up in Dreamweaver:
Name Default Value Run-time Value
varIndustry % $GET['Industry']
varLocation % $GET['Location']
varArea % $GET['Area']
varSubIndustry % $GET['SubIndustry']
Following is the code generated by Dreamweaver for the Server Markup. Would appreciate it if someone could tell me what I need to add and where to get this search functionality working properly.
$currentPage = $_SERVER["PHP_SELF"];
$maxRows_rsSearchJobBoard = 1;
$pageNum_rsSearchJobBoard = 0;
if (isset($GET['pageNum_rsSearchJobBoard'])) {
$pageNum_rsSearchJobBoard = $GET['pageNum_rsSearchJobBoard'];
}
$startRow_rsSearchJobBoard = $pageNum_rsSearchJobBoard * $maxRows_rsSearchJobBoard;
$varIndustry_rsSearchJobBoard = "%";
if (isset($GET['Industry'])) {
$varIndustry_rsSearchJobBoard = (get_magic_quotes_gpc()) ? $GET['Industry'] : addslashes($GET['Industry']);
}
$varLocation_rsSearchJobBoard = "%";
if (isset($GET['Location'])) {
$varLocation_rsSearchJobBoard = (get_magic_quotes_gpc()) ? $GET['Location'] : addslashes($GET['Location']);
}
$varArea_rsSearchJobBoard = "%";
if (isset($GET['Area'])) {
$varArea_rsSearchJobBoard = (get_magic_quotes_gpc()) ? $GET['Area'] : addslashes($GET['Area']);
}
$varSubIndustry_rsSearchJobBoard = "%";
if (isset($GET['SubIndustry'])) {
$varSubIndustry_rsSearchJobBoard = (get_magic_quotes_gpc()) ? $GET['SubIndustry'] : addslashes($GET['SubIndustry']);
}
mysql_select_db($database_connJobBoard, $connJobBoard);
$query_rsSearchJobBoard = sprintf("SELECT * FROM tblVacancies WHERE Location = '%s' AND Area = '%s' AND Industry = '%s' AND SubIndustry = '%s'", $varLocation_rsSearchJobBoard,$varArea_rsSearchJobBoard,$varIndustry_rsSearchJobBoard,$varSubIndustry_rsSearchJobBoard);
$query_limit_rsSearchJobBoard = sprintf("%s LIMIT %d, %d", $query_rsSearchJobBoard, $startRow_rsSearchJobBoard, $maxRows_rsSearchJobBoard);
$rsSearchJobBoard = mysql_query($query_limit_rsSearchJobBoard, $connJobBoard) or die(mysql_error());
$row_rsSearchJobBoard = mysql_fetch_assoc($rsSearchJobBoard);
if (isset($GET['totalRows_rsSearchJobBoard'])) {
$totalRows_rsSearchJobBoard = $GET['totalRows_rsSearchJobBoard'];
} else {
$all_rsSearchJobBoard = mysql_query($query_rsSearchJobBoard);
$totalRows_rsSearchJobBoard = mysql_num_rows($all_rsSearchJobBoard);
}
$totalPages_rsSearchJobBoard = ceil($totalRows_rsSearchJobBoard/$maxRows_rsSearchJobBoard)-1;
$queryString_rsSearchJobBoard = "";
if (!empty($SERVER['QUERY_STRING'])) {
$params = explode("&", $SERVER['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
if (stristr($param, "pageNum_rsSearchJobBoard") == false &&
stristr($param, "totalRows_rsSearchJobBoard") == false) {
array_push($newParams, $param);
}
}
if (count($newParams) != 0) {
$queryString_rsSearchJobBoard = "&" . htmlentities(implode("&", $newParams));
}
}
$queryString_rsSearchJobBoard = sprintf("&totalRows_rsSearchJobBoard=%d%s", $totalRows_rsSearchJobBoard, $queryString_rsSearchJobBoard);
