Support Forums

darek · 04-05-2007, 02:14 AM

ok, the story begun when I decided to use google maps in my 68c. The maps work, I managed to put maps in viewlisting and both admins. However it would be nice to have one map with all the markers in the index.php. So the first thing what I did is to try to get all the listings in the index.php This may be done by 2 ways (similar) either toplistings.php or searchresults.php has to be placed in the index.php

So I placed this code from toplistings.php in the index.php:
(I gave the sample but had to delete it as the message was too long)
This will return exactly the same data as this: http://www.68classifieds.com/example/toplistings.php but it will show all the other data that is in the index.php

OK, my google maps use extra fields with ID=2 for Latitude and ID=3 for Longitude
to have control over Latitude and Longitude in the viewlisting.php I did this:

PHP Code:


			
//extra fields code for $extraV - I had to delete it to shorten the message        

        

                

        //extra fields - Latitude

        $rs=$result->fetch();

        $sSQL = "SELECT f.fID,sValue FROM ".PREFIX."products_fields AS p, `".PREFIX."fields` AS f WHERE fID='2'";

        $sql="SELECT fID,fName,fType FROM ".PREFIX."fields WHERE fID='2'";

            $result2=$db->query($sql);

            $rs2=$result2->fetch();

            

            $tmp2 = array(

                'fID' => $rs2['fID'],

                'title' => $rs2['fName'],

                'value'=> $rs['sValue'],

            );

            $Latitude[$i++] = $tmp2;

            $i++;

            

        $class_tpl->assign('Latitude', $Latitude);

        

        



        //extra fields - Longitude

        $rs=$result->fetch();

        $sSQL = "SELECT f.fID,sValue FROM ".PREFIX."products_fields AS p, `".PREFIX."fields` AS f WHERE fID='3'";

        $sql="SELECT fID,fName,fType FROM ".PREFIX."fields WHERE fID='3'";

            $result2=$db->query($sql);

            $rs2=$result2->fetch();

            

            $tmp2 = array(

                'fID' => $rs2['fID'],

                'title' => $rs2['fName'],

                'value'=> $rs['sValue'],

            );

            $Longitude[$i++] = $tmp2;

            $i++;

            

        $class_tpl->assign('Longitude', $Longitude);

I copied this code into index.php - but it needs additional moddification as in viewlisting.php the code uses $view for

PHP Code:


			
WHERE p.pID='".$view."'

I copied the code under the part from toplistings.php and when I changed

PHP Code:


			
WHERE p.pID='".$view."'

to

PHP Code:


			
WHERE p.pID='2'

still nothing happend, I relized that I need to state the

PHP Code:


			
$row

before the extra fields part, so I put theat code inside

PHP Code:


			
while ($row=$result->fetch()) 

    { code }

- still nothing works, but when I removed

PHP Code:


			
{...}

and just left

PHP Code:


			
$row=$result->fetch();

I got dislpayed the extra fields in the listings.

However with

PHP Code:


			
p.pID='".$row['id']."'

it returns extra fields from last listing to all listings and when the code with extra fields is in the while brackets like here:

PHP Code:


			
while ($row=$result->fetch()) 

    { code }

it doesn't return any data from extra fields.

My final code looks like this:

