Skip navigation
aspenjian
Currently Being Moderated

Ignore Empty Parameter

Jun 20, 2009 2:56 PM

I am trying to create a form that searches through a recordset. I am wondering, if several of the fields within the search form are left blank when submitted by the "GET" method, how I can have the query string ignore those empty parameters in the URL. I posted a similar question a little earlier and got no responses. I'm a newbie so let me now if I'm not phrasing my question correctly or something.

 
Replies
  • Currently Being Moderated
    Jun 21, 2009 6:20 AM   in reply to aspenjian

    I searched for your earlier question, and see that you posted it in the ADDT forum. Since the question appears to have nothing to do with ADDT, it's not surprising that it didn't get an answer.

     

    The question you have posted here is rather vague. You don't say which server model you're using (although I see from your earlier post that it's PHP). The Useful Links box at the top right of the forum has advice on how to get help quickly. You can also find more general advice in How to Ask Questions the Smart Way. It's a long read, but contains really useful advice.

     

    Now, to your particular issue...

     

    The simple way to ignore empty parameters in search queries is to use SQL wildcards to match all values. When creating a SQL query in the Advanced recordset dialog box, the following requires both fields to have a value:

    SELECT *
    FROM myTable
    WHERE category = var1 AND price = var2
    

    In this case, var1 and var2 are the variables that will be replaced by $_GET['category'] and $_GET['price'].

     

    I you want to permit either value to be blank, you need to change the SQL like this:

    SELECT *
    FROM myTable
    WHERE category LIKE %var1% AND price LIKE %var2%
    

    % is a SQL wildcard that matches anything (including nothing). The LIKE keyword must be followed by a string. So, you need to set the data type of var1 and var2 to "text" in the recordset dialog box, even if the value you're matching is a number.

     
    |
    Mark as:
  • Currently Being Moderated
    Calculating status...
    Jun 21, 2009 9:11 PM   in reply to aspenjian

    This might help u.

     

    query = "SELECT * FROM mainitems";

    $where = false;

     

    //to check if the first field(category) is posted

    if(isset($_POST['category']) && !empty($_POST['category'])) {

    $query .= " WHERE category LIKE %varCategory%";
    $where = true;
    }

     

    //to check if price input is posted

    if(isset($_POST['price']) && !empty($_POST['price'])) {

    if ($where) {
    $query .= ' AND ';
    } else {
    $query .= ' WHERE ';
    $where = true;
    }
    $query .= "price LIKE %varprice%";
    }

     

    ...continue with other input fields

     

    From here on, searching will continue without the blank input.

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 22, 2009 11:17 AM   in reply to aspenjian

    The problem is that you're trying to create a sophisticated search form, which requires a screwdriver, but you've only got a hammer.

     

    Quite honestly, the technique that I showed you is just a simple trick for handling a couple of columns. What you want to do is much more complex, and cannot be handled without getting down and dirty with hand-coding. Dreamweaver's server behaviors are not intended to solve every problem with dynamic web page development. They're designed for rapid prototyping or proof of concept. For anything more sophisticated, you need to do the coding yourself.

     

    The approach taken by QiQi86 is right, although she has used $_POST instead of $_GET. In fact, she has adapted the solution I posted for someone else in this forum some time ago.

     

    What the code does is build the SQL query by checking whether a variable exists. Let's say your recordset is called "search", this is how you need to build the query:

    $query_search = 'SELECT * FROM mainitems';
    $where = false;
    if (isset($_GET['category']) && !empty($_GET['category'])) {
      $query_search .= ' WHERE category = '. GetSQLValueString($_GET['category'], 'text');
      $where = true;
    }
    if (isset($_GET['price']) && !empty($_GET['price'])) {
      if ($where) {
        $query_search .= ' AND ';
      } else {
        $query_search .= ' WHERE ';
        $where = true;
      }
      $query_search .= 'price = ' . GetSQLValueString($_GET['price'], 'text');
    }
    if (isset($_GET['time1']) && !empty($_GET['time1'])) {
        if ($where) {
        $query_search .= ' AND ';
      } else {
        $query_search .= ' WHERE ';
        $where = true;
      }
      $query_search .= 'time1 = ' . GetSQLValueString($_GET['time1'], 'text');
    }
    if (isset($_GET['time2']) && !empty($_GET['time2'])) {
        if ($where) {
        $query_search .= ' AND ';
      } else {
        $query_search .= ' WHERE ';
        $where = true;
      }
      $query_search .= 'time2 = ' . GetSQLValueString($_GET['time2'], 'text');
    }
    if (isset($_GET['city']) && !empty($_GET['city'])) {
        if ($where) {
        $query_search .= ' AND ';
      } else {
        $query_search .= ' WHERE ';
        $where = true;
      }
      $query_search .= 'city = ' . GetSQLValueString($_GET['city'], 'text');
    }
    if (isset($_GET['zip']) && !empty($_GET['zip'])) {
        if ($where) {
        $query_search .= ' AND ';
      } else {
        $query_search .= ' WHERE ';
        $where = true;
      }
      $query_search .= 'zip = ' . GetSQLValueString($_GET['zip'], 'text');
    }
    if (isset($_GET['neighborhood']) && !empty($_GET['neighborhood'])) {
        if ($where) {
        $query_search .= ' AND ';
      } else {
        $query_search .= ' WHERE ';
      }
      $query_search .= 'neighborhood = ' . GetSQLValueString($_GET['neighborhood'], 'text');
    }
    

    All that code replaces the line that Dreamweaver would create for $query_search.

     

    It uses PHP conditional statements to check whether a URL parameter exists, and whether it contains any value. Only if both are true does it incorporate it into the SQL query.

     

    It's a lot of code, but it's very repetitive, so it's easy to copy and paste.

     

    A better way of doing it would be to create a loop to go through all the variables, but the code I have given here should work.

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 22, 2009 11:42 AM   in reply to David_Powers

    OK, I have done a bit of testing, and the following code is a lot shorter:

    $expected = array('category', 'price', 'time1', 'time2', 'city', 'state', 'zip', 'neighborhood');
    $query_search = 'SELECT * FROM mainitems';
    $where = false;
    foreach ($expected as $var) {
      if (isset($_GET[$var]) && !empty($_GET[$var])) {
        if ($where) {
          $query_search .= ' AND ';
        } else {
          $query_search .= ' WHERE ';
          $where = true;
        }
        $query_search .= $var . ' = '. GetSQLValueString($_GET[$var], 'text');
      }
    }
    

    This does exactly the same as before, but in a more efficient manner.

     

    The first line is an array containing the names of the URL parameters. For this to work, the URL parameters need to have the same name as the equivalent columns in the database. It also assumes that the recordset is called "search" (Dreamweaver appends the name of the recordset to $query_).

     

    This will build the SQL query and include only those URL parameters that are set, and which have a value.

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 22, 2009 9:11 PM   in reply to aspenjian

    Ya, sorry David. It should be $_GET instead of $_POST. When u change the code of recordset, it will show red exclamation mark or maybe it doesnt listed in your server behaviour anymore. But dont worry, it's just mean that you have change the original code and the code u have now is still working. Maybe u should try the code that David gave to u.

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 22, 2009 9:18 PM   in reply to QiQi86

    Owh, ya.. Sorry. I forgot to explain a bit about my code. Maybe after this u could understand more

     

    query = "SELECT * FROM mainitems"; //select all variables from table mainitems

    $where = false; // means u dont have parameters for WHERE

     

    //to check if the first field(category) is posted

    if(isset($_GET['category']) && !empty($_GET['category'])) {

    $query .= " WHERE category LIKE %varCategory%"; //now u want to add category for WHERE parameter
    $where = true; //set where as true
    }

     

    //to check if price input is posted

    if(isset($_GET['price']) && !empty($_GET['price'])) {

    if ($where) { // check if has previous WHERE parameters
    $query .= ' AND '; // if has put AND
    } else {
    $query .= ' WHERE '; //if no then this will be the first WHERE parameter
    $where = true; //set it as true
    }
    $query .= "price LIKE %varprice%"; // this would be the parameters either for AND or WHERE
    }

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 23, 2009 2:31 AM   in reply to aspenjian

    aspenjian wrote:

     

    I get a red exclamation point appear next to the recordset in the server behavior panel in dreamweaver telling me that no recordset exists.

    Yes, that's normal. Once you change the recordset code by hand, Dreamweaver no longer recognizes it. That's not important. Just make sure that you lay out the page with dynamic text from the Bindings panel before you edit the recordset code. Welcome to the world of hand-coding.

     

    This is the line that you replace:

    $query_rsMainItems = "SELECT * FROM mainitems ORDER BY itemID ASC";
    

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 24, 2009 5:15 AM   in reply to aspenjian

    If you get a completely blank screen with a PHP page, it means there is an error on the page, and that display_errors is turned off in your PHP configuration. This is a security precaution, but you should always test pages in a development environment with display_errors turned on. To do so, you need to edit php.ini and restart your web server.

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 20, 2009 10:11 PM   in reply to David_Powers

    Hi David,

     

    I realize this is a bit late on this topic, but I just ran across it in my own quest.

     

    I too am trying to set up a search form wherein if some of the search form fields are left empty, the record will still be returned. I’ve deployed your code and it works nicely in the first three aspects but not the last. Hence:

    If some of the data table fields are empty and those same search fields are empty, the record returns with the remaining requested values, fine;

    If some of the data table fields are empty and those same search fields are filled, the record returns with the remaining requested values, fine;

    If the table fields contain the values, and the search form fields contain the values, it retrieves the record just fine;

    But if the table fields have values and the search form fields are empty, it returns nothing at all.

     

    I think this is the blank page to which aspenjian was referring previously. I know it isn't a error reporting config problem because I ripped some of the code out and it reported errors as expected.

     

    This is the code of yours that I've used:

    $expected = array(first_name, last_name, level, language, 'city', 'state', 'zip', );
    $query_search = 'SELECT * FROM french;
    $where = false;
    foreach ($expected as $var) {
      if (isset($_GET[$var]) && !empty($_GET[$var])) {
        if ($where) {
          $query_search .= ' AND ';
        } else {
          $query_search .= ' WHERE ';
          $where = true;
        }
        $query_search .= $var . ' = '. GetSQLValueString($_GET[$var], 'text');
      }
    }

     

    Can you help me figure out how to set it so that when someone decides not to fill in one of the search form fields, that the record is still returned?

     

    Thanks,

    Brian

     

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 21, 2009 2:29 AM   in reply to BrianNardone

    Have you tried the "long version" of the code mentioned earlier in the thread?

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 21, 2009 7:34 AM   in reply to DwFAQ

    Hi DW

     

    Nope. I took what he had condensed since it looked very much like some other

    contributions he's made which worked to a tee. What do you know that I don't

    about the long version.

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 21, 2009 9:21 AM   in reply to BrianNardone

    I know the long version works. It's the same method I've used for some time and I know it works so it might be worth trying. I haven't used the condensed version but from looking at it it looks like it should work. Try the long version and if that doesn't work then you'll know it's something else.

     

    Did you just plug in that one code into your page or did you already create a recordset to supplement the query? Also did you rename the query to coincide with your recordsets name? It's not a cookie cutter code that will work for anyone just pasted in you have to adjust the code for your recordset and DB. Also as mentioned the condensed version must have the same DB field names as the array.

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 21, 2009 6:04 PM   in reply to DwFAQ

    You know, I'm working with it again tonight and I think I can make it go

    without any additions. I'll let you know

     
    |
    Mark as:
  • Currently Being Moderated
    Sep 12, 2010 10:32 AM   in reply to David_Powers

    David,   This is a fabulously tidy bit of code you've provided for us and it has given me search results!  I have one little

    gotcha though.  I need an or condition in the middle of this.  There is one field in my database that can have seven different values.  For all the other selection criteria, they either select it or not, but with this one field, they can select all of the values, none of the values or some combination of the values.  I'm thinking that I might need to do the lengthier version to get this to work?  Do you have a suggestion for how I can determine if one or more of these values has been selected and then use an 'or' condition to test the database value against the selections?

     

    TIA!

     

    Laura

     
    |
    Mark as:
  • Currently Being Moderated
    Sep 12, 2010 11:43 AM   in reply to daneaffr

    I've attempted to use the longer version of the code to make my select statement work. This is the code that I've got now:

     

    $query_rs_results = "SELECT dogid, b_email, b_fname, b_lname, b_phone, b_url, dob, dod, reg_name, reg_no, o_email, o_fname, o_lname, o_phone, o_url, registry, semen_l, semen_f, semen_fc, semen_fr, photo FROM dog_main";

    // Set a flag to indicate whether the query has a WHERE clause

    // Set a variable to indicate if the select statement has a color value selected
    $where = false;
    $color = 0;

     

    if (isset($_GET['arm']) && !empty($_GET['arm'])) {
      $query_rs_results .= ' WHERE arm = '. GetSQLValueString($_GET['arm'], 'text');
      $where = true;
    }
    if (isset($_GET['bite']) && !empty($_GET['bite'])) {
      if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'bite = ' . GetSQLValueString($_GET['bite'], 'text');
    }
    if (isset($_GET['body']) && !empty($_GET['body'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'body1 = ' . GetSQLValueString($_GET['body'], 'text');
    }
    if (isset($_GET['breadth']) && !empty($_GET['breadth'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'breadth = ' . GetSQLValueString($_GET['breadth'], 'text');
    }
    if (isset($_GET['brisket']) && !empty($_GET['brisket'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'brisket = ' . GetSQLValueString($_GET['brisket'], 'text');
    }
    if (isset($_GET['dentition']) && !empty($_GET['dentition'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'dentition = ' . GetSQLValueString($_GET['dentition'], 'text');
    }
    if (isset($_GET['color_b']) && !empty($_GET['color_b'])) {
    $color = $color + 1;
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
      }
      $query_rs_results .= '(color = ' . GetSQLValueString($_GET['color_b'], 'text');
    }
    if (isset($_GET['color_bl']) && !empty($_GET['color_bl'])) {
    $color = $color + 1;
    If ($color > 1) {
      $query_rs_results .= ' OR ';
         } else {
                if ($where) {
                   $query_rs_results .= ' AND ';
                } else {
                   $query_rs_results .= ' WHERE ';
       }
              }

      $query_rs_results .= 'color = ' . GetSQLValueString($_GET['color_bl'], 'text');
    }
    if (isset($_GET['color_br']) && !empty($_GET['color_br'])) {
    $color = $color + 1;
    If ($color > 1) {
      $query_rs_results .= ' OR ';
         } else {
                if ($where) {
                   $query_rs_results .= ' AND ';
                } else {
                   $query_rs_results .= ' WHERE ';
       }
              }

      $query_rs_results .= 'color = ' . GetSQLValueString($_GET['color_br'], 'text');
    }
    if (isset($_GET['color_f']) && !empty($_GET['color_f'])) {
    $color = $color + 1;
    If ($color > 1) {
      $query_rs_results .= ' OR ';
         } else {
                if ($where) {
                   $query_rs_results .= ' AND ';
                } else {
                   $query_rs_results .= ' WHERE ';
       }
              }

      $query_rs_results .= 'color = ' . GetSQLValueString($_GET['color_f'], 'text');
    }
    if (isset($_GET['color_h']) && !empty($_GET['color_h'])) {
    $color = $color + 1;
    If ($color > 1) {
      $query_rs_results .= ' OR ';
         } else {
                if ($where) {
                   $query_rs_results .= ' AND ';
                } else {
                   $query_rs_results .= ' WHERE ';
       }
              }

      $query_rs_results .= 'color = ' . GetSQLValueString($_GET['color_h'], 'text');
    }
    if (isset($_GET['color_m']) && !empty($_GET['color_m'])) {
    $color = $color + 1;
    If ($color > 1) {
      $query_rs_results .= ' OR ';
         } else {
                if ($where) {
                   $query_rs_results .= ' AND ';
                } else {
                   $query_rs_results .= ' WHERE ';
       }
              }

      $query_rs_results .= 'color = ' . GetSQLValueString($_GET['color_m'], 'text');
    }
    if (isset($_GET['color_o']) && !empty($_GET['color_o'])) {
    $color = $color + 1;
    If ($color > 1) {
      $query_rs_results .= ' OR ';
         } else {
                if ($where) {
                   $query_rs_results .= ' AND ';
                } else {
                   $query_rs_results .= ' WHERE ';
       }
              }

      $query_rs_results .= 'color = ' . GetSQLValueString($_GET['color_o'], 'text');
    }
    if ($color > 0) {
    $query_rs_results .= ') ';
    }
    if (isset($_GET['ears']) && !empty($_GET['ears'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'ears = ' . GetSQLValueString($_GET['ears'], 'text');
    }
    if (isset($_GET['eye_color']) && !empty($_GET['eye_color'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'eye_color = ' . GetSQLValueString($_GET['eye_color'], 'text');
    }
    if (isset($_GET['eye_shape']) && !empty($_GET['eye_shape'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'eye_shape = ' . GetSQLValueString($_GET['eye_shape'], 'text');
    }
    if (isset($_GET['eye_tight']) && !empty($_GET['eye_tight'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'eye_tight = ' . GetSQLValueString($_GET['eye_tight'], 'text');
    }
    if (isset($_GET['f_feet']) && !empty($_GET['f_feet'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'f_feet = ' . GetSQLValueString($_GET['f_feet'], 'text');
    }
    if (isset($_GET['head']) && !empty($_GET['head'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'head = ' . GetSQLValueString($_GET['head'], 'text');
    }
    if (isset($_GET['hocks']) && !empty($_GET['hocks'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'hocks = ' . GetSQLValueString($_GET['hocks'], 'text');
    }
    if (isset($_GET['lip']) && !empty($_GET['lip'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'lip = ' . GetSQLValueString($_GET['lip'], 'text');
    }
    if (isset($_GET['longevity']) && !empty($_GET['longevity'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'longevity = ' . GetSQLValueString($_GET['longevity'], 'text');
    }
    if (isset($_GET['neck_length']) && !empty($_GET['neck_length'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'neck_length = ' . GetSQLValueString($_GET['neck_length'], 'text');
    }
    if (isset($_GET['neck_set']) && !empty($_GET['neck_set'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'neck_set = ' . GetSQLValueString($_GET['neck_set'], 'text');
    }
    if (isset($_GET['r_angulation']) && !empty($_GET['r_angulation'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'r_angulation = ' . GetSQLValueString($_GET['r_angulation'], 'text');
    }
    if (isset($_GET['r_feet']) && !empty($_GET['r_feet'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'r_feet = ' . GetSQLValueString($_GET['r_feet'], 'text');
    }
    if (isset($_GET['semen_f']) && !empty($_GET['semen_f'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'semen_f = ' . GetSQLValueString($_GET['semen_f'], 'text');
    }
    if (isset($_GET['semen_fc']) && !empty($_GET['semen_fc'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'semen_fc = ' . GetSQLValueString($_GET['semen_fc'], 'text');
    }
    if (isset($_GET['semen_fr']) && !empty($_GET['semen_fr'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'semen_fr = ' . GetSQLValueString($_GET['semen_fr'], 'text');
    }
    if (isset($_GET['semen_l']) && !empty($_GET['semen_l'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'semen_l = ' . GetSQLValueString($_GET['semen_l'], 'text');
    }
    if (isset($_GET['shoulders']) && !empty($_GET['shoulders'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'shoulders = ' . GetSQLValueString($_GET['shoulders'], 'text');
    }
    if (isset($_GET['tail_length']) && !empty($_GET['tail_length'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'tail_length = ' . GetSQLValueString($_GET['tail_length'], 'text');
    }
    if (isset($_GET['tail_set']) && !empty($_GET['tail_set'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'tail_set = ' . GetSQLValueString($_GET['tail_set'], 'text');
    }
    if (isset($_GET['temperament']) && !empty($_GET['temperament'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'temperament = ' . GetSQLValueString($_GET['temperament'], 'text');
    }
    if (isset($_GET['topline']) && !empty($_GET['topline'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'topline = ' . GetSQLValueString($_GET['topline'], 'text');
    }
    if (isset($_GET['withers']) && !empty($_GET['withers'])) {
        if ($where) {
        $query_rs_results .= ' AND ';
      } else {
        $query_rs_results .= ' WHERE ';
        $where = true;
      }
      $query_rs_results .= 'withers = ' . GetSQLValueString($_GET['withers'], 'text');
    }
    echo "Query search = ".$query_rs_results;
    $query_limit_rs_results = sprintf("%s LIMIT %d, %d", $query_rs_results, $startRow_rs_results, $maxRows_rs_results);
    $rs_results = mysql_query($query_limit_rs_results, $dqdb) or die(mysql_error());
    $row_rs_results = mysql_fetch_assoc($rs_results);

    if (isset($_POST['totalRows_rs_results'])) {
      $totalRows_rs_results = $_POST['totalRows_rs_results'];
    } else {
      $all_rs_results = mysql_query($query_rs_results);
      $totalRows_rs_results = mysql_num_rows($all_rs_results);
    }
    $totalPages_rs_results = ceil($totalRows_rs_results/$maxRows_rs_results)-1;

     

    I'm echoing $query_rs_results to verify that it is building the select statement correctly, but it is not.  The select statement being echoed is:

     

    SELECT dogid, b_email, b_fname, b_lname, b_phone, b_url, dob, dod, reg_name, reg_no, o_email, o_fname, o_lname, o_phone, o_url, registry, semen_l, semen_f, semen_fc, semen_fr, photo FROM dog_main

     

    The results page is displaying every dog in the database.

     

    What am I missing here?

     

    TIA,

     

    Laura

     
    |
    Mark as:
  • Currently Being Moderated
    Sep 27, 2011 4:26 AM   in reply to daneaffr

    Hope the people on this thread still read it and can help me with this?

     

    I am using this type of search for my Rugby club website and needed a search that can be submitted by the user to return specific members of staff.

     

    I am using Dreamweaver CS4 with ADDT

     

    The  following works:

     

    mysql_select_db($database_JuniorComm, $JuniorComm);

     

    $expected = array(    'RFUNumber' =>'text',

                        'CRBNumber' =>'text',

                        'role' =>'text',

                        'course'   => 'text',

                        'ageGroup' =>'text',

                        'medical' =>'text',

                        'safeguard_YN' =>'text',

                        'medical_YN' =>'text',

                        'disability_YN' =>'text',

                        'disability' => 'text');

     

     

    $query_search = "SELECT DISTINCT staff.*, ADDDATE(CRBCheck, INTERVAL 2 YEAR) AS EXPIRE, ADDDATE(safeGuard, INTERVAL 2 YEAR) AS SAFEEXPIRE

    FROM ((((((((staff

    LEFT JOIN courses_rel ON courses_rel.staff_ID=staff.staff_ID)

    LEFT JOIN courses ON courses.course_ID=courses_rel.course_ID)

    LEFT JOIN roles_rel ON roles_rel.Staff_ID=staff.staff_ID)

    LEFT JOIN roles ON roles.roles_ID=roles_rel.roles_ID)

    LEFT JOIN disability_rel ON disability_rel.Staff_ID=staff.staff_ID)

    LEFT JOIN disability ON disability.disability_ID=disability_rel.disability_ID)

    LEFT JOIN medical_rel ON medical_rel.Staff_ID=staff.staff_ID)

    LEFT JOIN medical ON medical.medical_ID=medical_rel.medical_ID)";

     

    // Set a flag to indicate whether the query has a WHERE clause

    $where = false;

     

     

    // Loop through the associatiave array of expected search values

    foreach ($expected as $var => $type) {

      if (isset($_GET[$var])) {

        $value = trim(urldecode($_GET[$var]));

        if (!empty($value)) {

          // Check if the value begins with > or <

          // If so, use it as the operator, and extract the value

          if ($value[0] == '>' || $value[0] == '<') {

            $operator = $value[0];

            $value = ltrim(substr($value, 1));

          } elseif (strtolower($type) != 'like') {

            $operator = '=';

          }

          // Check if the WHERE clause has been added yet

          if ($where) {

            $query_search .= ' AND ';

          } else {

            $query_search .= ' WHERE ';

            $where = true;

           }

          // Build the SQL query using the right operator and data type

          $type = strtolower($type);

          switch($type) {

            case 'like':

              $query_search .= "`$var` LIKE " . GetSQLValueString('%' .

    $value . '%', "text");

              break;

            case 'int':

            case 'double':

            case 'date':

              $query_search .= "`$var` $operator " .

    GetSQLValueString($value, "$type" );

              break;

            default:

            $query_search .= "`$var` = " . GetSQLValueString($value,

    "$type");

          }

        }

      }

    }

     

    $search = mysql_query($query_search, $JuniorComm) or die(mysql_error());

    $row_search = mysql_fetch_assoc($search);

    $totalRows_search = mysql_num_rows($search);

     

     

    The LEFT JOINS are information from relationship tables as some fields can have more than one entry such as roles - a person can have multiple roles. This is my problem when a person has multiple roles I can extract the roles from the DB using GROUP_CONCAT(DISTINCT' ', roles.role) as 'role' in my query, BUT, this returns multiple results for the same person. I know I can group these results using GROUP BY staff.staff_ID but I do not know how where to include the GROUP BY in the query. (I would also like to include a ORDER BY function).

     

    So my question is simple (althought the answer is more complex) where, and how,  do I put the GROUP BY and ORDER BY???

     

    Any help would be appreciated.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points