Re: Q: SQL constructs for 'match all' v 'match any'

[Ron Savage]

On Sat, 14 Jun 2003 15:22:31 +1000, Ron Savage wrote:

See below for my first attempt.

>Hi Folks
>
>Let's say I have search form with fields like these, for
>Organizational Entities:
>o Name
>o Code
>o Cost center
>o Fund
>
>My customer wants a search option with 2 choices:
>o Match all
>o Match any
>
>meaning that if the Entity's name and fund are entered, then 'match
>all' will operate like AND on those 2 fields and 'match any' will
>operate like OR.

$alter_ego is an object of type CGI::Application.

-----><8-----
        my($key, $value, @sql);

        for ('entity_name', 'entity_code', 'entity_cost_center_code',
'entity_fund_code')
        {
                $value = $alter_ego -> param($_);

                next if (! $value); # Warning: This assumes '' and 0
cannot be
valid.

                $key        = $_;
                $value        = $$self{'_dbh'} -> quote($value);

                if ($_ eq 'entity_name')
                {
                        $key        = 'entity_key';
                        $value        = lc $value;
                }

                if ($option eq 'Match any')
                {
                        push @sql, "select entity_id from entity
where $key like
'%$value%'";
                }
                else
                {
                        push @sql, "$key like '%$value%'";
                }
        }

        if ($option eq 'Match any')
        {
                $sql = join(' union ', @sql);
        }
        else
        {
                $sql = 'select entity_id from entity where ' . join('
and ', @sql);
        }
-----><8-----

Hence we collect the entity_id's and then the corresponding entities.

--
Cheers
Ron Savage, [EMAIL PROTECTED] on 14/06/2003
http://savage.net.au/index.html