Home » Database » MDB2 » Bug #8883

[2006-10-07 05:37 UTC] redbeard at mdjohnson dot us

Description:
------------
I'm using prepared queries almost exclusively in an application being developed for my employer. In doing so, I have recently come across the need to build IN() clauses into a query.

Basically, I need to be able to do this:

SELECT a, b, c FROM t1
WHERE a = ? AND b IN ( ... );

where the '...' comes from an array of elements. For instance, deleting a set of user selected users from a group is a specific use.

My need is not immediate, but I'd like to have it relatively soon. As such, I can either add support for this in my query wrapper (basically a way to store a library of queries) or write a patch for MDB2. Of course, I don't want to do either if MDB2 can already do this.

My thought for adding support would be to add a new parameter type specifically for use with IN() clauses. This is how I'll implement it my library if it is not a reasonable request for MDB2, which I would not be suprised if it is not.

Of course, something like this could be easily simulated with executeMultiple().

Please let me know what you, the maintainers, think of this idea. I will gladly write a patch if it is something that MDB2 can use. If it is not, I'll put the work into my own library.

Thank you,
Michael

Test script:
---------------
$params = array(1, array(1,2,3));
$q = 'SELECT a, b, c FROM t1 WHERE a = ? AND b IN (?)';
$h =& $db->prepare($q, array('integer', 'array:integer'));
$res = $h->execute($params);
$data = $res->fetchAll();

// data: a b c
// 1 1 foo
// 1 2 bar
// 1 3 baz

Expected result:
----------------
$data should be equivalent to:

array(array(1, 1, 'foo'),
array(1, 2, 'bar'),
array(1, 3, 'baz'))

[2006-10-09 02:04 UTC] redbeard at mdjohnson dot us

Completely understandable. I didn't know about implodeArray(), and I will certainly use it as the need arises.

Thanks for the prompt reply. I have to say I'm very impressed with everything you've done with MDB2.

Thanks again,
Michael