in reply to mysql search statement

Use join: 
my $sql ='select from table where'
   . join(' and ', (
      " username = $form_username",
      "password = $form_password",
      "name = $form_name",
   )
);

[download]

Update: listen to talexb. This really is something you should let the database handle. Alternatively, I would recommend just using your code and take out the last AND ... sure it isn't elegant, but, if and only if you have a few form variables, well, it does get the job done.

I have been toying with hash to solve this problem, but it's tricky, mainly because undef is slightly different than the empty string ... which is something you have to consider with this problem. My idea is to have the database column names as the keys, and the form variables as the values - if i can find a convienient way to remove all keys whose values are undefined or empty strings, then creating the SQL statement will be a sinch ... back in a few ...

Okay, here it is - i doubt i'll ever use, but it was fun anyways ;) 

my %hash = (
   username => $form_username,
   password => $form_password,
   name     => $form_name,
);   

# was trying to do this with map ... but time is short ;)
for (keys %hash) {
   delete $hash{$_} unless $hash{$_};
}

my $sql = 'select from table where ' .
   join(' and ', map { qq|$_ = "$hash{$_}"| } keys %hash);

[download]
Hope this helps somehow - check out Coding Errror for more info (like bind vars).

jeffa 

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
F--F--F--F--F--F--F--F--
(the triplet paradiddle)

Replies are listed 'Best First'.
Re: (jeffa) Re: mysql search statement
by Parham (Friar) on Dec 31, 2001 at 02:02 UTC
    the problem is not creating the statement. It's making mysql not accept '' as an actual value. I want blanks to be ignored.
[reply]
      I'd suggest modifying that column to be NOT NULL .. that way the database will refuse to accept '' as a valid value.

      --t. alex

      "Excellent. Release the hounds." -- Monty Burns.

[reply]
Re: (2): mysql search statement
by dmmiller2k (Chaplain) on Jan 01, 2002 at 02:00 UTC

    Good idea, jeffa. It's very similar to a construct I often find myself using when i have to construct a lot of dynamic queries.

    It all started when I suddenly noticed that every SQL statement follows approximately the same general pattern, which may be briefly summarized by the following BNF-like pseudocode:

    keyword value [{separator} value ...]

    So, for example, every keyword in a typical SELECT statement has a associated list, which lends itself very nicely to the following hash-based perl structure:

    my $sql = { SELECT => [ 'column1', 'column2', 'column3' ],
            FROM   => [ 'table1', 'table2' ],
            WHERE  => [ 'condition1', 'condition2' ],
            GROUP_BY => [ 'column1', 'column2' ],
            HAVING => [ 'condition3]' ],
            ORDER_BY => [ 'column1','column2' ],
            # ... you get the idea };

# Store the separators in another hash:
my $sep = { SELECT => ',',
            FROM   => ',',
            WHERE  => ' AND ',
            HAVING => ' AND ',
            GROUP_BY => ',',
            ORDER_BY => ',' };

    [download]

    The code to produce a SQL string is almost trivial:

    my $query = join ' ', map { join $sep->{$_}, @{$sql->{$_}} } qw(SELECT
+ FROM WHERE GROUP_BY HAVING);

    [download]

    I suppose one could take this a step further and dynamically generate the where clauses, but for my uses this is beyond the point of diminishing returns (it's more work than it's worth), so for me the where clauses tend to look like 'table2.column4 = "string"' or whatever (in other words the column names must be redundantly specified).

    One advantage of this approach it that you can manipulate the set of SELECT columns as a list (array) until the very end when the query is generated. Very often many of the same columns appear in the GROUP BY clause as in the SELECT list. This works incredibly nicely when there is one basic query, which is then modified manifold, based upon various external attributes, and one must make sure the SELECT list remains in sync with the WHERE, GROUP BY, and HAVING clauses, etc.

    Of course, in the degenerate case (no join), there is only one table (i.e., the FROM array has only one element) and no GROUP BY or HAVING clauses.

    This is just the basic idea; for production quality code, more checking would have to be added along with blank elimination, etc. (Left as the proverbial exercise for the reader... :)

    dmm

    You can give a man a fish and feed him for a day ...
    Or, you can     teach him to fish and feed him for a lifetime
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom