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' )

Setting form requirements for a FuseDataController

The below code is taken from an application I'm working on where customer service representatives can track calls, which are linked to contacts. I wanted to give the representatives the ability to add a contact at the same time as a call, if they choose. The form requirements below require that a call[datetime_date] always be set, and that the contact[f_name], etc... are set *if* the form input add_new_contact has a value of 1. It also forces call[datetime_date] to be in mm/dd/YYYY format using the 'regexp' option

 public $form_requirements = array( 
  'contact[f_name]' => 
  array( 'friendly_name' => 'the contact\'s first name', 
   'required_if' => array('add_new_contact' => array('value' => 1, 'type' => 'textbox' )) ),

         'contact[l_name]' => 
  array( 'friendly_name' => 'the contact\'s last name', 
       'required_if' => array('add_new_contact' => array('value' => 1, 'type' => 'textbox' )) ),
   
  'contact[address1]' => 
  array( 'friendly_name' => 'the first line of the address', 
   'required_if' => array('add_new_contact' => array('value' => 1, 'type' => 'textbox' )) ),

  'contact[city]' => 
   array( 'friendly_name' => 'a city',
    'required_if' => array('add_new_contact' => array('value' => 1, 'type' => 'textbox' )) ),
  
                'contact[zip_code]' => 
   array( 'friendly_name' => 'a zip code',
    'required_if' => array('add_new_contact' => array('value' => 1, 'type' => 'textbox' )) ),
   
         'call[datetime_date]' => 
  array( 'friendly_name' => 'the call date', 
  'regexp' => '/\d{2}\/\d{2}\/\d{4}/' )
 );

In my controller, I used a version of adding to multiple tables from one form to add both the contact and the call.

Setting a date or datetime field to current date or time

If you need a datetime or datetime field to reflect the current date and/or time when adding, use the following in your controller:

public function before_add() {

   //assuming our datetime field is called 'entry_datetime'
   $this->model->set_db_param_type( 'entry_datetime', 'function' ); //tell the model that we don't want a literal 'NOW()' inserted
   $this->model->entry_datetime = 'NOW()';

}

You can also add the same code to before_edit() if you need the same functionality when editing

Using a helper to count items in an iterator

today the example is for a site that organizes web links and associates them with tags. In order to count the tags while listing data, we do something like this:

views/Link/Link-List.tmpl

<table>
  <tr>
    <th>URL</th>
    <th>Tags</th>
  </tr>

  <{ITERATOR links}>
  <tr>
    <td><{url}></td>
    <td>
        <{IF (LinkHelper::Count_tags_by_link_id(id) > 0)}>
        <{ITERATOR get_tags()}>
          <{name}><br />
        <{/ITERATOR}>
        <{ELSE}>
        (none)
        <{/IF}>
    </td>
  </tr>
  <{/ITERATOR}>
</table>

include/LinkHelper.class.php

public static function Count_tags_by_link_id( $link_id ) {

  FUSE::Require_model( 'Link' );
  
  $link = new Link();
  $link->id = $link_id;

  return $link->tags->count_all();
}

models/Link.class.php

public function get_tags() {
  return $this->tags->fetch_all();
}

Adding to multiple tables from one form

Today I had a situation where I needed to add to the customer_addresses table when creating a new entry in the customers table. Fuse made this a simple exercise:

views/Customer/Customer-Edit.tmpl

<{IF message}>
<div style="margin-bottom: 8px; font-weight: bold; text-align:center; padding: 2px;">
  <{message}>
</div>
<{/IF}>

<{form_script_tag}>
<{form_tag}>

  
<div style="margin-top: 6px;">First Name</div>
<div><{HTML/FormHelper::text_field('Customer', 'customer_f_name') }></div>
  
<div style="margin-top: 6px;">Last Name</div>
<div><{HTML/FormHelper::text_field('Customer', 'customer_l_name') }></div>
  
<div style="margin-top: 6px;">Email</div>
<div><{HTML/FormHelper::text_field('Customer', 'customer_email') }></div>
  
<div style="margin-top: 6px;">Address Line 1</div>
<div><{HTML/FormHelper::text_field('CustomerAddress', 'l1') }></div>
  
