Re: Removing auto-generated methods (set_sql)

[prev] [thread] [next] [Date index for 2004/06/14]

On Mon, Jun 14, 2004 at 12:57:38PM -0400, William McKee wrote:
> Is there a way that I could generate the SQL once and set empty fields
> to match anything? To my knowledge, that would only be possible using
> LIKE and '%'. Any suggestions would be most appreciated.

If you have a small number of fields you could achieve what
you say (if I understand correctly) by creating a general piece
of SQL like this :

  WHERE (? OR field1 = ?)
    AND (? OR field2 = ?)
    AND (? OR field3 = ?)

Then :

given    : (field1 => $x, field3 => $y) 
do       : $sth->execute(0, $x, 1, '', 0, $y);

given    : (field2 => $z);
do       : $sth->execute(1, '', 0, $z, 1, '');

i.e. if a value is provided you use the placeholder pair (0, $value) so
the left hand side of the SQL OR is false and the right hand side
(your value) becomes the criteria.  If a pair is not provided you use the
placeholder pair (1, '') so the left hand side of the OR is true and the
right hand side is redundant - (hopefully these are optimized away).

It gets horrible fast (or perhaps it is just plain horrible)
but with 3 or 4 fields it may be manageable and allows you to
do everything with one prepared statement.

Just a thought.

Colm