[SOLVED]Re: [PHP-DB] Search results page not working with table joins
| From: | Stuart Felenstein | Date: | Mon, 25 Oct 2004 20:08:26 +0000 |
| Subject: | [SOLVED]Re: [PHP-DB] Search results page not working with table joins | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-37015@lists.php.net to get a copy of this message | ||
--- Stuart Felenstein <stuart4m@yahoo.com> 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 ===