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'),
);
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
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
Hi Hiroko,
Unfortunately the search system doesn't have the ability to leftJoin on a table, but you could use the method below to get the linked record:
if(@$_REQUEST['search']){
// search viewer code begins
$searchOptions = array();
$searchOptions['keywords'] = @$_REQUEST['q'];
$searchOptions['perPage'] = "10";
$searchOptions['debugSql'] = "0";
$searchOptions['orderBy'] = "RAND()";
$searchTables = array();
//Blogs
$searchTables['blog'] = array(
'viewerUrl' => 'blog.php',
'titleField' => 'title',
'summaryField' => 'content',
'searchFields' => array('title','content'),
'field1' => 'category',
'field2' => 'createdByUserNum',
'debugSql' => true,
);
list($searchRows, $searchDetails) = searchMultipleTables($searchTables, $searchOptions);
// search viewer code ends
}
?>
<!DOCTYPE html>
<html>
<head>
</head>
<body>
<form method="get" action="?">
<input type="text" name="q" value="<?php echo htmlEncode(@$_REQUEST['q']); ?>" />
<input type="submit" name="search" value="search" />
</form>
<?php foreach($searchRows as $row): ?>
<h1><?php echo htmlEncode($row['_title']); ?></h1>
<?php echo $row['_summary']; ?>
<?php if(@$row['field2']){ $account = mysql_get('accounts', intval($row['field2'])); } ?>
<?php if($account): ?>
<p><?php echo $account['fullname']; ?></p>
<?php endif; ?>
<?php endforeach; ?>
</body>
</html>
This is example code, you'll need to adapt it for your search system. The method above works by adding the field that is leftJoined into field2 of my search results for the blog. Then as the code cycles through the results, if field2 is set, it gets the linked account using mysql_get, and displays the user's name. This method would work for the event search as well.
Let me know if you have any questions on implementing this.
Cheers,
Greg
PHP Programmer - interactivetools.com