PHP Code:


			
require_once('includes/init.php');



    

    // part from index.php unchanged//

        

    

    

    

    

    //part from toplistongs.php /////////////////////////

    $pageNum = 1;

    $page="new";

    

    //define blank variables

    $first="";

    $prev="";

    $next="";

    $last="";

    $pageNumber ="";





    if (isset($_GET['pg']))

    {

        $page=$_GET['pg'];

    }

    if(isset($_GET['page']))

    {

        $pageNum = (int)$_GET['page'];

    }

    

    //get search settings //

    $searchSQL = "SELECT sPagination,sMaxSearch,sProductSort,sProductSortOption,sTemplate FROM ".PREFIX."search_settings WHERE sID=1";

    $sResult=$db->query($searchSQL);

    $srs=$sResult->fetch();

    define("MAX_ROWS_SEARCH", $srs['sMaxSearch']);

    define("MAIN_PRODUCT_SORT", $srs['sProductSort']);

    define("MAIN_PRODUCT_SORT_OPTION", $srs['sProductSortOption']);

    $pagination=$srs['sPagination'];

    $sTemplate=$srs['sTemplate'];

    $limit=MAX_ROWS_SEARCH;

    

    $class_tpl->assign('sDisPhoto',$canViewSearchPhoto);

    $class_tpl->assign('sDisTitle',$canViewSearchTitle);

    $class_tpl->assign('sDisPrice',$canViewSearchPrice);

    $class_tpl->assign('sDisDateAdded',$canViewSearchDateAdded);

    $class_tpl->assign('sDisExpiration',$canViewSearchExpiration);

    $class_tpl->assign('sDisCity',$canViewSearchCity);

    $class_tpl->assign('sDisState',$canViewSearchState);

    $class_tpl->assign('sDisCountry',$canViewSearchCountry);

    

    //do the query

    switch($page)

    {

        

        default:

            $where = " ORDER BY dateadded DESC";

    }



    

    

    $select = $modules->call_hook('searchlistings_select', ''); // Call any module functions

    $wherehook= $modules->call_hook('searchquery_where', ''); // Call any module functions

    $sSQL="SELECT p.id, p.owner, p.title, p.display, p.featured, p.section, p.shortDescription, p.description, p.url, p.price, p.dateadded, p.expiration, p.pHighlighted, p.pBold, u.state, u.city, u.country".$select." FROM ".PREFIX."products AS p LEFT JOIN ".PREFIX."users AS u ON p.owner = u.id WHERE p.expiration > NOW() AND p.display = 'Y' ". $wherehook . $where;

    //get the number of listings

    $resultSet = $db->query($sSQL);

    $numrows = $resultSet->size();

    $resultSet->freeResult();

    

    $offset = ($pageNum - 1) * MAX_ROWS_SEARCH;

    $searchSQL = sprintf("%s LIMIT %d, %d", $sSQL, $offset, MAX_ROWS_SEARCH);

    $result=$db->query($searchSQL);

    $i=0;

    while ($row=$result->fetch()) 

    {

        //now get the images

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

        $iResult=$db->query($imageSQL);

        $irs=$iResult->fetch();

            if($row['pBold']=="Y")

            {

                $pre="<strong>";

                $post="</strong>";

            }

            else

            {

                $pre="";

                $post="";

            }

            if($row['pHighlighted']=="Y")

            {

                $row['class']="highlighted";

            }

            elseif($row['featured']=="Y")

            {

                $row['class']="featured";

            }

            else

            {

                $row['class']="";

            }

            if(defined('SEO'))

            {

                $listing_title=$row['title'];

                $listing_title=str_replace(' ', '_', $listing_title);

                $listing_title=preg_replace('/\W/e', '', $listing_title);

                $row['link']="listing/".$row['id']."/".$listing_title.".html";    

            }

            else

            {

                $row['link']="viewlisting.php?view=".$row['id'];

            }

            

                $row['image']=htmlspecialchars($irs['image']);

                $row['title']=$pre.safeStripSlashes($row['title']).$post;

                $row['shortDescription']=safeStripSlashes($row['shortDescription']);

                $row['price']=$pre.FormatCurrency($row['price']).$post;

                $row['state']=$pre.safeStripSlashes($row['state']).$post;

                $row['country']=$pre.safeStripSlashes($row['country']).$post;

                $row['url']=$pre.safeStripSlashes($row['url']).$post;

                

            

               $data[]=$row;

    }//end while

    

    







    $row=$result->fetch();

    

    // part from viewlisting.php /////////////////////////////////////

                        

        $extraV=array();

        $sSQL = "SELECT f.fID,sValue FROM ".PREFIX."products_fields AS p, `".PREFIX."fields` AS f WHERE p.pID='".$row['id']."' AND f.fSeen='Y' AND p.fID=f.fID ORDER BY f.fOrder ASC";

        $result=$db->query($sSQL);

        $i=0;

        while ($rs=$result->fetch()) 

        {

            $sql="SELECT fID,fName,fType FROM `".PREFIX."fields` WHERE fID='".$rs['fID']."' AND fSeen='Y' ORDER BY fOrder ASC";

            $result2=$db->query($sql);

            $rs2=$result2->fetch();



            $tmp = array(

                'fID' => $rs['fID'],

                'title' => $rs2['fName'],

                'value'=> $rs['sValue'],

            );

            $extraV[$i++] = $tmp;

            $i++;

        }

        

        

            $row['extraV']=$pre.safeStripSlashes($row['extraV']).$post;

        

          



    $result->freeResult();

    

     

     

    $class_tpl->assign('extraV', $extraV);

    

    //process the template

    $class_tpl->assign('title', $title);

    $class_tpl->assign('page', $currentPage);

    $class_tpl->assign('first', $first);

    $class_tpl->assign('prev', $prev);

    $class_tpl->assign('next', $next);

    $class_tpl->assign('last', $last);

    $class_tpl->assign('pageNum', $pageNum);

    $class_tpl->assign('pageNumber', $pageNumber);

    $class_tpl->assign('maxPage', $maxPage);

    $class_tpl->assign('pagination', $pagination);

    $class_tpl->assign('results', $data);

            

        

    

    $class_tpl->assign('title', $title);

    $class_tpl->assign('cols', $Core->settings['mainIndexCols']);

    $class_tpl->assign('data', $cat);

    $class_tpl->assign('body','home.tpl.php');

    $class_tpl->display('layout.tpl.php');

    $db->disconnect();

but as I wrote it returns the extra fields from the last listing

I think the code for extra fields should look like this:

PHP Code:


			






    while ($row=$result->fetch()) 

    {

        // part from viewlisting.php                

        $extraV=array();

        $sSQL = "SELECT f.fID,sValue FROM ".PREFIX."products_fields AS p, `".PREFIX."fields` AS f WHERE p.pID='".$row['id']."' AND f.fSeen='Y' AND p.fID=f.fID ORDER BY f.fOrder ASC";

        $result=$db->query($sSQL);

        $i=0;

        while ($rs=$result->fetch()) 

        {

            $sql="SELECT fID,fName,fType FROM `".PREFIX."fields` WHERE fID='".$rs['fID']."' AND fSeen='Y' ORDER BY fOrder ASC";

            $result2=$db->query($sql);

            $rs2=$result2->fetch();



            $tmp = array(

                'fID' => $rs['fID'],

                'title' => $rs2['fName'],

                'value'=> $rs['sValue'],

            );

            $extraV[$i++] = $tmp;

            $i++;

        }

        

        

            $row['extraV']=$pre.safeStripSlashes($row['extraV']).$post;

        

               $data[]=$row;

    }//end while

    $result->freeResult();

    

     

     

    $class_tpl->assign('extraV', $extraV);

but unfortunately it doesn't return any data for extra fields
(I had to shorten the code)

Can anyone help me with this?

juven14 · 04-05-2007, 10:08 AM

You need to include your extra fields inside of the while like the images are being done:

