Search results page not working with table joins

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 

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']; ?>&nbsp; 
	
  </td>
  <td align="center">
    <?php echo $row_RecordSet1['JobTitle']; ?>&nbsp; 
	
  </td>
  <td align="center">
    <?php echo $row_RecordSet1['VendorID']; ?>&nbsp; 
	
  </td>
  <td align="center">
    <?php echo $row_RecordSet1['Industry']; ?>&nbsp; 
	
  </td>
  <td align="center">
    <?php echo $row_RecordSet1['LocationCity'];
?>&nbsp; 
	
  </td>
  <td align="center">
    <?php echo $row_RecordSet1['LocationState'];
?>&nbsp; 
	
  </td>
  <td align="center">
    <?php echo $row_RecordSet1['TaxTerm']; ?>&nbsp; 
	
  </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
[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]
  Powered by Linux