Searching Values in a List

Hi Perch,

Here's another approach that I've used to search for records where there 's a match in a field value that's generated from a pull down. The actual option values are from other table that I set up to keep the values identical and allow the client to add values without needing to reconfigure the section editor.

<form method="POST" action="<?php echo $_SERVER['PHP_SELF'] ?>">

<select name="media_type_keyword">
<option value="">Choose The Media Type To Display</option>
<?php foreach ($media_typesRecords as $record): ?>
<option value="<?php echo $record['num'] ?>"><span class="body-text"><?php echo $record['title'] ?></span></option>
 <?php endforeach ?>

</select>

<select name="specialty_group_keyword">
<option value="">And/Or Choose The Specialty To Display</option>
<?php foreach ($specialty_groupRecords as $record): ?>
<option value="<?php echo $record['num'] ?>"><span class="body-text"><?php echo $record['title'] ?></span></option>
 <?php endforeach ?>

</select>

<select name="source_keyword">
<option value="">And/Or Choose The Source</option>
<?php foreach ($sourceRecords as $record): ?>
<option value="<?php echo $record['num'] ?>"><span class="body-text"><?php echo $record['title'] ?></span></option>
 <?php endforeach ?>

</select>

<input type="submit" name="submit" value="Search">
</form> </td>
    <td align="left" valign="top"> <FORM ACTION="">

<INPUT TYPE="submit" VALUE="Cancel Search Filters">
</FORM>

Also, here's a recipe from my CMSB Cookbook http://www.thecmebcookbook.com that might help.

CREATING A PRODUCT CATEGORY SEARCH FORM THAT POPULATES IT'S OPTIONS LIST FROM VALUES IN EXISTING RECORDS - Dec 27th, 2012


I’m a big fan of pulling list values from a multi-record master category table. That way, there are no misspellings to throw off subsequent searches.

I had a fairly comprehensive list of categories that could be assigned to products, however, I wanted the viewer search filter form to offer only those category choices that would render results and I didn’t want to have any duplicates in the options list.

The multi-record master category section (mobility_aid_categories) has only one text field (category)

The multi-record section that contains the actual listings records (mobility_aids) has a required list field (mobility_aid_category) that pulls it’s option labels from the category field and it’s option values from the record number field of the mobility_aid_categories table. Each listing record must be assigned to a specific category.

With a bit of help from Greg Thomas a programming guru at Interactive Tools, we came up with the following.

The load records call code (with a variable $categorygroup defined) in the viewer looks like this:
_____ code ________________________________________________
<?php
// load records from 'mobility_aids'
list($mobility_aidsRecords, $mobility_aidsMetaData) = getRecords(array(
'tableName' => 'mobility_aids',
'loadUploads' => true,
'allowSearch' => true,
));

list($mobility_aid_categoryRecords, $mobility_aid_categoryMetaData) = getRecords(array(
'tableName' => 'mobility_aid_category',
));

$categorygroup = array_filter(array_pluck($mobility_aid_categoryRecords, 'category'));

?>
__________________________________________________________
The viewer search form code looks like this:

_____ code ________________________________________________

<form method="POST" action="<?php echo $_SERVER['PHP_SELF'] ?>">
<?php
//Create an array to store the values that have been selected
$selectArray = array();
//foreach section item
foreach($mobility_aidsRecords as $record){
//If the record has something selected for mobility_aid_category
if(@$record['mobility_aid_category:label']){
//If the selected value isn't already in the array
if(!in_array(@$record['mobility_aid_category:label'],$selectArray)){
//Add it to the $selectArray
$selectArray[$record['mobility_aid_category']] = $record['mobility_aid_category:label'];
}
}
}
?>
<div class="body-text">
<select name="mobility_aid_category" >
<option value= "">Choose The Category To Display And Click/Tap The Search Button</option>
<?php foreach ($selectArray as $value => $name): ?>
<option value="<?php echo $value; ?>"><?php echo $name; ?></option>
<?php endforeach; ?>
</select>
</div>
<br /><input type="submit" name="submit" value="Search">
&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp;
<input type="submit" value="Click/Tap To Cancel Search Filters" />
</form>
__________________________________________________________

and the viewer code for the listings themselves look like this:
_____ code ________________________________________________

<?PHP
function wordCount($textOrHtml) {
$text = strip_tags($textOrHtml, "<b></b><i></i>");
$words = preg_split("/\s+/", $text);

return count($words);
}
?>
<table width="100%" align="center"border="0" cellpadding="10">
<tr>
<td><?php foreach ($categorygroup as $group): ?>
<div class=" heading-text-12"><?php foreach ($mobility_aidsRecords as $record): ?><?php if ($record['mobility_aid_category:label'] == $group) : ?> <?php echo strtoupper($group); ?><?php break ?><?php endif; ?><?php endforeach ?>
</div>
<?php foreach ($mobility_aidsRecords as $record): ?>
<?php if ($record['mobility_aid_category:label'] == $group) : ?>
<?php $name = htmlspecialchars($record['name']); ?>
<table border="0">
<tr>
<td width="150" height="150" align="center" valign="middle"><?php foreach ($record['image'] as $index => $upload): ?>
<a href="<?php echo $record['_link']; ?>"><img src="<?php echo $upload['thumbUrlPath'] ?>" width="<?php echo $upload['thumbWidth'] ?>" height="<?php echo $upload['thumbHeight'] ?>" alt="" /></a>
<?php endforeach ?>
</td>
<td>
<a href="<?php echo $record['_link']; ?>"><span class="heading-text-12"><b><?php echo strtoupper($name); ?></b></span></a><br /> <br />
<div align="left" class=" body-text-9"><?php echo maxWords($record['description'], 25); ?>
<?php $words = 25 ?>
<?php if (wordCount($record['description']) > $words) : ?>
<span class=" body-text-9">...</span><a href="<?php echo $record['_link']; ?>"><span class=" body-text-bold-italic-10"> READ MORE</span></a></td>
</tr>
</table>

Best,

Jerry Kornbluth

Hi, Jerry -

Thanks for this. However, I really need to crack the piece of code I posted at the top - if only so I learn a bit more!

As I said, I want to match a number in the search. However, the current $where clause doesn't work properly.

This is the normal code:

foreach ($fieldsToSearch as $field) {       
        array_push($searchClauses, "$field LIKE '%" . mysql_escape($search_string) . "%'");     
      }     
      $where = join(' OR ', $searchClauses);   }


However, this will not work when trying to match on a single number as a search for  "2" will match 12,  20, 102, etc....

:0/

Perch

Fixed it.

I needed to change this...

foreach ($fieldsToSearch as $field) {       
        array_push($searchClauses, "$field LIKE '%" . mysql_escape($search_string) . "%'");     
      }     
      $where = join(' OR ', $searchClauses);   } 

to this...

foreach ($fieldsToSearch as $field) {       
        array_push($searchClauses, "$field IN ('" . mysql_escape($search_string) . "')");    
      }     
      $where = join(' OR ', $searchClauses);   } 

There are a number of subtle changes to the apostrophes and quotes wrapped around $search_string that made all the difference!

:0)

Perch

Good Job

Jerry Kornbluth