Get fieldname:label in searchFields

4 posts by 3 authors in: Forums > CMS Builder
Last Post: February 25, 2016   (RSS)

By zaba - February 17, 2016

Hi I need to get the search to search on the list label rather than the value, as the value is referencing a num from a different table, when I use cat:label and sub_cat:label in the example below, I get the error 

Search Multiple Tables: MySQL Error: Unknown column 'cat:label' in 'field list'.

Is there any way around this?

What I did try as a workaround, in the editor (for products) was to create another list cat_search that would auto populate with the cat field as a radio with both value and label as the same, using:

SELECT cat
FROM `<?php echo $TABLE_PREFIX ?>categories` 
WHERE num='<?php echo $ESCAPED_FILTER_VALUE ?>'
LIMIT 1

Advanced Filter: Refresh list when this field changes: cat"

and then use cat_search as the search field, but I could not get the radio box to be automatically checked, so it didn't work.

Heres the master search code that doesn't work

/* search tables */  
       $searchOptions = array();
      $searchOptions['keywords'] = @$FORM['q'];
      $searchOptions['perPage']  = "12";
      $searchOptions['debugSql'] = "0";

      $searchTables = array();
      $searchTables['products'] = array(
        'viewerUrl'       => 'products.php',
        'titleField'      => 'title',
        'summaryField'    => 'product_details',
        'imagesField'    => 'image_gallery',
        'searchFields'    =>  array('title',"cat:label","sub_cat:label",'brand','product_details'),
      );

By gregThomas - February 17, 2016

Hi Zaba, 

Unfortunately the :label fields are meta fields that are added to the getRecords function and aren't searchable as the data isn't stored in the database. Searching these fields is outside what is possible with searchMultipleTables function, you'd have to write a custom search system that left joins to the product and category tables. 

Perhaps you could add the categories as a separate search item, and then configure the results so that the user can view all of the products that fall under that category on a separate page?

Cheers,

Greg

Greg Thomas







PHP Programmer - interactivetools.com

By hiroko - February 25, 2016

Hi,

I am trying to do a left join in my search result page but cannot get it to work.

Is this done the same way as in getRecords? and also in the viewer?

This is what I have

 // search viewer code begins
  $searchOptions = array();
  $searchOptions['keywords'] = @$FORM['q'];
  $searchOptions['perPage']  = "10";
  $searchOptions['debugSql'] = "0";
   $searchOptions['orderBy'] = "RAND()";

  $searchTables = array();
  
  // accounts
  $searchTables['accounts'] = array(
    'viewerUrl'       => 'galleryDetail.php',
    'titleField'      => 'fullname',
    'summaryField'    => 'content',
    'searchFields'    =>  array('fullname','fullname_jh','fullname_e','address','address_e','keywords_j','keywords','content','content_e'),
'field1'    =>  'keywords_j',
'field2'    =>  'num',
'field4'    =>  'fullname_jh',
  );
  
  
  // events
  $searchTables['event'] = array(
    'viewerUrl'       => 'eventDetail.php',
    'titleField'      => 'title_j',
    'summaryField'    => 'content_j',
    'searchFields'    =>  array('title','title_j','keywords_j','keywords','content_j','content'),
'field1'    =>  'keywords_j',
'field3'    =>  'event_end',
'field5'    =>  'gallery',
'leftJoin'      => array(  
  // foreign table => local field (that matches num in foreign table)  
'accounts' => 'gallery',
  );
  
  list($searchRows, $searchDetails) = searchMultipleTables($searchTables, $searchOptions);
  // search viewer code ends

and on the viewer code

<?php echo $record['accounts.fullname'] ?>

Can you give me advise on how to set this correctly?

Thank you,

HIroko