Case #1: Using an associative array format is supported by the
Zend_Db_Table_Abstract::fetchAll() and fetchRow(). But this is the only
place this format is supported for a WHERE clause in Zend_Db. This
format for a WHERE clause is not supported in other Zend_Db methods.
$assocArray = array(
'expr1 = ?' => 'value1',
'expr2 = ?' => 'value2'
);
$table->fetchAll($assocArray);
This results in a query like the following:
SELECT * FROM "tablename" WHERE (expr1 = 'value1') AND (expr2 =
'value2')
Case #2: the same query can be generated using a plain integer-indexed
array for the WHERE clause:
$plainArray = array(
$db->quoteInto('expr1 = ?', 'value1'),
$db->quoteInto('expr2 = ?', 'value2')
);
$table->fetchAll($plainArray);
Case #3: a string can be used too. You can use PHP's implode function
to combine an array into a string:
$string = implode(' AND ', array(
$db->quoteInto('expr1 = ?', 'value1'),
$db->quoteInto('expr2 = ?', 'value2')
));
$table->fetchAll($string);
The implode() is basically what Zend_Db is doing, but it also adds
parentheses when you provide the WHERE clause as an array, just in case
your expressions contain OR operators. If the parens weren't added, it
could result in unexpected behavior due to precedence of AND vs. OR.
The associative array format shown in case #1 above is not supported in
update() or delete() methods of Zend_Db_Table or Zend_Db_Adapter.
// NOT SUPPORTED
$table->delete(array('expr1 = ?' => 'value1', 'expr2 = ?' =>
'value2'));
// NOT SUPPORTED
$table->update($setArray, array('expr1 = ?' => 'value1', 'expr2 = ?'
=> 'value2'));
// NOT SUPPORTED
$db->delete('tablename', array('expr1 = ?' => 'value1', 'expr2 = ?' =>
'value2'));
// NOT SUPPORTED
$db->update('tablename', $setArray, array('expr1 = ?' => 'value1',
'expr2 = ?' => 'value2'));
Neither associative array nor plain array formats are supported by
Zend_Db_Select::where().
// NOT SUPPORTED
$select = $db->select()->from('tablename')->where(array('expr1 = ?' =>
'value1', 'expr2 = ?' => 'value2'));
// NOT SUPPORTED
$select = $db->select()->from('tablename')->where(
array(
$db->quoteInto('expr1 = ?', 'value1'),
$db->quoteInto('expr2 = ?', 'value2'),
)
);
The Zend_Db_Select::where() and whereOr() methods support only a string
for the expression, with an optional extra argument as a parameter.
Additional terms can be given by additional calls to the method.
$select =
$db->select()->from('tablename')->where('expr1')->where('expr2',
'value2');
Regards,
Bill Karwin
> -----Original Message-----
> From: Ian Warner [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, August 22, 2007 9:08 AM
> To: [email protected]
> Subject: Re: [fw-general] Zend DB Table -- Delete
>
> Strange I use this all the time with my Inserts:
>
> Also in the manual:
>
> Example 9.87. Example of finding rows by an expression array
> with multiple terms
>
> $table = new Bugs();
>
> $where = array(
> 'bug_status = ?' => 'NEW',
> 'reported_by = ?' => 'dduck'
> );
>
> $rows = $table->fetchAll($where);
> // generated SQL contains:
> // WHERE bug_status = 'NEW' AND reported_by = 'dduck'
>
> This is for a select but I have found it works for Inserts -
> does the above actually guard against SQL injecttion, or am I
> mistaken as the above example doesnt contain variables.
>
> Slightly confused now.
>
> Matthew Weier O'Phinney wrote:
> > -- Ian Warner <[EMAIL PROTECTED]> wrote (on Wednesday, 22
> > August 2007, 11:48 AM +0900):
> >> Thank you for the guidance.
> >>
> >> The manual is misleading then:
> >>
> >> "The second argument can be an array of SQL expressions. The
> >> expressions are combined as Boolean terms using an AND operator.
> >>
> >> Since the table delete() method proxies to the database adapter
> >> delete() method, the second argument can be an array of SQL
> >> expressions. The expressions are combined as Boolean terms
> using an AND operator."
> >>
> >> It kinda reads that I create the array and the AND is substitued
> >> autmoaticall on iteratating the array I pass in.
> >
> > That *does* work. What *didn't* work in your original attempt was
> > placeholder substitution:
> >
> > 'user_id = ?' => $user
> >
> > (BTW, there is *no* place in Zend_Db where the above would work.)
> >
> > The following *would* work:
> >
> > $adapter = $table->getAdapter();
> > $where = array(
> > $adapter->quoteInto('user_id = ?', $user),
> > $adapter->quoteInto('video_id = ?',
> $this->_getParam('code')),
> > );
> >
> >
> >> Matthew Weier O'Phinney wrote:
> >>> -- Ian Warner <[EMAIL PROTECTED]> wrote (on Tuesday, 21
> >>> August 2007, 05:31 PM +0900):
> >>>> I have
> >>>>
> >>>> $where = array(
> >>>> 'user_id = ?' => $user,
> >>>> 'video_id = ?' => $this->_getParam('code') );
> >>>>
> >>>> $table->delete($where);
> >>> You need a well-formed WHERE clause, not an assoc array of
> >>> column/value pairs. Try this:
> >>>
> >>> $adapter = $table->getAdapter();
> >>> $where = $adapter->quoteInto('user_id = ?', $user)
> >>> . ' AND ' . $adapter->quoteInto('video_id = ?',
> >>> $this->_getParam('code'));
> >>> $table->delete($where);
> >>>
> >>>
> >>>> and I am getting the Exception:
> >>>>
> >>>> Failed: Mysqli prepare error: Unknown column
> 'M1F94C3359F8FFC35B'
> >>>> in 'where clause'
> >>>>
> >>>> $this->_getParam('code') = M1F94C3359F8FFC35B
> >>>>
> >>>> I just dont understand why it is using the value of code as a
> >>>> column name when it should be using video_id ?
> >>>>
> >>>> Cheers
> >>>>
> >>>> Ian
> >>>>
> >
>
