Searching by extra fields issue -- problem fixed

FINALLY

I have narrowed the down the problem with searching by extrafields --and posted a solution

I have not tested all the extra field types but I have tested the checkboxes.

The problem I referred to previously -- indicated that the extra-field criteria were not being included in the query. In fact they are being included in the query however it only appeared this way because I had one ad in the system.

I will explain further.

Create category structure:

Category1

Set a checkbox field called 'test" that appears with the category structure:

Category1

The Checkbox should have 4 potential values : 'a', 'b' , 'c', 'd'

Create 3 ads under the category structure:

Category1

Assign each of the 3 ads only one checkbox value a
'a' checked in one ad
'b' checked in one ad
'c' checked in one ad

'd' is not checked in any ad.

The problem:
--click search
--select category: Category1
--extra field "test" appears
--select 'a' ===> The ad containing the 'a' check appears
--select 'b' ===> The ad containing the 'b' check appears
--select 'c' ===> The ad containing the 'c' check appears
--select 'd' ===> All Three ads appear even though none of them have 'd' --checked

Till there is at least one ad with a particular value checked ==>selecting that value will return all ads.


The fix is in Listings.php function getAllListings($options, $current_row, $max)

$inSQL="";
$count=0;
//correction: if extra_fields are present then flag because the where clause must change about line 175
$ExtraFieldFlag=0;
/**
* Get all the extra fields.
*/
foreach(@$_GET as $objItem => $objValue)
{
if(substr($objItem,0,3)=="opt" && $objValue<>"")
{ //correction: flag that optional fields were processed --required for where clause line 175
$ExtraFieldFlag=1;
$total=count($objValue);
if(substr($objItem,0,6)=="optmax"){
$count=$count-1;
}elseif( substr($objItem,0,6)=="optmin" && $objValue<>"" ){
if($objValue > 1) {
$min=str_replace('optmin','',$objItem);
$max=(int)$_GET["optmax$min"];
$or="";
if($count>0){
$or= " OR ";
}
$inSQL .= $or ."(fID=$min AND sValue BETWEEN ". (int)$objValue ." AND ". (int)$max.")";
}
}else{
if($count>0){
$inSQL .= " OR sValue LIKE '%". safeAddSlashes($objValue) ."%'";


}else{
$inSQL .= " sValue Like '%". safeAddSlashes($objValue) ."%'";


}
}
$count++;
}
}

/**
* Run the query to get the listing id based off the product fields or "extra fields".
*/
if($count>0 && $inSQL<>'')
{
$pids='';
$sql2 = "SELECT pID FROM ".PREFIX."products_fields WHERE (".$inSQL.")";
$result=$db->query($sql2);
while ($row=$result->fetch())
{
$pids .= $row['pID'].',';
}
}

$select = $modules->call_hook('searchlistings_select', ''); // Call any module functions
$sSQL = "SELECT p.id, p.owner, p.title, p.featured, p.section, p.shortDescription, p.description, p.price, p.dateadded, p.expiration, p.display, p.pHighlighted, p.pBold, p.hitcount, p.url, u.state, u.city, u.country ". $select ." FROM ".PREFIX."listings AS p LEFT JOIN ".PREFIX."users AS u ON p.owner = u.id ";
}
//where clause
$where="WHERE 1=1";
if (isset($_REQUEST['fromdate']) && $_REQUEST['fromdate']<>"")
{
$enddate=date("Y-m-d");
if(isset($_REQUEST['enddate']) && $_REQUEST['enddate']<>"")
{
$enddate=$_REQUEST['enddate'];
}
$where .= " AND dateadded BETWEEN '".trim($_REQUEST['fromdate'])."' AND '".trim($enddate)." 23:59:59'";
}


// CORRECTED Where syntax in the event that there are extra fields--if there are extrafields then include where clause to take this into account --if empy($pids) is true then there are no ads that had met the criteria selected in the optionalfields therefore kill the query by setting 1<>1 -- ie the query will return an empty dataset

if ($ExtraFieldFlag > 0){
$where .= ( empty($pids) ) ? ' AND 1 <> 1': " AND p.id IN (". rtrim($pids, ',') .")";
}

$where .= ( empty($options['owner']) ) ? '': " AND owner = ".(int)$options['owner'];
$where .= ( empty($options['section']) ) ? '': " AND section = ".(int)$options['section'];
$where .= ( empty($options['featured']) ) ? '': " AND featured = 'Y'";
$where .= ( empty($_REQUEST['owner']) ) ? '': " AND owner = ".(int)$_REQUEST['owner'];
$where .= ( empty($_REQUEST['l_Category']) ) ? '': " AND section = ".(int)$_REQUEST['l_Category'];
$where .= ( empty($_REQUEST['l_ID']) ) ? '': " AND id = ".(int)$_REQUEST['l_ID'];
$where .= ( empty($_REQUEST['adid']) ) ? '': " AND id = ".(int)$_REQUEST['adid'];
$where .= ( empty($options['o_ID']) ) ? '': " AND orderID = ".(int)$options['o_ID']







Mark

Mark,

Thanks for the bug report.

We have done some testing with this and modified that function a little differently than you did. I was wanting to see if our changed code still works for you?

I am a total newbie so I imagine you fix will be swifter. I am happy to test the new file any time you would like. Are we scheduled for an RC8 yet.

Mark

I am planning for rc8 this week.

In the meantime you can try the file here:
http://www.68classifieds.com/forums/...56#attachments

I have left in some echo statements but it will hopefully give you good results.

I tested the file -- works well.