Search labels
5 posts by 3 authors in: Forums > CMS Builder
Last Post: February 3, 2014 (RSS)
By samsam - January 28, 2014
Hi,
I know this topic has been already answered a while back but maybe someone figured out a solution or alternative for this problem.
We need to search Products by keywords. The keyword input field would search through basic textfields, textboxes but also list fields with data from another table.
We don't want to have a dropdown select field made of those external data lists, it really needs to be part of the same and unique keyword box.
Here is how the product table is made of:
<?php foreach ($productsRecords as $record): ?>
Record Number: <?php echo htmlencode($record['num']) ?><br/>
Stock #: <?php echo htmlencode($record['title']) ?><br/>
Gem Type (value): <?php echo $record['gem_type'] ?><br/>
Gem Type (label): <?php echo $record['gem_type:label'] ?><br/>
Featured? (value): <?php echo $record['featured'] ?><br/>
Featured? (text): <?php echo $record['featured:text'] ?><br/>
Weight (Carats): <?php echo htmlencode($record['weight_carats']) ?><br/>
# of pieces: <?php echo htmlencode($record['of_pieces']) ?><br/>
Gem Variety: <?php echo htmlencode($record['gem_variety']) ?><br/>
Shape/ Cut (value): <?php echo $record['shape_cut'] ?><br/>
Shape/ Cut (label): <?php echo $record['shape_cut:label'] ?><br/>
Measurements: <?php echo htmlencode($record['measurements']) ?><br/>
Origin: <?php echo htmlencode($record['origin']) ?><br/>
Description: <?php echo htmlencode($record['content']) ?><br/>
<?php endforeach ?>
Basically we need to search the title, gem_type:label, gem_variety, shape_cut:label and content fields.
If you have a solution, that would be greatly appreciated.
Thank you
By Daryl - February 3, 2014
Hi Samsam,
What I would do is I will build a where clause for all the fields and related list fields because I don't think that using the allowSearch => true will include the searching of list fields from another table.
Here's an example:
$where = '';
if (@$_REQUEST['searchKeyword']){
$where .= 'title LIKE "%'.mysql_escape($_REQUEST['searchKeyword']).'%" OR content LIKE "%'.mysql_escape($_REQUEST['searchKeyword']).'%"';
$loadGemTypeRecords = mysql_select('gem_type', 'title LIKE "%'.mysql_escape($_REQUEST['searchKeyword']).'%"');
$loadShapeCutRecords = mysql_select('shape_cut', 'title LIKE "%'.mysql_escape($_REQUEST['searchKeyword']).'%"');
if (@$loadGemTypeRecords){
$gemTypeRecordNums = array_pluck($loadGemTypeRecords, 'num');
$where .= ' OR gem_type IN ('.mysql_escapeCSV($gemTypeRecordNums).')';
}
if (@$loadShapeCutRecords){
$shapeCutRecordNums = array_pluck($loadShapeCutRecords, 'num');
$where .= ' OR shape_cut IN ('.mysql_escapeCSV($shapeCutRecordNums).')';
}
// load records from 'products'
list($productsRecords , $productsMetaData) = getRecords(array(
'tableName' => 'products',
'loadUploads' => false,
'allowSearch' => false,
'where' => $where,
));
}
Hope this helps!
Cheers!
PHP Programmer - interactivetools.com
By samsam - February 3, 2014
Hi Daryl
Thanks so much for taking the time. We'll give a shot and let you know!
By gregThomas - February 3, 2014 - edited: February 3, 2014
Hi samsam,
The best way to do this is using a getRecords with a leftjoin variable. I've set up a demo of how you could implement the system below:
<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php
// load viewer library
$libraryPath = 'cmsAdmin/lib/viewer_functions.php';
$dirsToCheck = array('/home/greg/www/','','../','../../','../../../');
foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }
if(@$_REQUEST['searchTerm']){
$searchTerm = @$_REQUEST['searchTerm'];
// load records from 'cars'
list($carsRecords, $carsMetaData) = getRecords(array(
'tableName' => 'cars',
'where' => "brands.`title` LIKE '%$searchTerm%' OR cars.`title` LIKE '%$searchTerm%' OR cars.`content` LIKE '%$searchTerm%'" ,
'loadUploads' => true,
'allowSearch' => true,
'leftJoin' => array('brands' => 'brand'),
));
}
?>
<form method="get" action="scratch.php">
<input type="text" name="searchTerm" value="<?php echo @$_REQUEST['searchTerm']; ?>" />
<input type="submit" name="go" value="Go!" />
</form>
<ul>
<?php if(@$carsRecords): ?>
<?php foreach($carsRecords as $car): ?>
<li><?php echo $car['title']; ?></li>
<?php endforeach; ?>
<?php endif; ?>
</ul>
I set up my sections so that each car has a brand, which is linked via its num value to the brand section.
https://docs.google.com/a/interactivetools.com/file/d/0BwTyoYnBEdlANTA1dy1yRWRaNG8/edit?usp=drivesdk
Firstly I told the getRecords function that brand field was linked to the brands section by adding a leftJoin (highlighted in orange). The getRecords function will link on the num value stored in the brand field to the num value of the a brands section record. You can add as many left joins to the leftJoins array as you require.
Then I added a where statement that will search the title field of the brand as well as the title field of the car, and its content field (highlighted in green). If you do a left join, you have to start each field in your where statement with the section that the item is stored in.
The results returned by the getRecords function are displayed at the bottom of the page in the $carsRecords for each loop.
Let me know if you have any questions about using this method.
Thanks!
Greg
PHP Programmer - interactivetools.com