A nice feature from Zend Framework (ZF) is its robust database adapter. Although there is still some work needed to extend the functionality (ORM?) and some strange bugs I encounter when working with multiple tables, Zend_Db package is already good solution for my CRUD application.
In Zend_Db_Adapter_Abstract, there is a function called quoteInto which is used to quote a field value from an sql statement. Most implementation is in where clause like
-
$where = $db->quoteInto(‘name = ?’,‘John Doe’);
In some occassions, I found the where clause should check multiple fields hence quoteInto should be altered to accept multiple question marks. My proposed change to the function is as follows:
-
function quoteInto()
-
{
-
if($numArg < 2) {
-
}
-
eval("$return = sprintf(‘".str_replace(‘?’,‘%s’,$argList[0])."’,".substr(strstr($this->quote($argList),‘,’),1).");");
-
-
return $return;
-
}
As result, one should now be able to use the function like example below:
-
$where = $db->quoteInto(‘nickname= ? or fullname = ?’,‘John’,‘John’);
Bill Karwin from Zend told me a method to invoke the multiple quote by using an array to be passed into quoteInto’s second parameter. My eval code is likely to discourage him to accept arbitrary parameters in quoteInto. His code (adjusted) is below:
-
$where[] = $db->quoteInto(‘nickname = ?’, ‘John’);
-
$where[] = $db->quoteInto(‘fullname = ?’, ‘John’);
-
$db->update(‘mytable’, $data, $where);
I thought this is a good tweaked solution. However, now I face another problem with logic used to attach all where clauses in a query. They all use logic “AND” while sometimes I will need mixture between “AND” and “OR”, and some math operation.
As Bill explained about function internal and how it worked, one should specify logic “OR” in the where clause prior to joining with other clause using “AND”, something like
-
$where[] = ‘this OR that’;
-
$where[] = ‘one OR the other’; //result: ‘(this OR that) AND (one OR the other)’
This way, there wouldn’t be a single line for where clause like I expect before but it logically and syntatically answered my initial question.
Anway, to honour myself and appreciate my creativity, I use my modified function with a switch to default behaviour.
Many parts of the Zend Framework seem rather raw and in need of more work and this is certainly one of them. I’d like to see the quoteInto function accept and array as it’s second parameter and replace each ? from left to right with the values of the array. So quoteInto should accept either and array or a string as the second param. Passing in a string would result in the function’s current behavior. Does that make sense to anyone else?
@Lee: I use this solution:
public function quoteInto($text, $value)
{
if ( ! is_array($value) )
$value = array($value);
$qmpos = 0; // question mark position
foreach ( $value as $relacement )
{
// get the most left question mark position
$qmpos = strpos($text, ‘?’, $qmpos);
// no question mark left, jump out of the cycle
if ( $qmpos === false )
break;
// replace the question mark with $replacement
$text = substr_replace($text,
$this->quote($relacement), $qmpos, 1);
}
return $text;
}