<div style="margin-top: 6px;">Address Line 2</div>
<div><{HTML/FormHelper::text_field('CustomerAddress', 'l2') }></div>
  
<div style="margin-top: 6px;">City</div>
<div><{HTML/FormHelper::text_field('CustomerAddress', 'city_name') }></div>
  
  
<div style="margin-top: 6px;">State</div>
<div><{HTML/FormOptionsHelper::Select_all('State', 'name', 'abbr', array('for_model' => 'CustomerAddress', 'for_field' => 'state_abbr')) }></div>
  
<div style="margin-top: 6px;">Zip</div>
<div><{HTML/FormHelper::text_field('CustomerAddress', 'zip_code') }></div>
  
<div>
  <input type="submit" value="Save Changes" />
</div>
  
</form>

controllers/CustomerController.class.php

 public function after_add() {
  
  if ( $this->is_postback() ) {

   FUSE::Require_model('CustomerAddress');
     
   $address = new CustomerAddress();
   $address->customer_id = $this->customer->id;
   $address->add_from_form();
 
   $this->redirect( 'customer/view/' . $this->model->id );
  }
   
 }

More information on Fuse can be found at http://www.phpfuse.net

Using a view for css so you can use variables in .css files

Oftentimes, you are developing in an environment that has a different base uri (e.g. http://localhost/projects/myproject) than the final project will have (http://www.projectdomain.com).

This isn't a problem in normal views/templates in FUSE, because you have the SITE_BASE_URI variable available to you. However, regular CSS files aren't parsed by the templating engine, so you have to explicitly specify the url for, say, background images. Let's take a look at how we can use FUSE to get our SITE_BASE_URI and other variables embedded in the css file so they work in any environment.

Here is our CSS file, mystylesheet.css

body {
   /* this image only loads if our base uri is / */
   background: url(/images/body_bg.jpg);
}

The problem is that some developers may be working at a base uri of /projects or /projectname, etc, so linking to / will cause the image not to load. We can get around this in Fuse by creating a controller for CSS files and having them parsed as templates. Here's how:

1. Create the controller

controllers/CSSController.class.php

<?php

FUSE::Require_class('AppControl/FuseApplicationController');

class CSSController extends FuseApplicationController {

 public $skip_pre_action = true;
 public $header_auto_render = false;
 public $footer_auto_render = false;

 public $template_directory = 'css';
 
    function load() {
     
     if ( $this->filename ) {
      
      
      $this->set_template_filename( "{$this->template_directory}/$this->filename", array( 'append_extension' => false ) );
      
      header('Content-Type: text/css');
      $this->render(); 
      
     }
     
    }
    
}
?>

In the controller above, we're explicitly setting the template file to be "css/$this->filename", where $this->filename will be the .css filename from the route itself, as we'll see below.

2. Add the route

adding our route for css/load/{filename} to config/routes.conf.php:

FuseURIRouter::route_connect( 'css/load/:filename', array( 
      'action' => 'load',
      'controller' => 'CSS', 
      'requirements' => array( 'filename' => '/.+/' )
       ,
       'static_cache' => array( 'always' => true, 'id' => 'filename', 'headers' => 'Content-type: text/css' ),      
     )
       );  

3. Move the css file to views/css and add variables

views/css/mystylesheet.css

body {
   background: url(<{SITE_BASE_URI}>/images/body_bg.jpg);
}

4. Link the CSS file in the header

We created our route for loading CSS templates as css/load/{cssfilename}, so let's now link our new css template in the header:

adding to views/Layout/default/default-header.tmpl:

<head>

<!-- Other head elements -->

<link rel="stylesheet" type="text/css" media="all" href="<{SITE_BASE_URI}>/css/load/mystylesheet.css" />

</head>

And there you have it. You may notice that in the route the we are statically caching the css file. This is because it takes server overhead to load Fuse for each CSS file, but if we statically cache the file (which saves it as a regular css file), Fuse doesn't have to load, so our overhead is kept to a minimum. You can read more about Fuse's static cache at http://phpfuse.net/wiki/index.php?title=Static_Page_Caching

More information on the Fuse MVC Framework for PHP can be found at http://www.phpfuse.net