PHP Code:


			
while ($row=$result->fetch())  
{
     //now get the images
    $imageSQL = "SELECT image FROM ".PREFIX."prodimages WHERE pid=".$row['id']." ORDER BY rank LIMIT 1";
    $iResult=$db->query($imageSQL);
    $irs=$iResult->fetch();
    if($row['pBold']=="Y")
    {
        $pre="<strong>";
        $post="</strong>";
    }
    //[....]

    //put your extra field code here, be sure not to overwrite any variables being defined like $row or etc. 
    
    //[....]
}

darek · 04-05-2007, 11:17 AM

Yeah, I new that but I got an error from the image, but I solved it bu changing the name of $result to $Vresult, so the code looks like this now:

PHP Code:


			
$select = $modules->call_hook('searchlistings_select', ''); // Call any module functions

    $wherehook= $modules->call_hook('searchquery_where', ''); // Call any module functions

    $sSQL="SELECT p.id, p.owner, p.title, p.display, p.featured, p.section, p.shortDescription, p.description, p.url, p.price, p.dateadded, p.expiration, p.pHighlighted, p.pBold, u.state, u.city, u.country".$select." FROM ".PREFIX."products AS p LEFT JOIN ".PREFIX."users AS u ON p.owner = u.id WHERE p.expiration > NOW() AND p.display = 'Y' ". $wherehook . $where;

    //get the number of listings

    $resultSet = $db->query($sSQL);

    $numrows = $resultSet->size();

    $resultSet->freeResult();

    

    $offset = ($pageNum - 1) * MAX_ROWS_SEARCH;

    $searchSQL = sprintf("%s LIMIT %d, %d", $sSQL, $offset, MAX_ROWS_SEARCH);

    $result=$db->query($searchSQL);

    $i=0;

    while ($row=$result->fetch()) 

    {

        //now get the images

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

        $iResult=$db->query($imageSQL);

        $irs=$iResult->fetch();

            if($row['pBold']=="Y")

            {

                $pre="<strong>";

                $post="</strong>";

            }

            else

            {

                $pre="";

                $post="";

            }

            if($row['pHighlighted']=="Y")

            {

                $row['class']="highlighted";

            }

            elseif($row['featured']=="Y")

            {

                $row['class']="featured";

            }

            else

            {

                $row['class']="";

            }

            if(defined('SEO'))

            {

                $listing_title=$row['title'];

                $listing_title=str_replace(' ', '_', $listing_title);

                $listing_title=preg_replace('/\W/e', '', $listing_title);

                $row['link']="listing/".$row['id']."/".$listing_title.".html";    

            }

            else

            {

                $row['link']="viewlisting.php?view=".$row['id'];

            }

            

                    //$row=$result->fetch();                

        $extraV=array();

        $sSQL = "SELECT f.fID,sValue FROM ".PREFIX."products_fields AS p, `".PREFIX."fields` AS f WHERE p.pID='".$row['id']."' AND f.fSeen='Y' AND p.fID=f.fID ORDER BY f.fOrder ASC";

        $Vresult=$db->query($sSQL);

        $i=0;

        while ($rs=$Vresult->fetch()) 

        {

            $sql="SELECT fID,fName,fType FROM `".PREFIX."fields` WHERE fID='".$rs['fID']."' AND fSeen='Y' ORDER BY fOrder ASC";

            $result2=$db->query($sql);

            $rs2=$result2->fetch();



            $tmp = array(

                'fID' => $rs['fID'],

                'title' => $rs2['fName'],

                'value'=> $rs['sValue'],

            );

            $extraV[$i++] = $tmp;

            $i++;

        }

            

                $row['image']=htmlspecialchars($irs['image']);

                $row['title']=$pre.safeStripSlashes($row['title']).$post;

                $row['shortDescription']=safeStripSlashes($row['shortDescription']);

                $row['price']=$pre.FormatCurrency($row['price']).$post;

                $row['state']=$pre.safeStripSlashes($row['state']).$post;

                $row['country']=$pre.safeStripSlashes($row['country']).$post;

                $row['url']=$pre.safeStripSlashes($row['url']).$post;

                //$row['extraV']=$pre.safeStripSlashes($row['extraV']).$post;

            

               $data[]=$row;

    }//end while

    



    $result->freeResult();

    

     

     

    $class_tpl->assign('extraV', $extraV);

    

    

    //process the template

    $class_tpl->assign('title', $title);

    $class_tpl->assign('page', $currentPage);

    $class_tpl->assign('first', $first);

    $class_tpl->assign('prev', $prev);

    $class_tpl->assign('next', $next);

    $class_tpl->assign('last', $last);

    $class_tpl->assign('pageNum', $pageNum);

    $class_tpl->assign('pageNumber', $pageNumber);

    $class_tpl->assign('maxPage', $maxPage);

    $class_tpl->assign('pagination', $pagination);

    $class_tpl->assign('results', $data);

I don't have errors, but it displays the same extraV for all listings

any idea how to make it change???? maybe the template should be changed? I still use the same

PHP Code:


			
            {foreach from=$extraV item=extras}

            <strong>{$extras.title}:</strong><br />

            {if isset($extras.value)}

                      {foreach key=key item=item from=$extras.value}

                        {$item}<br />

                      {/foreach}

                     {/if}

                   {/foreach}

juven14 · 04-05-2007, 11:26 AM

You shouldn't need to mess around with looping the fields outside of the results.

Modify your code to look like this:

Replace this:

