CakePHP Tips and Tricks by Rtconner

Static Data Helper

July 27, 2007

Static Data in my Database? Why would I want to store data in the database that never changes? First, with proper usage you can minimize the load on your database server by permanently caching the results of the queries. Secondly, it becomes easy to JOIN to when you do need it. You never should need it though. Third, it becomes very easy for you as a developer to get data from a single row or get a full list of data with which you can create a <SELECT> dropdown. Fourth, It becomes exceedingling easy to say add a 51st state to your website. Just add a row to the table, and delete the cache. Perhaps adding an Order status at the request of a customer is a more common thing. A very simple to understand example of this is states. All you need to do is store all states in your database, and say you want to store where each user in your system lives. In the Users table create a state_id field. After you have installed the Helper, you can simply call <?= $static->status($User, 'name'); ?> to get the name of the state. This will not query your database, and will add zero extra load onto your database server. And notice how easy it was for you to write in code. Some real life usage examples <div class="code"><pre> <?php echo $form->input('country_id', array('options' => $static->countries('id', 'name'))); ?>  <?php echo $static->status(40, 'desc'); ?>  <?= $form->select('user/state_id', $static->states('id', 'abbrev'), $user['User']['state_id']); ?> </pre></div> <div style='display:none'> <?php echo $form->input('country_id', array('options' => $static->countries('id', 'name'))); ?>  <?php echo $static->status(40, 'desc'); ?>  <?= $form->select('user/state_id', $static->states('id', 'abbrev'), $user['User']['state_id']); ?> </div> Sorry, you have to get the data to put into your database on your own. This Helper will cache the data from that data for 1 year, making it so the data is almost never selected from your database. Installation1. Take the code below and put it into the file app/views/helpers/static.php 2. Config the file if you want (I suggest changing the $tmp_folder var) 3. In your controller add var $helpers = array('Static'); The Code (intended to go in a helper named static.php) <div class="code"><pre><?php /** * CakePHP Helper to handle use of data in database that never changes * * array plural($order, $field='name'); // ready for use in FormHelper::select * string singular($id, $field='name'); * * usage examples (in Views): * * $static->states('abbrev'); // all states sorted by abbrev * $static->statuses('id', 'name'); // get list of all status names, sorted by id * $static->status(40, 'desc'); // description of status with id 40 * $status->countries(); // get all countries sorted by id * * @author Rob Conner aka rtconner */ class StaticHelper extends Helper { // set this to a folder within your app/tmp folder to store the data (no trailing slash) var $tmp_folder = 'cache'; var $cache_expires = '+1 Year'; var $database = 'default'; //plural only please, will enable both plural and singular methods (status needs to be added to inflections.php) var $methods = array('states', 'countries', 'statuses'); function __call($method, $args) { $plural = Inflector::pluralize($method); if(in_array($plural, $this->methods)) { array_unshift($args, $method); if($plural==$method) // return list of all static items return call_user_func_array(array(&$this, 'all'), $args); else // return single item return call_user_func_array(array(&$this, 'row'), $args); } else // try to imitate php's errors trigger_error(sprintf('Call to undefined function: %s::%s().', get_class($this), $method), E_USER_ERROR); } /** * @param string $table to get list from * @param string $order sort order of list * @param string $field field data to return * @return array */ function all($table, $order='id', $field='name') { $cache_name = $this->tmp_folder.DS.$table.'_'.$order.'_'.$field; $cache_data = cache($cache_name, null, $this->cache_expires, 'tmp'); if (empty($cache_data)) { $db =& ConnectionManager::getDataSource($this->database); $table = Inflector::tableize($table); $qs = 'SELECT * FROM `'.$table.'` ORDER BY `'.$order.'`;'; $results = $db->fetchAll($qs, false, 'States'); $list = array(); foreach($results as $r) $list[$r[$table]['id']] = $r[$table][$field]; $cache_data = cache($cache_name, serialize($list), $this->cache_expires, 'tmp'); } return @unserialize($cache_data); } /** * @param string $table to get row from * @param string $field name to return (ex: name, abbrev, etc) * @param int $id primary id or item in row * @return string $value */ function row($table, $id, $field='name') { $all = $this->all($table, $field); return array_key_exists($id, $all) ? $all[$id] : null; } } </pre></div> <div style='display:none'><?php /** * CakePHP Helper to handle use of data in database that never changes * * array plural($order, $field='name'); // ready for use in FormHelper::select * string singular($id, $field='name'); * * usage examples (in Views): * * $static->states('abbrev'); // all states sorted by abbrev * $static->statuses('id', 'name'); // get list of all status names, sorted by id * $static->status(40, 'desc'); // description of status with id 40 * $status->countries(); // get all countries sorted by id * * @author Rob Conner aka rtconner */ class StaticHelper extends Helper { // set this to a folder within your app/tmp folder to store the data (no trailing slash) var $tmp_folder = 'cache'; var $cache_expires = '+1 Year'; var $database = 'default'; //plural only please, will enable both plural and singular methods (status needs to be added to inflections.php) var $methods = array('states', 'countries', 'statuses'); function __call($method, $args) { $plural = Inflector::pluralize($method); if(in_array($plural, $this->methods)) { array_unshift($args, $method); if($plural==$method) // return list of all static items return call_user_func_array(array(&$this, 'all'), $args); else // return single item return call_user_func_array(array(&$this, 'row'), $args); } else // try to imitate php's errors trigger_error(sprintf('Call to undefined function: %s::%s().', get_class($this), $method), E_USER_ERROR); } /** * @param string $table to get list from * @param string $order sort order of list * @param string $field field data to return * @return array */ function all($table, $order='id', $field='name') { $cache_name = $this->tmp_folder.DS.$table.'_'.$order.'_'.$field; $cache_data = cache($cache_name, null, $this->cache_expires, 'tmp'); if (empty($cache_data)) { $db =& ConnectionManager::getDataSource($this->database); $table = Inflector::tableize($table); $qs = 'SELECT * FROM `'.$table.'` ORDER BY `'.$order.'`;'; $results = $db->fetchAll($qs, false, 'States'); $list = array(); foreach($results as $r) $list[$r[$table]['id']] = $r[$table][$field]; $cache_data = cache($cache_name, serialize($list), $this->cache_expires, 'tmp'); } return @unserialize($cache_data); } /** * @param string $table to get row from * @param string $field name to return (ex: name, abbrev, etc) * @param int $id primary id or item in row * @return string $value */ function row($table, $id, $field='name') { $all = $this->all($table, $field); return array_key_exists($id, $all) ? $all[$id] : null; } } </div>

CakePHP Tips and Tricks

by Robert Conner

Categories:

Static Data Helper