Automatic PHP SQL Searching with Fuse

The FuseDataController has powerful data search capabilities built right in. All you have to do is set up inputs in your view that are named as "search_field_name" (a Helper for this is in the works), then mark those fields as searchable in your controller. Below is an example from a project I worked on where we had to search products by various criteria

ProductController.class.php

FUSE::Require_class('AppControl/FuseDataController');
class ProductController extends FuseDataController {

        public $paginate = array( 'items_per_page' => 10, 'pages_linked_through_uri' => false );


 public $search_fields = array( 
  'product_title' => array('filter_type' => 'like' ),
  'product_number' => array('filter_type' => 'wildcard_right' ), 
  'product_customer_number' => array( 'filter_type' => 'wildcard_right', 'db_field' => 'customer_assignments.customer_assignment_part_number'),
  'product_factory_number' => array( 'filter_type' => 'wildcard_right'), 
  'product_type_id' => array( 'filter_type' => 'direct')
 );

        // We need to join the customer_assignments table, but only
        // if a product_customer_number was specified 
        //
 public $search_settings = array( 
   'join_if' => array( 'product_customer_number' => 'LEFT JOIN customer_assignments ON customer_assignments.product_id = products.product_id')
 
 );
}

Supported options for filter_type are:

fulltext
greater_than
greater_than_equal
in
less_than
direct (will search using a direct comparison, e.g. myfield=34)
like (default - where myfield like '%value%')
like_insensitive (case insensitive version of like)
wildcard_left (myfield like '%value')
wildcard_left_i (myfield like '%value' - case insensitive)
wildcard_right (myfield like 'value%')
wildcard_right_i (myfield like 'value%' - case insensitive)
date_interval (expects dropdowns called search_mydatefield_month_to, _day_to, year_to, _day_from, _month_from, _year_from)

views/Product/Product-Search.tmpl

<script language="Javascript" type="text/javascript" src="<{SITE_BASE_URI}>/script/DomControl.js"></script>
<script language="Javascript" type="text/javascript" src="<{SITE_BASE_URI}>/script/Pagination.js"></script>

<{HTML/ListHelper::Pagination_setup('Product')}>

<form action="?" method="get">
<input type="hidden" name="start_search" value="1" />
<fieldset style="margin-top: 12px;">
<legend>Product Search</legend>

<div class="input_container">
<div class="form_label">Product Name:</div>
<div class="form_input"><input id="textbox_search_product_name" type="text" name="search_product_name" /></div>
</div>

<div class="input_container">
<div class="form_label">Customer Part Number:</div>
<div class="form_input"><input id="textbox_search_product_customer_number" type="text" name="search_product_customer_number" /></div>
</div>

<div class="input_container">
<div class="form_label">Factory Part Number:</div>
<div class="form_input"><input id="textbox_search_factory_part_number" type="text" name="search_product_factory_part_number" /></div>
</div>

<div class="input_container">
<div class="form_label">Product Type:</div>
<div class="form_input"><{HTML/FormOptionsHelper::Select_all('ProductType', 'name', 'id', array('empty_value_text' => '-Any-', 'input_name' => 'search_product_type_id'))}></div>
</div>

<div style="margin-top: 5px; clear:both; text-align:center;">
<input type="submit" value="Search" />
</div>
</form>

</fieldset>

<{IF search_is_active}>
<fieldset style="margin-top: 12px;">
<legend>Results</legend>

<div id="page_links" style="float:right; margin-top: 5px;"></div>
<{HTML/ListHelper::Pagination_generate('Product')}>
<div class="clearer"></div>
</div>
<div style="margin-top: 4px;">
<table class="list_table" style="width:100%;">
  <tr>
    <th style="width: 120px;">Picture</th>
    <th colspan="">Title</th>
    <th colspan="">Details</th>
    <th colspan="">Description</th>
   </tr>
  <{ITERATOR products}>
  <tr style="cursor:pointer;" onclick="document.location.href='<{$_SITE_BASE_URI}>/product/view/<{id}>'" class="row<{HTML/ListHelper::Cycle('1','2')}>">
       <td><img src="<{$_SITE_BASE_URI}>/photos/products/<{print(File/FilePath::Range_dir_name( id ))}>/thumb_browse/<{photo_filename}>" /></td>
       <td style="width: 140px;"><{title}></td>
     <td style="width: 140px;"><{print(str_replace('|', '<br />', product_detail_string) )}></td>
     <td><{Output/OutputHelper::Substr_echo( description, 0, 300, array('more_link' => '...') ) }></td>
  </tr>
  <{/ITERATOR}>
</table>
</div>
</fieldset>
<{/IF}>

Note: by default, the search checks $_GET for the search input values. To change to post, add to your $search_settings member as follows:

public $search_settings = array( 'form_submit_method' => 'post' )