PHP Code:


			
//inside while loop 
$tmp = array(
    'fID' => $rs['fID'],
    'title' => $rs2['fName'],
    'value'=> $rs['sValue'],
);
$extraV[$i++] = $tmp;
$i++;

With this:

PHP Code:


			
//inside while loop
$fData['fID'] = $rs['fID'];
$fData['title'] = safeStripSlashes($rs2['fName']);
$fData['value'] = safeStripSlashes($rs['sValue']);
$row['xFields'] = $fData;

Then in your template, where the results are being displayed:

Code:

{foreach from=$xFields item=item}
$item.fID
$item.title
$item.value
{foreachelse}
something else
{/foreach}

darek · 04-05-2007, 11:55 AM

I saw this code in custom-fields-showlisting.zip, however mine was taken from original 3.15 version, Eric wrote it, and it nearly works perfect, the only thing is that it displays extras from one listing in all serched listings

I did try your code, doesn't work

when I change

PHP Code:


			
$sSQL = "SELECT f.fID,sValue FROM ".PREFIX."products_fields AS p, `".PREFIX."fields` AS f WHERE p.pID='".$row['id']."' AND f.fSeen='Y' AND p.fID=f.fID ORDER BY f.fOrder ASC";

PHP Code:


			
p.pID='".$row['id']."'

to

PHP Code:


			
p.pID='1'

or

PHP Code:


			
p.pID=2

I get display from first or second listing on all searched listings

- so it almost work fine, almost...

would you have some more ideas?

juven14 · 04-05-2007, 11:34 PM

Ok, this works but you will need to test for specific field names to ensure your data pads for listings that don't have a value for each specific field.

I created two fields, test1 and test2:

PHP Code:


			
$select = $modules->call_hook('searchlistings_select', ''); // Call any module functions
        $wherehook = $modules->call_hook('searchquery_where', ''); // Call any module functions
        $sSQL = "SELECT p.id, p.owner, p.title, p.display, p.featured, p.section, p.shortDescription, p.description, p.url, p.price, p.dateadded, p.expiration, p.pHighlighted, p.pBold, u.state, u.city, u.country" . $select . " FROM " . PREFIX . "products AS p LEFT JOIN " . PREFIX . "users AS u ON p.owner = u.id WHERE p.expiration > NOW() AND p.display = 'Y' " . $wherehook . $where;
        //get the number of listings
        $resultSet = $db->query($sSQL);
        $numrows = $resultSet->size();
        $resultSet->freeResult();
        
        $offset = ($pageNum - 1) * MAX_ROWS_SEARCH;
        $searchSQL = sprintf("%s LIMIT %d, %d", $sSQL, $offset, MAX_ROWS_SEARCH);
        $result = $db->query($searchSQL);
        $i = 0;
        while ($row=$result->fetch())
        {
            //now get the images
            $imageSQL = "SELECT image FROM ".PREFIX."prodimages WHERE pid=".$row['id']." ORDER BY rank LIMIT 1";
            $iResult=$db->query($imageSQL);
            $irs=$iResult->fetch();
            
            
            if($row['pBold']=="Y") {
                $pre="<strong>";
                $post="</strong>";
            } else {
                $pre="";
                $post="";
            }
            
            if($row['pHighlighted']=="Y") {
                $row['class']="highlighted";
            } elseif ($row['featured']=="Y") {
                $row['class']="featured";
            } else {
                $row['class']="";
            }
            
            if(defined('SEO')) {
                $listing_title=$row['title'];
                $listing_title=str_replace(' ', '_', $listing_title);
                $listing_title=preg_replace('/\W/e', '', $listing_title);
                $row['link']="listing/".$row['id']."/".$listing_title.".html";    
            } else {
                $row['link']="viewlisting.php?view=".$row['id'];
            }
            
            $xfSQL = "SELECT f.fID, f.fName, sValue FROM " . PREFIX . "products_fields AS p, `" . PREFIX . "fields` AS f WHERE p.pID='" . $row['id'] . "' AND f.fSeen='Y' AND p.fID=f.fID ORDER BY f.fOrder ASC";
            $xfResult = $db->query($xfSQL);
            while ($xfRS = $xfResult->fetch())
            {
                $fID = $xfRS['fID'];
                $fName = safeStripSlashes($xfRS['fName']);
                $sValue = safeStripSlashes($xfRS['sValue']);
                
                $row[$fName] = $sValue;
            }
            if (!isset($row['test1'])) {
                $row['test1'] = '';
            }
            if (!isset($row['test2'])) {
                $row['test2'] = '';
            }
            $row['image']=htmlspecialchars($irs['image']);
            $row['title']=$pre.safeStripSlashes($row['title']).$post;
            $row['shortDescription']=safeStripSlashes($row['shortDescription']);
            $row['price']=$pre.FormatCurrency($row['price']).$post;
            $row['state']=$pre.safeStripSlashes($row['state']).$post;
            $row['country']=$pre.safeStripSlashes($row['country']).$post;
            $row['url']=$pre.safeStripSlashes($row['url']).$post;
            $data[]=$row;
        }//end while

Note this part:

PHP Code:


			
            if (!isset($row['test1'])) {
                $row['test1'] = '';
            }
            if (!isset($row['test2'])) {
                $row['test2'] = '';
            }

Here is the part you need to custom tailor for your site fields. Remember by doing it this way you need to add the field names in on your own for the table heading. Also, the way you and I have it setup is to display all fields. If you want to control that you must put in a test for the field id, or call specific ids only. i.e.

Code:

