22 Replies Latest reply: Sep 27, 2011 4:26 AM by LouieCypher RSS

    Ignore Empty Parameter

    aspenjian

      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.

        • 1. Re: Ignore Empty Parameter
          David_Powers CommunityMVP

          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.

          • 2. Re: Ignore Empty Parameter
            aspenjian Community Member

            Thanks David, that helped tremendously with those two parameters. I was experimenting with just those two until I got it working, and now I'm trying to add further parameters and it seems that the query is only reading the first two parameters, being category and price.

             

            Here is my query string:

             

            SELECT *
            FROM mainitems
            WHERE category LIKE %varCategory% AND price LIKE %varPrice% AND time1 LIKE %varTime1% AND time2 LIKE %varTime2% AND city LIKE %varCity% AND state LIKE %varState% AND zip LIKE %varZip% AND neighborhood LIKE %varNeighborhood%
            

             

            And here is the URL string that the form is submitting:

             

            http://localhost/~aspen/website/results.php?category=%25&price=%25&city=&state=%25&zip=&ne ighborhood=&search2=Search

             

            As you can see, the first parameters are price and category, but if I search by any other parameter, it brings up every item in the recordset.

             

            Also, and this might be somewhat of a second question (although somewhat related), which I'm going to squeeze in here. If any of the variables are left out of the URL, the search doesn't work at all. So, for example, if I have a link for each category in my recordset, and when a user clicks on that link, I want the recordset to filter by that category, it seems that I can't just use "...results.php?category=shows" . I have to write out the entire URL with empty/blank parameters. Is that correct?

            • 3. Re: Ignore Empty Parameter
              QiQi86 Community Member

              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.

              • 4. Re: Ignore Empty Parameter
                aspenjian Community Member

                Qiqi,

                 

                I appreciate your help but I do most of my designing with dreamweaver and am not that familiar with code, so I have no idea what you mean in your post. Could you explain in laymen's terms what I'm supposed to do with all of that or how I can do it more through dreamweaver? Thanks!

                • 5. Re: Ignore Empty Parameter
                  David_Powers CommunityMVP

                  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.

                  • 6. Re: Ignore Empty Parameter
                    David_Powers CommunityMVP

                    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.

                    • 7. Re: Ignore Empty Parameter
                      aspenjian Community Member

                      Here is the code that I'm trying to copy and paste this query into:

                       

                       

                      The Green underlined line is the one that I assume I should replace with the code you gave me. Also, I had enough sense to replace all the "$query_search" with "$query_rsMainItems". When I do this, however, and refresh, I get a red exclamation point appear next to the recordset in the server behavior panel in dreamweaver telling me that no recordset exists. I must be putting your code in the wrong place, where should it go?

                      • 8. Re: Ignore Empty Parameter
                        aspenjian Community Member

                        It looks like the code didn't post, here is the code I was referring to:

                         

                         

                        • 9. Re: Ignore Empty Parameter
                          aspenjian Community Member

                          <?php
                          if (!function_exists("GetSQLValueString")) {
                          function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
                          {
                            $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
                          
                            $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
                          
                            switch ($theType) {
                              case "text":
                                $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
                                break;    
                              case "long":
                              case "int":
                                $theValue = ($theValue != "") ? intval($theValue) : "NULL";
                                break;
                              case "double":
                                $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
                                break;
                              case "date":
                                $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
                                break;
                              case "defined":
                                $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
                                break;
                            }
                            return $theValue;
                          }
                          }
                          
                          $editFormAction = $_SERVER['PHP_SELF'];
                          if (isset($_SERVER['QUERY_STRING'])) {
                            $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
                          }
                          
                          if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "itemSubmit")) {
                            $insertSQL = sprintf("INSERT INTO mainitems (title, category, `description`, price, address, city, `state`, zip, neighborhood, starttime, email, phone, endtime, URL) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                                                 GetSQLValueString($_POST['spryTitle'], "text"),
                                                 GetSQLValueString($_POST['spryCategory'], "text"),
                                                 GetSQLValueString($_POST['description2'], "text"),
                                                 GetSQLValueString($_POST['spryPrice'], "int"),
                                                 GetSQLValueString($_POST['spryAddress'], "text"),
                                                 GetSQLValueString($_POST['spryCity'], "text"),
                                                 GetSQLValueString($_POST['spryState'], "text"),
                                                 GetSQLValueString($_POST['spryZip'], "int"),
                                                 GetSQLValueString($_POST['neighborhood'], "text"),
                                                 GetSQLValueString($_POST['startTime'], "date"),
                                                 GetSQLValueString($_POST['email'], "text"),
                                                 GetSQLValueString($_POST['contactPhoneNumber'], "text"),
                                                 GetSQLValueString($_POST['endTime'], "date"),
                                                 GetSQLValueString($_POST['website'], "text"));
                          
                            mysql_select_db($database_cementmonkey, $cementmonkey);
                            $Result1 = mysql_query($insertSQL, $cementmonkey) or die(mysql_error());
                          }
                          
                          mysql_select_db($database_cementmonkey, $cementmonkey);
                          $query_rsMainItems = "SELECT * FROM mainitems ORDER BY itemID ASC";
                          $rsMainItems = mysql_query($query_rsMainItems, $cementmonkey) or die(mysql_error());
                          $row_rsMainItems = mysql_fetch_assoc($rsMainItems);
                          $totalRows_rsMainItems = mysql_num_rows($rsMainItems);
                          ?>
                          

                          • 10. Re: Ignore Empty Parameter
                            QiQi86 Community Member

                            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.

                            • 11. Re: Ignore Empty Parameter
                              QiQi86 Community Member

                              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
                              }

                              • 12. Re: Ignore Empty Parameter
                                David_Powers CommunityMVP

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

                                • 13. Re: Ignore Empty Parameter
                                  aspenjian Community Member

                                  Thanks for the warm welcome to the world of hand coding. I've conceded to the fact that I need to just bite the bullet and learn PHP, so I got myself an "essential training" video. In the mean time, when I insert your code, besides dreamweaver giving me an error, when I preview the website, it comes up blank. As in, the browser comes up completely white. I have a pretty good understanding of HTML and this completely baffles me. I would think SOMETHING would show up on the screen, however distorted, if something were wrong with the code, but it's just a blank screen. Any thoughts?

                                  • 14. Re: Ignore Empty Parameter
                                    David_Powers CommunityMVP

                                    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.

                                    • 15. Re: Ignore Empty Parameter
                                      BrianNardone

                                      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

                                       

                                      • 16. Re: Ignore Empty Parameter
                                        DwFAQ Community Member

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

                                        • 17. Re: Ignore Empty Parameter
                                          BrianNardone Community Member

                                          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.

                                          • 18. Re: Ignore Empty Parameter
                                            DwFAQ Community Member

                                            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.

                                            • 19. Re: Ignore Empty Parameter
                                              BrianNardone Community Member

                                              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

                                              • 20. Re: Ignore Empty Parameter
                                                daneaffr Community Member

                                                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

                                                • 21. Re: Ignore Empty Parameter
                                                  daneaffr Community Member

                                                  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

                                                  • 22. Re: Ignore Empty Parameter
                                                    LouieCypher

                                                    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.