SELECT * FROM items WHERE item_id IN(1,2,3)
$c = $this->modx->newQuery( 'itemdbItems' ); $c->where( array( 'item_id:IN' => '1,2,3' ) ); $items = $this->modx->getCollection( 'itemdbItems', $c );
[2008-08-28 18:01:37] (ERROR @ /revolution/index.php) Error 42000 executing statement:
Array
(
[0] => 42000
[1] => 1064
[2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''1,2,3'' at line 1
)
$items= $this->modx->getCollection('itemdbItems', array ('item_id:IN' => '(1,2,3)'));
$c = $this->modx->newQuery( 'itemdbItems' );
$c->where(array('item_id:IN' => '(1,2,3)' ));
$items = $this->modx->getCollection( 'itemdbItems', $c );
[2008-08-28 19:00:07] (ERROR @ /revolution/index.php) Error 42000 executing statement:
Array
(
[0] => 42000
[1] => 1064
[2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''(1,2,3)'' at line 1
)
MODX Staff
<?php
$c = $this->modx->newQuery( 'itemdbItems' );
$c->where('item_id IN (1,2,3)');
$items = $this->modx->getCollection( 'itemdbItems', $c );
?>
Ahh, that makes sense! Thanks a lot, that worked perfectly.
This is because I’m trying to use prepared statement bindings when you use the array(’field:OPERATOR’ => ’value’) condition syntax, and unfortunately, there is no valid PDO_PARAM_TYPE for this kind of value; PDO is actually seeing that the value is a PHP string and forcing the quoting to occur. For now you’ll have to write:
<?php $c = $this->modx->newQuery( 'itemdbItems' ); $c->where('item_id IN (1,2,3)'); $items = $this->modx->getCollection( 'itemdbItems', $c ); ?>
MODX Staff
Yeah, I was thinking this too doze, but so much has changed. Might work if you are not running native PDO (i.e. in PHP 4, or 5.0.x), or running the latest versions of PDO perhaps got more strict about bindings, or maybe prior to xPDOQuery being introduced it did work and I broke the capability some how.
Has the :in operator worked in some revision of xPDO? I remembered that I have used it at somepoint succesfully.. but I use very old xPDO revision though. Or I remember wrong.