$xfSQL = "SELECT f.fID, f.fName, sValue FROM " . PREFIX . "products_fields AS p, `" . PREFIX . "fields` AS f WHERE p.pID='" . $row['id'] . "' AND f.fSeen='Y' AND p.fID=f.fID AND f.fID=1 ORDER BY f.fOrder ASC";

Then just make a call for each field you want to display and giving it a unique key in the $row array.

...fID = 1
$row['field1'] = ...

darek · 04-06-2007, 09:28 AM

John, THANK YOU VERY MUCH!!!

and I mean it. Ispent probably 30 or 40 hours trying to get the data from array in the first page. Not only your last post solved the problem but also made it all work!! I lost hope I could do it myself. Thank you once more.

04-05-2007, 02:14 AM	#1
darek Senior Member Join Date: Dec 2006 Posts: 105 Rep Power: 13	I need a little help please ok, the story begun when I decided to use google maps in my 68c. The maps work, I managed to put maps in viewlisting and both admins. However it would be nice to have one map with all the markers in the index.php. So the first thing what I did is to try to get all the listings in the index.php This may be done by 2 ways (similar) either toplistings.php or searchresults.php has to be placed in the index.php So I placed this code from toplistings.php in the index.php: (I gave the sample but had to delete it as the message was too long) This will return exactly the same data as this: http://www.68classifieds.com/example/toplistings.php but it will show all the other data that is in the index.php OK, my google maps use extra fields with ID=2 for Latitude and ID=3 for Longitude to have control over Latitude and Longitude in the viewlisting.php I did this: PHP Code: //extra fields code for $extraV - I had to delete it to shorten the message //extra fields - Latitude $rs=$result->fetch(); $sSQL = "SELECT f.fID,sValue FROM ".PREFIX."products_fields AS p, `".PREFIX."fields` AS f WHERE fID='2'"; $sql="SELECT fID,fName,fType FROM ".PREFIX."fields WHERE fID='2'"; $result2=$db->query($sql); $rs2=$result2->fetch(); $tmp2 = array( 'fID' => $rs2['fID'], 'title' => $rs2['fName'], 'value'=> $rs['sValue'], ); $Latitude[$i++] = $tmp2; $i++; $class_tpl->assign('Latitude', $Latitude); //extra fields - Longitude $rs=$result->fetch(); $sSQL = "SELECT f.fID,sValue FROM ".PREFIX."products_fields AS p, `".PREFIX."fields` AS f WHERE fID='3'"; $sql="SELECT fID,fName,fType FROM ".PREFIX."fields WHERE fID='3'"; $result2=$db->query($sql); $rs2=$result2->fetch(); $tmp2 = array( 'fID' => $rs2['fID'], 'title' => $rs2['fName'], 'value'=> $rs['sValue'], ); $Longitude[$i++] = $tmp2; $i++; $class_tpl->assign('Longitude', $Longitude); I copied this code into index.php - but it needs additional moddification as in viewlisting.php the code uses $view for PHP Code: `WHERE p.pID='".$view."'` I copied the code under the part from toplistings.php and when I changed PHP Code: `WHERE p.pID='".$view."'` to PHP Code: `WHERE p.pID='2'` still nothing happend, I relized that I need to state the PHP Code: `$row` before the extra fields part, so I put theat code inside PHP Code: `while ($row=$result->fetch()) { code }` - still nothing works, but when I removed PHP Code: `{...}` and just left PHP Code: `$row=$result->fetch();` I got dislpayed the extra fields in the listings. However with PHP Code: `p.pID='".$row['id']."'` it returns extra fields from last listing to all listings and when the code with extra fields is in the while brackets like here: PHP Code: `while ($row=$result->fetch()) { code }` it doesn't return any data from extra fields. My final code looks like this: PHP Code: require_once('includes/init.php'); // part from index.php unchanged// //part from toplistongs.php ///////////////////////// $pageNum = 1; $page="new"; //define blank variables $first=""; $prev=""; $next=""; $last=""; $pageNumber =""; if (isset($_GET['pg'])) { $page=$_GET['pg']; } if(isset($_GET['page'])) { $pageNum = (int)$_GET['page']; } //get search settings // $searchSQL = "SELECT sPagination,sMaxSearch,sProductSort,sProductSortOption,sTemplate FROM ".PREFIX."search_settings WHERE sID=1"; $sResult=$db->query($searchSQL); $srs=$sResult->fetch(); define("MAX_ROWS_SEARCH", $srs['sMaxSearch']); define("MAIN_PRODUCT_SORT", $srs['sProductSort']); define("MAIN_PRODUCT_SORT_OPTION", $srs['sProductSortOption']); $pagination=$srs['sPagination']; $sTemplate=$srs['sTemplate']; $limit=MAX_ROWS_SEARCH; $class_tpl->assign('sDisPhoto',$canViewSearchPhoto); $class_tpl->assign('sDisTitle',$canViewSearchTitle); $class_tpl->assign('sDisPrice',$canViewSearchPrice); $class_tpl->assign('sDisDateAdded',$canViewSearchDateAdded); $class_tpl->assign('sDisExpiration',$canViewSearchExpiration); $class_tpl->assign('sDisCity',$canViewSearchCity); $class_tpl->assign('sDisState',$canViewSearchState); $class_tpl->assign('sDisCountry',$canViewSearchCountry); //do the query switch($page) { default: $where = " ORDER BY dateadded DESC"; } $select = $modules->call_hook('searchlistings_select', ''); // Call any module functions $wherehook= $modules->call_hook('searchquery_where', ''); // Call any module functions $sSQL="SELECT p.id, p.owner, p.title, p.display, p.featured, p.section, p.shortDescription, p.description, p.url, p.price, p.dateadded, p.expiration, p.pHighlighted, p.pBold, u.state, u.city, u.country".$select." FROM ".PREFIX."products AS p LEFT JOIN ".PREFIX."users AS u ON p.owner = u.id WHERE p.expiration > NOW() AND p.display = 'Y' ". $wherehook . $where; //get the number of listings $resultSet = $db->query($sSQL); $numrows = $resultSet->size(); $resultSet->freeResult(); $offset = ($pageNum - 1) * MAX_ROWS_SEARCH; $searchSQL = sprintf("%s LIMIT %d, %d", $sSQL, $offset, MAX_ROWS_SEARCH); $result=$db->query($searchSQL); $i=0; while ($row=$result->fetch()) { //now get the images $imageSQL = "SELECT image FROM ".PREFIX."prodimages WHERE pid=".$row['id']." ORDER BY rank LIMIT 1"; $iResult=$db->query($imageSQL); $irs=$iResult->fetch(); if($row['pBold']=="Y") { $pre="<strong>"; $post="</strong>"; } else { $pre=""; $post=""; } if($row['pHighlighted']=="Y") { $row['class']="highlighted"; } elseif($row['featured']=="Y") { $row['class']="featured"; } else { $row['class']=""; } if(defined('SEO')) { $listing_title=$row['title']; $listing_title=str_replace(' ', '_', $listing_title); $listing_title=preg_replace('/\W/e', '', $listing_title); $row['link']="listing/".$row['id']."/".$listing_title.".html"; } else { $row['link']="viewlisting.php?view=".$row['id']; } $row['image']=htmlspecialchars($irs['image']); $row['title']=$pre.safeStripSlashes($row['title']).$post; $row['shortDescription']=safeStripSlashes($row['shortDescription']); $row['price']=$pre.FormatCurrency($row['price']).$post; $row['state']=$pre.safeStripSlashes($row['state']).$post; $row['country']=$pre.safeStripSlashes($row['country']).$post; $row['url']=$pre.safeStripSlashes($row['url']).$post; $data[]=$row; }//end while $row=$result->fetch(); // part from viewlisting.php ///////////////////////////////////// $extraV=array(); $sSQL = "SELECT f.fID,sValue FROM ".PREFIX."products_fields AS p, `".PREFIX."fields` AS f WHERE p.pID='".$row['id']."' AND f.fSeen='Y' AND p.fID=f.fID ORDER BY f.fOrder ASC"; $result=$db->query($sSQL); $i=0; while ($rs=$result->fetch()) { $sql="SELECT fID,fName,fType FROM `".PREFIX."fields` WHERE fID='".$rs['fID']."' AND fSeen='Y' ORDER BY fOrder ASC"; $result2=$db->query($sql); $rs2=$result2->fetch(); $tmp = array( 'fID' => $rs['fID'], 'title' => $rs2['fName'], 'value'=> $rs['sValue'], ); $extraV[$i++] = $tmp; $i++; } $row['extraV']=$pre.safeStripSlashes($row['extraV']).$post; $result->freeResult(); $class_tpl->assign('extraV', $extraV); //process the template $class_tpl->assign('title', $title); $class_tpl->assign('page', $currentPage); $class_tpl->assign('first', $first); $class_tpl->assign('prev', $prev); $class_tpl->assign('next', $next); $class_tpl->assign('last', $last); $class_tpl->assign('pageNum', $pageNum); $class_tpl->assign('pageNumber', $pageNumber); $class_tpl->assign('maxPage', $maxPage); $class_tpl->assign('pagination', $pagination); $class_tpl->assign('results', $data); $class_tpl->assign('title', $title); $class_tpl->assign('cols', $Core->settings['mainIndexCols']); $class_tpl->assign('data', $cat); $class_tpl->assign('body','home.tpl.php'); $class_tpl->display('layout.tpl.php'); $db->disconnect(); but as I wrote it returns the extra fields from the last listing I think the code for extra fields should look like this: PHP Code: while ($row=$result->fetch()) { // part from viewlisting.php $extraV=array(); $sSQL = "SELECT f.fID,sValue FROM ".PREFIX."products_fields AS p, `".PREFIX."fields` AS f WHERE p.pID='".$row['id']."' AND f.fSeen='Y' AND p.fID=f.fID ORDER BY f.fOrder ASC"; $result=$db->query($sSQL); $i=0; while ($rs=$result->fetch()) { $sql="SELECT fID,fName,fType FROM `".PREFIX."fields` WHERE fID='".$rs['fID']."' AND fSeen='Y' ORDER BY fOrder ASC"; $result2=$db->query($sql); $rs2=$result2->fetch(); $tmp = array( 'fID' => $rs['fID'], 'title' => $rs2['fName'], 'value'=> $rs['sValue'], ); $extraV[$i++] = $tmp; $i++; } $row['extraV']=$pre.safeStripSlashes($row['extraV']).$post; $data[]=$row; }//end while $result->freeResult(); $class_tpl->assign('extraV', $extraV); but unfortunately it doesn't return any data for extra fields (I had to shorten the code) Can anyone help me with this? Last edited by darek; 04-05-2007 at 02:19 AM.

