Results 1 to 8 of 8

Thread: Database question.

Hybrid View

  1. #1

    Database question.

    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


  2. #2
    Mentor
    Join Date
    Jun 2001
    Posts
    1,672

    Re:Database question.

    [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:
    Code:
    my $statement = "select * from table where datafld1=$field1 AND datafld2=$field2";
    $statement .= $field3 ? " AND datafld2=$field3" : "";
    my $sth=$dbh->prepare($statement);
    $sth->execute();

  3. #3

    Re:Database question.

    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

  4. #4
    Moderator
    Advisor
    redhead's Avatar
    Join Date
    Jun 2001
    Location
    Copenhagen, Denmark
    Posts
    811

    Re:Database question.

    Then make it:
    Code:
    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.

  5. #5

    Re:Database question.

    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

  6. #6
    Moderator
    Advisor
    redhead's Avatar
    Join Date
    Jun 2001
    Location
    Copenhagen, Denmark
    Posts
    811

    Re:Database question.

    Well, then create it like this:
    Code:
    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.

  7. #7

    Re:Database question.

    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.

  8. #8

    Re:Database question.

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

    Code:
    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

Similar Threads

  1. [Question] How to connect remote database server
    By ubungu in forum Linux - Hardware, Networking & Security
    Replies: 1
    Last Post: 06-27-2012, 10:04 AM
  2. Preferred database
    By jro in forum Linux - Software, Applications & Programming
    Replies: 14
    Last Post: 03-21-2006, 07:03 AM
  3. Help about Database!
    By hellagurl in forum Windows - General Topics
    Replies: 1
    Last Post: 05-24-2005, 11:16 AM
  4. Database client
    By doggiebone in forum Redhat / Fedora
    Replies: 20
    Last Post: 04-28-2003, 12:30 AM
  5. SQL database related question
    By Schotty in forum Linux - Software, Applications & Programming
    Replies: 1
    Last Post: 08-10-2002, 07:13 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •