Thomas,
I encourage you to test the where() method with v1.0.2 and see the
results -
it generates SQL imploding all the array values into a single
placeholder
as
expected. The documentation used to have this usage listed but now it
seems
to have been removed. The code snipped you gave generated the following
SQL
with 1.0.2:
price IN (2, 10, 14, 20, 2, 10, 14, 20, 2, 10, 14, 20, 2, 10, 14, 20)
and the following SQL with the current SVN:
price IN (2, 10, 14, 20)
indicating they are generating different SQL. Enhancements are welcome
but
I
thought they would be backwards compatible - I had to revert to 1.0.2 :(
The old code used to be generic and would apply to both - a normal where
clause with a boolean operator or an IN operator because it used to
implode
the array into a list of values to replace a single placeholder but the
new
enhancements need IN-operators handled differently using arrays and
multiple
placeholders. I guess having a separate in() function for such
expressions
is more ideal and desired at the component level.
Here is the issue to get started:
http://framework.zend.com/issues/browse/ZF-2223
Keep up the good work!
On 11/21/07, Thomas Weidner <[EMAIL PROTECTED]> wrote:
Shekar,
actually the documentation does not mention this usage.
And there are also no testcases.
All testcases have approved before I did my commit.
Actually, giving an array as input would result in each single
placeholder
to be replaced.
where("price > ? and price <= ?", array(2, 10));
becomes
"where (price > 2 and price <= 10)"
This is the reason why the automatic imploding does not work anymore.
Principially there are several ways:
You could for example do the following:
$array = array(2,10,14,20);
$addwhere = "";
for($i = 1; $i < count($array); ++$i) {
$addwhere .= ", ?";
}
$where = "price IN (?". $addwhere.")";
$db->where($where, $array);
In my eyes it would be better to have a own "IN" function avaiable for
such
clauses.
I am actually also working on a BEWTEEN function which adds between to
be
avaiable.
So eighter you use a the codesnippet I gave you or you add a new issue
for
creating a "IN" function which handles the IN within the where clause.
Greetings
Thomas
I18N Team Leader
----- Original Message -----
From: "Shekar C Reddy" <[EMAIL PROTECTED]>
To: "Thomas Weidner" <[EMAIL PROTECTED]>
Cc: "Zend Framework General" <fw-general@lists.zend.com>
Sent: Wednesday, November 21, 2007 8:28 AM
Subject: Re: [fw-general] Zend_Db_Select::where() malfunction
> Thomas,
>
> This feature of imploding an array of values into a single
place-holder
> was
> there from the the days of ZF v0.1.5 or earlier. My code was working
fine
> all these days. There were code examples in the documentation that
showed
> how an array would be imploded into the SQL using where(). I even
created
> an
> issue in the past to avoid quoting numeric values in the resulting
> SQL's
> array (look for the issue that reads: "quote() quotes numeric values"
a
> bug
> that was attributed to PDO). I just did a diff on Select.php between
> the
> current SVN and 1.0.2 and noticed that where() method actually
> invokes
and
> delegates to _where() in the current SVN whereas there is no _where()
> method
> in 1.0.2. My same code with ZF 1.0.2 works fine as expected but not
> with
> the
> latest SVN update.
>
> If this is not supported, what's the syntax for the expected SQL? I
> need
> to
> generate a where clause as under:
>
> WHERE status IN ( 'A', 'I' )
>
> Thanks,
>
>
>
>
> On 11/20/07, Thomas Weidner <[EMAIL PROTECTED]> wrote:
>>
>> Hy Shekar,
>>
>> this is not supported, and it was not supported in the past.
>> Because ? is a placeholder for only ONE variable.
>> And you gave two within your array :-)
>>
>> Actually I am working on a array integration which adds several new
>> features
>> to where.
>> But the behaviour you are expecting would break things with other
>> new
>> where
>> features.
>>
>> For now it is not planned to support such a use case.
>>
>> Btw: Before my improvement several days ago even the first variable
would
>> not have been integrated.
>> That the array value is inserted for a placeholder is also one of
>> the
new
>> features :-)
>>
>> Greetings
>> Thomas
>> I18N Team Leader
>>
>> ----- Original Message -----
>> From: "Shekar C Reddy" <[EMAIL PROTECTED]>
>> To: "Zend Framework General" <fw-general@lists.zend.com>
>> Sent: Tuesday, November 20, 2007 10:55 AM
>> Subject: [fw-general] Zend_Db_Select::where() malfunction
>>
>>
>> > Bill,
>> >
>> > I downloaded the latest SVN today and noticed a strange behavior
>> > with
>> the
>> > select component:
>> >
>> > $select->where( 'status IN ( ? )', array( 'A', 'I' ));
>> >
>> >
>> > SQL generated:
>> >
>> > WHERE status IN ( 'A' )
>> >
>> >
>> > SQL expected:
>> >
>> > WHERE status IN ( 'A', 'I' )
>> >
>>
>>
>
