Posted on 06/02/2009. By Pete Otaqui.
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.