The missing fluent MySQL wrapper
I just got a mail from John Fraboni, asking about that MySQL wrapper I’m using in my Zend AMFPHP tutorial.
The main thing here is that we can accomplish things like this since we can return $this, which will return the object itself. That’s why we can do for instance $this->getT()->findOne($id->id, false)->asObj($this) where we are first getting an instance of the class described below through getT next we find one record in a table (with findOne) and return it as an object (with asObj), no intermediary assignments are necessary.
I find this great, it saves a lot of space and makes the code more concise.
Alright alright, let’s go through it then, from top to bottom:
function __construct(){
$db_link = mysql_connect($GLOBALS['db_host'], $GLOBALS['db_user'], $GLOBALS['db_pass']);
mysql_select_db($GLOBALS['db_name'], $db_link);
}
static function conn(){
return new Mysql();
}
static function tbl($table){
$conn = new Mysql();
$conn->table = $table;
return $conn;
}First of all, this thing is supposed to be used with some kind of bootstrap file which initiates the globals used in the construct to create the database connection.
Conn() is just a static factory and tbl() will simply set the table we want to work with henceforth.
function get($get){
$this->get = empty($this->get) ? $get : $this->get;
return $this;
}
function insertRaw($table, $fields, $values){
$result = $this->exec("INSERT INTO `$table` ($fields) VALUES ($values)");
return $result ? mysql_insert_id() : false;
}
function findBy($field, $value, $get = '*'){
$this->get($get);
return $this->findAll("WHERE $field = '$value'");
}So $this->get are the columns we want to retrieve from the table, usually *.
function findCol($get, $field = '', $value = ''){
$this->get($get);
$this->sql = empty($field) ? '' : "WHERE $field = '$value'";
return $this->get2dAs1d();
}
function findOne($id, $ret = true){
$this->result = current($this->findBy('id', $id));
return $ret ? $this->result : $this;
}FindCol will retrieve one column from multiple results, for instance an array of all the ids of the results. FindOne is a little bit limited by the fact that it will only work with an id column.
function findAll($where, $get = ''){
$this->sql = $where;
$this->get($get);
return $this->get2dAssoc();
}
function _getSql($sql = ''){
return empty($sql) ? "SELECT {$this->get} FROM {$this->table} {$this->sql}" : $sql;
}Not much to say here findAll() will of course retrieve the whole set that matches, as an associative array.
function get2dAssoc($sql = ''){
$result = $this->exec($this->_getSql($sql));
$arr = array();
if($result){
while($row = mysql_fetch_assoc($result))
$arr[] = $row;
}else
return 0;
return $arr;
}
function get2dAs1d($sql = ''){
$result = $this->exec($this->_getSql($sql));
if(!$result)
return array();
$arr = array();
while($row = mysql_fetch_row($result))
$arr[] = current($row);
return $arr;
}
function exec($sql = ''){
$sql = empty($sql) ? $this->sql : $sql;
return mysql_query($sql);
}Not much to say here either, I think the method names explain everything.
function asObj($obj = null){
return Arr::fluent($this->result)->asObj($obj);
}So this one is using our fluent array, let’s go there:
function asObj($obj = null){
$obj = empty($obj) ? new stdClass : $obj;
foreach($this->c as $key => $value)
$obj->$key = $value;
return $obj;
}There’s a lot of nifty stuff here (as PHP goes anyway), first we instantiate an empty object (new stdClass) if we’re not passed an object already. Then we loop through our internal container ($this->c, needs to be an associative array). Every key is used to work with a member variable ($obj->$key will resolve to for instance $obj->login if the contents of the $key variable is ‘login’), if it exists already it will be overwritten, if not it will be created and assigned $value.
Download both Smoc and Fluent in one package.
Related Posts
Tags: fluent interfaces, mysql, PHP