[SOLVED]Re: [PHP-DB] Search results page not working with table joins

[Stuart Felenstein <stuart4m@xxxxxxxxx>]

--- Stuart Felenstein <stuart4m@xxxxxxxxx> wrote:

> 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";
> 
=== message truncated ===

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php