Custom Model and Field Result Set in CakePHP

If you’re using CakePHP and want to perform a custom SQL query while forcing your results into an arbitrary Model-based array when they are given back to you, you could do a lot worse than using grigri’s (who is a fellow South Westerner and therefore obviously a good bloke ;) DboMysqlEx (Mysql Extended) Class, or at least this particular part of it:

<?php
require_once (LIBS . 'model' . DS . 'datasources' . DS . 'dbo' . DS . 'dbo_mysql.php');
class DboMysqlEx extends DboMysql {
   var $description = "MySQL DBO Driver - Extended";
   // Override resultSet to allow for Model__field type aliases
   function resultSet(&$results) {
		if (isset($this->results) && is_resource($this->results) && $this->results != $results) {
			mysql_free_result($this->results);
		}
       $this->results =& $results;
       $this->map = array();
       $num_fields = mysql_num_fields($results);
       $index = 0;
       $j = 0;
       while ($j < $num_fields) {
           $column = mysql_fetch_field($results,$j);
           if (!empty($column->table)) {
               $this->map[$index++] = array($column->table, $column->name);
           } else {
               if (strpos($column->name, '__')) {
                   $parts = explode('__', $column->name);
                   $this->map[$index++] = array($parts[0], $parts[1]);
               } else {
                   $this->map[$index++] = array(0, $column->name);
               }
           }
           $j++;
       }
   }
}
?>

This code, which overrides the default MySQL DBO, allows you to SELECT something AS Modelname__Fieldname (with a double underscore) and will end up populating your result set as you would like it to ($results['Modelname']['Fieldname']).

In order to use a custom datasource, copy the code and save it as “/cake_dir/app/models/datasources/dbo/dbo_mysqlex.php” and edit your “/cake_dir/app/config/database.php” file so that it uses the “mysqlex” driver instead of plain old “mysql”.

This all came from a discussion on the excellent CakePHP Google Group

Grigri posted a copy of his modified MySQL Extended DBO online if you want some more of his nice features like backtracing.