mysql search statement

[Parham]

I ran into an interesting problem today. While testing my database program (mysql driven), i began to realize that when selecting from fields, that '' is considered acceptable input. SELECT FROM table WHERE username=$form_username and password=$form_password and name=''; will yield nothing because name always has a value in it. i know i can do this:

$form_username = 'bleah';
$form_name = 'joejoe';

$SQL = "SELECT FROM table WHERE"; 
if ($form_username) { $SQL .= "username = $form_username AND "; }
if ($form_password) { $SQL .= "password = $form_password AND "; }
if ($form_name) { $SQL .= "name = $form_name AND "; }
$SQL =~ s/\sAND\s$//; #exists if not everything is filled in

print $SQL;

but it's totally impractical, not to mention ugly. any good solution out there?

[jeffa]

Use join:

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

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);

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)

[Parham]

the problem is not creating the statement. It's making mysql not accept '' as an actual value. I want blanks to be ignored.

[talexb]

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.

[dmmiller2k]

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 => ',' };

The code to produce a SQL string is almost trivial:

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

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

[khkramer]

I usually do this like so:

"SELECT FROM table WHERE ".
  ( $form_username ? "username = $form_username " : '' ).
    ( $form_username && $form_password ? 'AND ' : '' ).
  ( $form_password ? "password = $form_password " : '' ).
    ( ($form_username || $form_password) && $form_name ? 'AND ' : '' )
+.
  ( $form_name ? "name = $form_name" : '' );

It's not really any shorter than your if-statement version, but my brain finds it easier to read. If you have a number of statements like this, you can write a little sub to generate them. The sub could use a loop, or it could use a variation of the concatenation statement above, for which the general form is:

    a (or not)
    AND if a and b
    b (or not)
    AND if (a or b) and c
    c (or not)
    AND if (a or b or c) and d
    ...
    z (or not)

Here's a sub using a loop:

$sql_string = make_anded_statement ( username => $form_username,
                                     password => $form_password,
                                     name     => $form_name );

sub make_anded_statement {
  my ( %args ) = @_;
  my @keys = keys %args;
  my $where_string = '';
  my $ANDING = 0;
  foreach my $i ( 0 .. $#keys ) {
    if ( $args{$keys[$i]} ) {
      $where_string .= $keys[$i] . '=' . $args{$keys[$i]} . ' ';
      $ANDING = 1;
    }
    if ( $ANDING && $args{$keys[$i+1]} ) {
      $where_string .= 'AND ';
    }
  }
  return "SELECT FROM table WHERE $where_string";
}

[Parham]

i thought i should post an answer even though khkramer had an awesome solution. I got this and thought i should share it :).

#!/usr/bin/perl -w 
my $username = 'joejoe'; 
my $password = 'lightning'; 
my $name = 'Joe';
my $SQL = "SELECT FROM table WHERE" .
          (($username)?" username = '$username'":'') .
          (($password)?" and password = '$password'":'') .
          (($name)?" and name = '$name'":'') . ';';
print $SQL;

Happy New Years all :)

[dmmiller2k]

So, if $username is not populated (or otherwise false), but password is, you'll get:

$SQL = "SELECT FROM table WHERE  AND password='pwd'

Just join your WHERE clauses together using the string, ' AND ' as the separator.

my @WHERE = ();
push @WHERE, "username = '$username'" if $username;
push @WHERE, "password = '$password'" if $password;
push @WHERE, "name = '$name'" if $name;

$SQL = "SELECT FROM table WHERE " . join ' AND ', @WHERE;

Or see my post at 'Re: (2): mysql search statement'.

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

[Spenser]

Are you sure that you're enclosing your SELECT statement in the right quote and double-quote combination?  I use Perl with mySQL a lot--that's almost all that I do, actually--and I can make mySQL look for a blank field without changing the column or table settings.  Below is an excerpt from one of my scripts.  I modified an existing working script to look for a blank (that is to say '') within one of my tables just to see if it would work, and it did.

$dbh_ship = DBI->connect("DBI:mysql:db_name:localhost",
                       "user", "passwd", \%attr ) 
    || die "Could not connect to database: " . DBI->errstr;
$sql_str_ship = "SELECT * FROM shipping WHERE street1='' ORDER BY ship
+_id";
$sth_ship = $dbh_ship->prepare($sql_str_ship);
$sth_ship->execute();

while (@shipping = $sth_ship->fetchrow_array()) {
   # and so forth....

In this example I'm looking for records in my shipping table that are missing the street address.  The column parameters read YES in the null column and NULL in the default column.  Again, I think you must be missing something very minor.

That's Spenser, with an "s" like the detective.

[jlongino]

Why can't you just do the following?

if ($form_username && $form_password && $form_name) {
  # create $SQL here
  # other stuff here
  print "Parms OK.\n";
} else {
  print "Missing one or more required value(s) for username, password 
+or name.\n";
}

I would imagine that you would also want to use place holders and qq() where appropriate as well.

--Jim