I have a search page and a results page. It's a
dynamic search. It works great with just the main
table, but as soon as I add in the table joins (to
translate the codes) it just dumps out all the records
regardless of criteria.
Here is the script without the joins:
<?php
$currentPage = $_SERVER["PHP_SELF"];
if (isset($HTTP_GET_VARS['AdvSearch']) == false ||
$HTTP_GET_VARS['AdvSearch']=="")
exit();
$hasSQLWhereClause=false;
$whereClause="";
$MM_whereConst=" ";
$fieldValue = $HTTP_GET_VARS['PostStart'];
if(isset($HTTP_GET_VARS['PostStart']) &&
$HTTP_GET_VARS['PostStart'] != "")
{
if($hasSQLWhereClause == false)
{
$hasSQLWhereClause=true;
$whereClause=$whereClause."
rtrim(ltrim(upper(PostStart))) =
rtrim(ltrim(upper($fieldValue)))";
}
else
{
$whereClause=$whereClause." and
rtrim(ltrim(upper(PostStart))) =
rtrim(ltrim(upper($fieldValue)))";
}
}
$fieldValue = $HTTP_GET_VARS['JobTitle'];
if(isset($HTTP_GET_VARS['JobTitle']) &&
$HTTP_GET_VARS['JobTitle']!= "")
{
if($hasSQLWhereClause == false)
{
$hasSQLWhereClause=true;
$whereClause=$whereClause."
rtrim(ltrim(upper(JobTitle))) like
rtrim(ltrim(upper('%$fieldValue%')))";
}
else
{
$whereClause=$whereClause." and
rtrim(ltrim(upper(JobTitle))) like
rtrim(ltrim(upper('%$fieldValue%')))";
}
}
if(isset($HTTP_GET_VARS['Industry2']))
{
$colValue = $HTTP_GET_VARS['Industry2'];
$orCtr = 1;
if ($hasSQLWhereClause == false)
{
$hasSQLWhereClause=true;
$whereClause=$whereClause."(";
foreach($colValue as $fieldValue)
{
$whereClause=$whereClause."rtrim(ltrim(upper(Industry)))
= rtrim(ltrim(upper('$fieldValue')))";
if($orCtr < count($colValue))
{
$whereClause=$whereClause." or ";
}
$orCtr++;
}
$whereClause=$whereClause.")";
}
else
{
$whereClause=$whereClause." and (";
foreach($colValue as $fieldValue)
{
$whereClause=$whereClause."rtrim(ltrim(upper(Industry)))
= rtrim(ltrim(upper('$fieldValue')))";
if($orCtr < count($colValue))
{
$whereClause=$whereClause." or ";
}
$orCtr++;
}
$whereClause=$whereClause.")";
}
}
$fieldValue = $HTTP_GET_VARS['LocationCity'];
if(isset($HTTP_GET_VARS['LocationCity']) &&
$HTTP_GET_VARS['LocationCity']!= "")
{
if($hasSQLWhereClause == false)
{
$hasSQLWhereClause=true;
$whereClause=$whereClause."
rtrim(ltrim(upper(LocationCity))) like
rtrim(ltrim(upper('%$fieldValue%')))";
}
else
{
$whereClause=$whereClause." and
rtrim(ltrim(upper(LocationCity))) like
rtrim(ltrim(upper('%$fieldValue%')))";
}
}
if(isset($HTTP_GET_VARS['LocationState2']))
{
$colValue = $HTTP_GET_VARS['LocationState2'];
$orCtr = 1;
if ($hasSQLWhereClause == false)
{
$hasSQLWhereClause=true;
$whereClause=$whereClause."(";
foreach($colValue as $fieldValue)
{
$whereClause=$whereClause."rtrim(ltrim(upper(LocationState)))
= rtrim(ltrim(upper('$fieldValue')))";
if($orCtr < count($colValue))
{
$whereClause=$whereClause." or ";
}
$orCtr++;
}
$whereClause=$whereClause.")";
}
else
{
$whereClause=$whereClause." and (";
foreach($colValue as $fieldValue)
{
$whereClause=$whereClause."rtrim(ltrim(upper(LocationState)))
= rtrim(ltrim(upper('$fieldValue')))";
if($orCtr < count($colValue))
{
$whereClause=$whereClause." or ";
}
$orCtr++;
}
$whereClause=$whereClause.")";
}
}
if(isset($HTTP_GET_VARS['TaxTerm2']))
{
$colValue = $HTTP_GET_VARS['TaxTerm2'];
$orCtr = 1;
if ($hasSQLWhereClause == false)
{
$hasSQLWhereClause=true;
$whereClause=$whereClause."(";
foreach($colValue as $fieldValue)
{
$whereClause=$whereClause."rtrim(ltrim(upper(TaxTerm)))
= rtrim(ltrim(upper('$fieldValue')))";
if($orCtr < count($colValue))
{
$whereClause=$whereClause." or ";
}
$orCtr++;
}
$whereClause=$whereClause.")";
}
else
{
$whereClause=$whereClause." and (";
foreach($colValue as $fieldValue)
{
$whereClause=$whereClause."rtrim(ltrim(upper(TaxTerm)))
= rtrim(ltrim(upper('$fieldValue')))";
if($orCtr < count($colValue))
{
$whereClause=$whereClause." or ";
}
$orCtr++;
}
$whereClause=$whereClause.")";
}
}
if($whereClause != "")
$MM_whereConst="WHERE";
$maxRows_RecordSet1 = 10;
$pageNum_RecordSet1 = 0;
if (isset($HTTP_GET_VARS['pageNum_RecordSet1'])) {
$pageNum_RecordSet1 =
$HTTP_GET_VARS['pageNum_RecordSet1'];
}
$startRow_RecordSet1 = $pageNum_RecordSet1 *
$maxRows_RecordSet1;
mysql_select_db($xxxx, $link);
$query_RecordSet1 = "SELECT * FROM VendorJobs
$MM_whereConst $whereClause order by PostStart desc";
$query_limit_RecordSet1 = sprintf("%s LIMIT %d, %d",
$query_RecordSet1, $startRow_RecordSet1,
$maxRows_RecordSet1);
$RecordSet1 = mysql_query($query_limit_RecordSet1,
$link) or die(mysql_error());
$row_RecordSet1 = mysql_fetch_assoc($RecordSet1);
if (isset($HTTP_GET_VARS['totalRows_RecordSet1'])) {
$totalRows_RecordSet1 =
$HTTP_GET_VARS['totalRows_RecordSet1'];
} else {
$all_RecordSet1 = mysql_query($query_RecordSet1);
$totalRows_RecordSet1 =
mysql_num_rows($all_RecordSet1);
}
$totalPages_RecordSet1 =
ceil($totalRows_RecordSet1/$maxRows_RecordSet1)-1;
$queryString_RecordSet1 = "";
if (!empty($_SERVER['QUERY_STRING'])) {
$params = explode("&", $_SERVER['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
if (stristr($param, "pageNum_RecordSet1") == false
&&
stristr($param, "totalRows_RecordSet1") ==
false) {
array_push($newParams, $param);
}
}
if (count($newParams) != 0) {
$queryString_RecordSet1 = "&" .
htmlentities(implode("&", $newParams));
}
}
$queryString_RecordSet1 =
sprintf("&totalRows_RecordSet1=%d%s",
$totalRows_RecordSet1, $queryString_RecordSet1);
?><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01
Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd";>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
charset=iso-8859-1">
<title>Untitled Document</title>
</head>
<body>
<?php if ($totalRows_RecordSet1 == 0) { // Show if
recordset empty ?>
Search returned no results.
<?php exit(); } // Show if recordset empty ?>
<table border="1" width="80%" align="center">
<tr>
<td align="center">PostStart</td>
<td align="center">JobTitle</td>
<td align="center">VendorID</td>
<td align="center">Industry</td>
<td align="center">LocationCity</td>
<td align="center">LocationState</td>
<td align="center">TaxTerm</td>
</tr>
<?php do { ?>
<tr>
<td align="center">
<?php echo $row_RecordSet1['PostStart']; ?>
</td>
<td align="center">
<?php echo $row_RecordSet1['JobTitle']; ?>
</td>
<td align="center">
<?php echo $row_RecordSet1['VendorID']; ?>
</td>
<td align="center">
<?php echo $row_RecordSet1['Industry']; ?>
</td>
<td align="center">
<?php echo $row_RecordSet1['LocationCity'];
?>
</td>
<td align="center">
<?php echo $row_RecordSet1['LocationState'];
?>
</td>
<td align="center">
<?php echo $row_RecordSet1['TaxTerm']; ?>
</td>
</tr>
<?php } while ($row_RecordSet1 =
mysql_fetch_assoc($RecordSet1)); ?>
</table><br>
<table border="0" width="50%" align="center">
<tr>
<td width="23%" align="center">
<?php if ($pageNum_RecordSet1 > 0) { // Show if not
first page ?>
<a href="<?php printf("%s?pageNum_RecordSet1=%d%s",
$currentPage, 0, $queryString_RecordSet1);
?>">First</a>
<?php } // Show if not first page ?>
</td>
<td width="31%" align="center">
<?php if ($pageNum_RecordSet1 > 0) { // Show if not
first page ?>
<a href="<?php printf("%s?pageNum_RecordSet1=%d%s",
$currentPage, max(0, $pageNum_RecordSet1 - 1),
$queryString_RecordSet1); ?>">Previous</a>
<?php } // Show if not first page ?>
</td>
<td width="23%" align="center">
<?php if ($pageNum_RecordSet1 <
$totalPages_RecordSet1) { // Show if not last page ?>
<a href="<?php printf("%s?pageNum_RecordSet1=%d%s",
$currentPage, min($totalPages_RecordSet1,
$pageNum_RecordSet1 + 1), $queryString_RecordSet1);
?>">Next</a>
<?php } // Show if not last page ?>
</td>
<td width="23%" align="center">
<?php if ($pageNum_RecordSet1 <
$totalPages_RecordSet1) { // Show if not last page ?>
<a href="<?php printf("%s?pageNum_RecordSet1=%d%s",
$currentPage, $totalPages_RecordSet1,
$queryString_RecordSet1); ?>">Last</a>
<?php } // Show if not last page ?>
</td>
</tr>
</table>
Records <?php echo ($startRow_RecordSet1 + 1) ?>
to <?php echo min($startRow_RecordSet1 +
$maxRows_RecordSet1, $totalRows_RecordSet1) ?>
of <?php echo $totalRows_RecordSet1 ?>
</body>
</html><?php
mysql_free_result($RecordSet1);
?>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