04-05-2007, 10:08 AM	#2
juven14 Moderator Join Date: Mar 2006 Location: Texas Posts: 2,191 Rep Power: 60	You need to include your extra fields inside of the while like the images are being done: PHP Code: while ($row=$result->fetch()) { //now get the images $imageSQL = "SELECT image FROM ".PREFIX."prodimages WHERE pid=".$row['id']." ORDER BY rank LIMIT 1"; $iResult=$db->query($imageSQL); $irs=$iResult->fetch(); if($row['pBold']=="Y") { $pre="<strong>"; $post="</strong>"; } //[....] //put your extra field code here, be sure not to overwrite any variables being defined like $row or etc. //[....] } __________________ John Snyder PHP Developer

04-05-2007, 11:17 AM	#3
darek Senior Member Join Date: Dec 2006 Posts: 105 Rep Power: 13	Yeah, I new that but I got an error from the image, but I solved it bu changing the name of $result to $Vresult, so the code looks like this now: PHP Code: $select = $modules->call_hook('searchlistings_select', ''); // Call any module functions $wherehook= $modules->call_hook('searchquery_where', ''); // Call any module functions $sSQL="SELECT p.id, p.owner, p.title, p.display, p.featured, p.section, p.shortDescription, p.description, p.url, p.price, p.dateadded, p.expiration, p.pHighlighted, p.pBold, u.state, u.city, u.country".$select." FROM ".PREFIX."products AS p LEFT JOIN ".PREFIX."users AS u ON p.owner = u.id WHERE p.expiration > NOW() AND p.display = 'Y' ". $wherehook . $where; //get the number of listings $resultSet = $db->query($sSQL); $numrows = $resultSet->size(); $resultSet->freeResult(); $offset = ($pageNum - 1) * MAX_ROWS_SEARCH; $searchSQL = sprintf("%s LIMIT %d, %d", $sSQL, $offset, MAX_ROWS_SEARCH); $result=$db->query($searchSQL); $i=0; while ($row=$result->fetch()) { //now get the images $imageSQL = "SELECT image FROM ".PREFIX."prodimages WHERE pid=".$row['id']." ORDER BY rank LIMIT 1"; $iResult=$db->query($imageSQL); $irs=$iResult->fetch(); if($row['pBold']=="Y") { $pre="<strong>"; $post="</strong>"; } else { $pre=""; $post=""; } if($row['pHighlighted']=="Y") { $row['class']="highlighted"; } elseif($row['featured']=="Y") { $row['class']="featured"; } else { $row['class']=""; } if(defined('SEO')) { $listing_title=$row['title']; $listing_title=str_replace(' ', '_', $listing_title); $listing_title=preg_replace('/\W/e', '', $listing_title); $row['link']="listing/".$row['id']."/".$listing_title.".html"; } else { $row['link']="viewlisting.php?view=".$row['id']; } //$row=$result->fetch(); $extraV=array(); $sSQL = "SELECT f.fID,sValue FROM ".PREFIX."products_fields AS p, `".PREFIX."fields` AS f WHERE p.pID='".$row['id']."' AND f.fSeen='Y' AND p.fID=f.fID ORDER BY f.fOrder ASC"; $Vresult=$db->query($sSQL); $i=0; while ($rs=$Vresult->fetch()) { $sql="SELECT fID,fName,fType FROM `".PREFIX."fields` WHERE fID='".$rs['fID']."' AND fSeen='Y' ORDER BY fOrder ASC"; $result2=$db->query($sql); $rs2=$result2->fetch(); $tmp = array( 'fID' => $rs['fID'], 'title' => $rs2['fName'], 'value'=> $rs['sValue'], ); $extraV[$i++] = $tmp; $i++; } $row['image']=htmlspecialchars($irs['image']); $row['title']=$pre.safeStripSlashes($row['title']).$post; $row['shortDescription']=safeStripSlashes($row['shortDescription']); $row['price']=$pre.FormatCurrency($row['price']).$post; $row['state']=$pre.safeStripSlashes($row['state']).$post; $row['country']=$pre.safeStripSlashes($row['country']).$post; $row['url']=$pre.safeStripSlashes($row['url']).$post; //$row['extraV']=$pre.safeStripSlashes($row['extraV']).$post; $data[]=$row; }//end while $result->freeResult(); $class_tpl->assign('extraV', $extraV); //process the template $class_tpl->assign('title', $title); $class_tpl->assign('page', $currentPage); $class_tpl->assign('first', $first); $class_tpl->assign('prev', $prev); $class_tpl->assign('next', $next); $class_tpl->assign('last', $last); $class_tpl->assign('pageNum', $pageNum); $class_tpl->assign('pageNumber', $pageNumber); $class_tpl->assign('maxPage', $maxPage); $class_tpl->assign('pagination', $pagination); $class_tpl->assign('results', $data); I don't have errors, but it displays the same extraV for all listings any idea how to make it change???? maybe the template should be changed? I still use the same PHP Code: `{foreach from=$extraV item=extras} <strong>{$extras.title}:</strong><br /> {if isset($extras.value)} {foreach key=key item=item from=$extras.value} {$item}<br /> {/foreach} {/if} {/foreach}`

04-05-2007, 11:26 AM	#4
juven14 Moderator Join Date: Mar 2006 Location: Texas Posts: 2,191 Rep Power: 60	You shouldn't need to mess around with looping the fields outside of the results. Modify your code to look like this: Replace this: PHP Code: `//inside while loop $tmp = array( 'fID' => $rs['fID'], 'title' => $rs2['fName'], 'value'=> $rs['sValue'], ); $extraV[$i++] = $tmp; $i++;` With this: PHP Code: `//inside while loop $fData['fID'] = $rs['fID']; $fData['title'] = safeStripSlashes($rs2['fName']); $fData['value'] = safeStripSlashes($rs['sValue']); $row['xFields'] = $fData;` Then in your template, where the results are being displayed: Code: {foreach from=$xFields item=item} $item.fID $item.title $item.value {foreachelse} something else {/foreach} __________________ John Snyder PHP Developer

04-05-2007, 11:55 AM	#5
darek Senior Member Join Date: Dec 2006 Posts: 105 Rep Power: 13	I saw this code in custom-fields-showlisting.zip, however mine was taken from original 3.15 version, Eric wrote it, and it nearly works perfect, the only thing is that it displays extras from one listing in all serched listings I did try your code, doesn't work when I change PHP Code: $sSQL = "SELECT f.fID,sValue FROM ".PREFIX."products_fields AS p, `".PREFIX."fields` AS f WHERE p.pID='".$row['id']."' AND f.fSeen='Y' AND p.fID=f.fID ORDER BY f.fOrder ASC"; PHP Code: `p.pID='".$row['id']."'` to PHP Code: `p.pID='1'` or PHP Code: `p.pID=2` I get display from first or second listing on all searched listings - so it almost work fine, almost... would you have some more ideas?

04-05-2007, 11:34 PM	#6
juven14 Moderator Join Date: Mar 2006 Location: Texas Posts: 2,191 Rep Power: 60	Ok, this works but you will need to test for specific field names to ensure your data pads for listings that don't have a value for each specific field. I created two fields, test1 and test2: PHP Code: $select = $modules->call_hook('searchlistings_select', ''); // Call any module functions $wherehook = $modules->call_hook('searchquery_where', ''); // Call any module functions $sSQL = "SELECT p.id, p.owner, p.title, p.display, p.featured, p.section, p.shortDescription, p.description, p.url, p.price, p.dateadded, p.expiration, p.pHighlighted, p.pBold, u.state, u.city, u.country" . $select . " FROM " . PREFIX . "products AS p LEFT JOIN " . PREFIX . "users AS u ON p.owner = u.id WHERE p.expiration > NOW() AND p.display = 'Y' " . $wherehook . $where; //get the number of listings $resultSet = $db->query($sSQL); $numrows = $resultSet->size(); $resultSet->freeResult(); $offset = ($pageNum - 1) * MAX_ROWS_SEARCH; $searchSQL = sprintf("%s LIMIT %d, %d", $sSQL, $offset, MAX_ROWS_SEARCH); $result = $db->query($searchSQL); $i = 0; while ($row=$result->fetch()) { //now get the images $imageSQL = "SELECT image FROM ".PREFIX."prodimages WHERE pid=".$row['id']." ORDER BY rank LIMIT 1"; $iResult=$db->query($imageSQL); $irs=$iResult->fetch(); if($row['pBold']=="Y") { $pre="<strong>"; $post="</strong>"; } else { $pre=""; $post=""; } if($row['pHighlighted']=="Y") { $row['class']="highlighted"; } elseif ($row['featured']=="Y") { $row['class']="featured"; } else { $row['class']=""; } if(defined('SEO')) { $listing_title=$row['title']; $listing_title=str_replace(' ', '_', $listing_title); $listing_title=preg_replace('/\W/e', '', $listing_title); $row['link']="listing/".$row['id']."/".$listing_title.".html"; } else { $row['link']="viewlisting.php?view=".$row['id']; } $xfSQL = "SELECT f.fID, f.fName, sValue FROM " . PREFIX . "products_fields AS p, `" . PREFIX . "fields` AS f WHERE p.pID='" . $row['id'] . "' AND f.fSeen='Y' AND p.fID=f.fID ORDER BY f.fOrder ASC"; $xfResult = $db->query($xfSQL); while ($xfRS = $xfResult->fetch()) { $fID = $xfRS['fID']; $fName = safeStripSlashes($xfRS['fName']); $sValue = safeStripSlashes($xfRS['sValue']); $row[$fName] = $sValue; } if (!isset($row['test1'])) { $row['test1'] = ''; } if (!isset($row['test2'])) { $row['test2'] = ''; } $row['image']=htmlspecialchars($irs['image']); $row['title']=$pre.safeStripSlashes($row['title']).$post; $row['shortDescription']=safeStripSlashes($row['shortDescription']); $row['price']=$pre.FormatCurrency($row['price']).$post; $row['state']=$pre.safeStripSlashes($row['state']).$post; $row['country']=$pre.safeStripSlashes($row['country']).$post; $row['url']=$pre.safeStripSlashes($row['url']).$post; $data[]=$row; }//end while Note this part: PHP Code: `if (!isset($row['test1'])) { $row['test1'] = ''; } if (!isset($row['test2'])) { $row['test2'] = ''; }` Here is the part you need to custom tailor for your site fields. Remember by doing it this way you need to add the field names in on your own for the table heading. Also, the way you and I have it setup is to display all fields. If you want to control that you must put in a test for the field id, or call specific ids only. i.e. Code: $xfSQL = "SELECT f.fID, f.fName, sValue FROM " . PREFIX . "products_fields AS p, `" . PREFIX . "fields` AS f WHERE p.pID='" . $row['id'] . "' AND f.fSeen='Y' AND p.fID=f.fID AND f.fID=1 ORDER BY f.fOrder ASC"; Then just make a call for each field you want to display and giving it a unique key in the $row array. ...fID = 1 $row['field1'] = ... __________________ John Snyder PHP Developer

04-06-2007, 09:28 AM	#7
darek Senior Member Join Date: Dec 2006 Posts: 105 Rep Power: 13	John, THANK YOU VERY MUCH!!! and I mean it. Ispent probably 30 or 40 hours trying to get the data from array in the first page. Not only your last post solved the problem but also made it all work!! I lost hope I could do it myself. Thank you once more.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode