V4.1.x Extra Field Filter

[pipelin +]

Hi,

Maybe someone can help with this:

I'm trying to modify the plugin "function.feature_listings_vertical_home" adding it "products_fields" table.

I need find the registers where fID==50 and sValue=='Toronto' then, find the featured in the listings table, but I got error (Query failed: Query was empty) and cant find it.

This is the code:

PHP Code:

$filter_field=(int)'50';
$fvalue = 'Toronto';

$lsql = 'SELECT pID, sValue, FROM ' .PREFIX. 'products_fields WHERE fID='.$filter_field.' ORDER BY fID ASC';
        $fresult = $db->query($sSQL);
        $i=0;
        while ($rs = $fresult->fetch()) 
            {
                if ($fvalue == $rs['sValue']) {

                    $sSQL='SELECT id,title,price FROM '.PREFIX.'listings WHERE display = \'Y\' AND id = '.$rs['pID'].'  AND expiration > NOW() AND featured = \'Y\' ORDER BY '. $sort .' LIMIT '.$number;
                    $result=$db->query($sSQL);
                    $row = $result->fetch();
                    
                        $imageSQL = "SELECT image FROM ".PREFIX."prodimages WHERE pid=".$row['id']." ORDER BY rank LIMIT 1";
                        $iResult=$db->query($imageSQL);
                        $irs=$iResult->fetch();
                        if($irs['image']<>"")
                        {
                            $image="thumbs/small_".$irs['image'];
                        }
                        else
                        {
                            $image="http://www.68classifieds.com/forums/images/nophoto.gif";
                        }
                        $tmp = array(
                            'id' => $row['id'],
                            'image'=> $image,
                            'title'=> $row['title'],
                            'price'=> FormatCurrency($row['price']),
                        );
                        
                } $loop[$i++] = $tmp; 
            } 
            
    $loop_count = count($loop)-1; 


[seymourjames +]

Are you trying to do this generally. In other words, filter on an extrafield and display featured listings horizontally for that extrafield?

[pipelin +]

Yes, but in this case is verticall featured listing. I fixed some mistakes. this is the code

PHP Code:

    $lsql = 'SELECT pID, sValue FROM ' .PREFIX. 'products_fields WHERE fID='.$filter_field.' ORDER BY fID ASC';
        $fresult = $db->query($lsql);
        $i=0;
        while ($rs = $fresult->fetch()) 
            {
                if ($fvalue == $rs['sValue']) {

                    $sSQL='SELECT id,title,price FROM '.PREFIX.'listings WHERE display = \'Y\' AND id = '.$rs['pID'].'  AND expiration > NOW() AND featured = \'Y\' ORDER BY '. $sort .' LIMIT '.$number;
                    $result=$db->query($sSQL);
                    $row = $result->fetch();
                    
                        $imageSQL = "SELECT image FROM ".PREFIX."prodimages WHERE pid=".$row['id']." ORDER BY rank LIMIT 1";
                        $iResult=$db->query($imageSQL);
                        $irs=$iResult->fetch();
                        if($irs['image']<>"")
                        {
                            $image="thumbs/small_".$irs['image'];
                        }
                        else
                        {
                            $image="images/nophoto.gif";
                        }
                        $tmp = array(
                            'id' => $row['id'],
                            'image'=> $image,
                            'title'=> $row['title'],
                            'price'=> FormatCurrency($row['price']),
                        );
                        
               $loop[$i++] = $tmp;  } 
             } 


Now it is filtering the city correctly, BUT I got this error (Query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY rank LIMIT 1' at line 1 SQL: SELECT image FROM class_textprodimages WHERE pid= ORDER BY rank LIMIT 1 in) for each register and after that it shows me the gallery, including all register for this city (featured and featured)

[pipelin +]

I changed ".$row['id']." to pid=".$rs['pID']." in this line:

$imageSQL = "SELECT image FROM ".PREFIX."prodimages WHERE pid=".$row['id']." ORDER BY rank LIMIT 1";

new: $imageSQL = "SELECT image FROM ".PREFIX."prodimages WHERE pid=".$rs['pID']." ORDER BY rank LIMIT 1";

Now I dont get the errors it show me the gallery including all registers for that city

I think it is not taking this part of the if:

PHP Code:

 $sSQL='SELECT id,title,price FROM '.PREFIX.'listings WHERE display = \'Y\' AND id = '.$rs['pID'].'  AND expiration > NOW() AND featured = \'Y\' ORDER BY '. $sort .' LIMIT '.$number;
                    $result=$db->query($sSQL);
                    $row = $result->fetch(); 


I don't know why

[pipelin +]

I fixed it. It was something weird with the featured field.

Thanks