Database question.

[HuggyBear]

For work I am trying to write a web interface to our database. They guys will need to run reports with it. My question it this:

If I have 3 text fields, on an html form, and 2 of them are filled out but one is blank, how does mysql interpret the blank field when using a prewritten query with the perl DBI. Do i need to convert it from a blank to a "*" so that it knows that I am looking for anything in that field ?

example:
if the following is the query i want to use and field3 is blank, what do I have to do to have MySQL read that as looking for anything, in datafld3, instead of looking for a blank field, in datafld3?

select * from table where datafld1=field1 datafld2=field2 datafld3=field3

[cloverm]

[quote author=HuggyBear link=board=4;threadid=4390;start=#43678 date=1027974406]
For work I am trying to write a web interface to our database. They guys will need to run reports with it. My question it this:

If I have 3 text fields, on an html form, and 2 of them are filled out but one is blank, how does mysql interpret the blank field when using a prewritten query with the perl DBI. Do i need to convert it from a blank to a "*" so that it knows that I am looking for anything in that field ?

example:
if the following is the query i want to use and field3 is blank, what do I have to do to have MySQL read that as looking for anything, in datafld3, instead of looking for a blank field, in datafld3?

select * from table where datafld1=field1 datafld2=field2 datafld3=field3


[/quote]

I would dynamically create the SQL statement. Consider the followin code snippet:

my $statement = "select * from table where datafld1=$field1 AND datafld2=$field2";
$statement .= $field3 ? " AND datafld2=$field3" : "";
my $sth=$dbh->prepare($statement);
$sth->execute();

[HuggyBear]

Yes but what if field1 and field3 are blank. This is very very possible. In fact I could have 10 or more fields and 9 or more of them could be blank.

Thanks for the help.

Huggy

[redhead]

Then make it:

if($field1 || $field2 || $field3)
{
  my $statement = "select * from table where ";
  if($field1)
   { 
     $statement .="datafld1='$field1'";
      if($field2 || $field3)
        $statement .= " AND ";
  }
  if($field2)
  {
     $statement .= "datafld2='$field2'";
      if($field3)
        $statement .= " AND ";
  }
  $statement .= $field3 ? " datafld2='$field3'" : "";
  my $sth=$dbh->prepare($statement);
  $sth->execute();
}

Takes care of any combination you can think of.

[HuggyBear]

So there is no easier way then to do it this way? I didnt want to type all that out if I didn't have to. I thought maybe the variable could use something like a * character to denote 0 or more then whatever instead.

Thanks Guys!
Jamin

[redhead]

Well, then create it like this:

my $statement = "select * from table";
f($field1 || $field2 || $field3)
{
  $statement .= " where ";
  if($field1)
  {
    $statement .="datafld1='$field1'";
      if($field2 || $field3)
        $statement .= " AND ";
  }
  if($field2)
  {
    $statement .= "datafld2='$field2'";
      if($field3)
        $statement .= " AND ";
  }
  $statement .= $field3 ? " datafld3='$field3'" : "";
}
my $sth=$dbh->prepare($statement);
$sth->execute();

If none of the fields are selected (holding any info) everything will be fetched from the table.
If just one of the fields are holding any info, this will be the selecting limit of whats beeing fetched from the table.
If theres more than one field holding info, they as well will be narroing the select statement.

And for the amount of typing, I've just wrote this hole thing twice.. I dont think it's that much to type, but if you have like a million arguments, then a for loop might be good.. just go with the $args argument, it will hold everything.

--edit--
Wouldn't this be more appropriate in Programming, since at this stage I would say it's more concerning perl/SQL queries than server related.

[HuggyBear]

I was going to use several more fields then this. I am not a typist so it takes me a little longer.

Thanks for all the help. I appreciate it!
Jamin

edit

I forgot there was a programming section. I havent needed to ask for help in a while.

[Feztaa]

So it's possible that any of the three fields can be blank? I would do this:

my @fields;
push @fields, "field1=$field1" if ($field1);
push @fields, "field2=$field2" if ($field2);
push @fields, "field3=$field3" if ($field3);

my $statement = "select * from table";
if (@fields)
{
  $statement .= " where ";
  $" = " AND ";
  $statement .= "@fields";
  $" = " ";
}

The $" variable determines what string should be placed between concatenated array elements when an array appears in quotes. In this case, the fields are only added to the array if they were given, and then they are automatically appended to the query string, separated by " AND ".

HTH ;D