18 Replies Latest reply: Jun 14, 2012 11:50 AM by wizbard RSS

    Trouble with search form recordset WHERE...AND clause

    wizbard

      Hi, I have created a member directory 'memb_directory' page with a repeating table and added a 'lastName' search box that works just fine as long as I use the simple, one variable recordset format ('rsMemDir') in Dreamweaver 5.5. But I need to have it only return those names for whom the 'member_pub' boolean field in the MySQL database = 1 (checkbox is checked) and for the life of me, I've tried every 'WHERE ... AND' SQL statement permutation I can think of, but keep getting an "internal server error" response as DW analyzes the code. Any leads much appreciated.

        • 1. Re: Trouble with search form recordset WHERE...AND clause
          bregent CommunityMVP

          Please show us the SQL string you are trying to use.

          • 2. Re: Trouble with search form recordset WHERE...AND clause
            wizbard Community Member

            bregent,

             

            I am so sorry I haven't seen this from you before today.  I got into another section of the website and guess I haven't checked back.  Here is the MySQL string I think you are referring to:

             

            SELECT *

            FROM member

            WHERE member_pub = 1 AND lastName like %colname% OR firstName like %colname%  OR emailLogin = %colname%

            ORDER BY lastName ASC

             

            FYI, the runtime value of 'colname' is $_GET['searchStr']

             

            -wiz

            • 3. Re: Trouble with search form recordset WHERE...AND clause
              bregent CommunityMVP

              You just need to use parenthesis to control the order of operations.

               

              SELECT *

              FROM member

              WHERE member_pub = 1 AND (lastName like %colname% OR firstName like %colname%  OR emailLogin = %colname%)

              ORDER BY lastName ASC

               

              SQL WHERE clauses use basic PEMDAS rules to combine operations. In boolean algebra, AND is equivalent to multiplication, while OR is addition.

              • 4. Re: Trouble with search form recordset WHERE...AND clause
                wizbard Community Member

                bregent - Thanks, it's starting to make sense.  When I plug it in, however, it pulls-up all records where member_pub = 1.

                • 5. Re: Trouble with search form recordset WHERE...AND clause
                  bregent CommunityMVP

                  Please show us

                   

                  1) The sql statement you are using

                  2) the value of the search string you are testing

                  3) an example of the results you are getting

                  • 6. Re: Trouble with search form recordset WHERE...AND clause
                    wizbard Community Member

                    The code I'm using comes from what Dreamweaver generates from my Recordset (rsMemDir).  Here is the section I think you're asking about:

                     

                    $colname_rsMemDir = "-1";

                    if (isset($_GET['searchStr'])) {

                      $colname_rsMemDir = $_GET['searchStr'];

                    }

                    mysql_select_db($database_iama_test, $iama_test);

                    $query_rsMemDir = sprintf("SELECT * FROM member WHERE member_pub = 1 AND (lastName like %s OR firstName like %s  OR emailLogin = %s) ORDER BY lastName ASC", GetSQLValueString("%" . $colname_rsMemDir . "%", "text"),GetSQLValueString("%" . $colname_rsMemDir . "%", "text"),GetSQLValueString("%" . $colname_rsMemDir . "%", "text"));

                    $query_limit_rsMemDir = sprintf("%s LIMIT %d, %d", $query_rsMemDir, $startRow_rsMemDir, $maxRows_rsMemDir);

                    $rsMemDir = mysql_query($query_limit_rsMemDir, $iama_test) or die(mysql_error());

                    $row_rsMemDir = mysql_fetch_assoc($rsMemDir);

                     

                    And here is a jpg of what my results screen displays - which is a list of all those records I have assigned a value of 1 to mem_pub:

                     

                    MemDir-results.jpg

                    Thanks for your patience.

                    • 7. Re: Trouble with search form recordset WHERE...AND clause
                      wizbard Community Member

                      correction: sorry, I meant to write "member_pub" the actual name of the field in MySQL.

                      • 8. Re: Trouble with search form recordset WHERE...AND clause
                        bregent CommunityMVP

                        But what is the value of the search string submitted when those results are displayed? You might want to use an echo statement to verify that. Also, you are using wildcards with the equality operator for the email_login, which is not valid. Either use the LIKE predicate, or remove the wildcard characters.

                        • 9. Re: Trouble with search form recordset WHERE...AND clause
                          wizbard Community Member

                          Fixed the wildcard issue.  Still working to get it to echo where I can see it on my form.  Should each of my entry fields have the same input/id name or should each be unique? 

                          • 10. Re: Trouble with search form recordset WHERE...AND clause
                            wizbard Community Member

                            Okay, for the lastName field, it works and echos the name I test, but for firstName and Email it must be echoing null.

                            • 11. Re: Trouble with search form recordset WHERE...AND clause
                              bregent CommunityMVP

                              It doesn't appear that you are using those other fields. You are populating only one variable and using that for all 3 conditions in the WHERE clause.

                               

                              $colname_rsMemDir = "-1";

                              if (isset($_GET['searchStr'])) {

                                $colname_rsMemDir = $_GET['searchStr'];

                              • 12. Re: Trouble with search form recordset WHERE...AND clause
                                wizbard Community Member

                                If I understand what you are saying, I should either:

                                modify the form to have only one entry field where a person can search Lastname, Firstname or Email

                                OR

                                add more variables to the php code so that each field will be populated.

                                 

                                Is that correct?

                                • 14. Re: Trouble with search form recordset WHERE...AND clause
                                  wizbard Community Member

                                  Well, I had hoped to report back with full success.  My echo test does return the correct values for input from each field, but the form itself returns with all of the records, instead of just those fitting the input criteria.  Here is the code for the echo test, which immediately follows the form:

                                   

                                  <?php

                                  if ($colname_rsMemDir) {

                                      echo $colname_rsMemDir;

                                  } elseif ($colname2_rsMemDir) {

                                      echo $colname2_rsMemDir;

                                  } elseif ($colname3_rsMemDir) {

                                      echo $colname3_rsMemDir;

                                  } else {

                                      echo "NULL, DAMMIT";

                                  }

                                  ?>

                                   

                                  I hate to burden you with the full recordset code, but here it is anyway.  It is pretty-much what Dreamweaver generates from my rsMemDir recordset and repeat region definitions.  I've also made sure the variable names match the input names in the form html.  Here's the code:

                                   

                                  <?php
                                  if (!function_exists("GetSQLValueString")) {
                                  function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
                                  {
                                    if (PHP_VERSION < 6) {
                                      $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;
                                  }
                                  }

                                  $currentPage = $_SERVER["PHP_SELF"];

                                  if (!function_exists("GetSQLValueString")) {
                                  function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
                                  {
                                    if (PHP_VERSION < 6) {
                                      $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;
                                  }
                                  }

                                  $maxRows_rsMemDir = 10;
                                  $pageNum_rsMemDir = 0;
                                  if (isset($_GET['pageNum_rsMemDir'])) {
                                    $pageNum_rsMemDir = $_GET['pageNum_rsMemDir'];
                                  }
                                  $startRow_rsMemDir = $pageNum_rsMemDir * $maxRows_rsMemDir;

                                  $colname_rsMemDir = "-1";
                                  if (isset($_GET['searchStr'])) {
                                    $colname_rsMemDir = $_GET['searchStr'];
                                  }
                                  $colname2_rsMemDir = "-1";
                                  if (isset($_GET['searchStr2'])) {
                                    $colname2_rsMemDir = $_GET['searchStr2'];
                                  }
                                  $colname3_rsMemDir = "-1";
                                  if (isset($_GET['searchStr3'])) {
                                    $colname3_rsMemDir = $_GET['searchStr3'];
                                  }
                                  mysql_select_db($database_iama_test, $iama_test);
                                  $query_rsMemDir = sprintf("SELECT * FROM member WHERE member_pub = 1 AND (lastName like %s OR firstName like %s OR emailLogin like %s) ORDER BY lastName ASC", GetSQLValueString("%" . $colname_rsMemDir . "%", "text"),GetSQLValueString("%" . $colname2_rsMemDir . "%", "text"),GetSQLValueString("%" . $colname3_rsMemDir . "%", "text"));
                                  $query_limit_rsMemDir = sprintf("%s LIMIT %d, %d", $query_rsMemDir, $startRow_rsMemDir, $maxRows_rsMemDir);
                                  $rsMemDir = mysql_query($query_limit_rsMemDir, $iama_test) or die(mysql_error());
                                  $row_rsMemDir = mysql_fetch_assoc($rsMemDir);

                                  if (isset($_GET['totalRows_rsMemDir'])) {
                                    $totalRows_rsMemDir = $_GET['totalRows_rsMemDir'];
                                  } else {
                                    $all_rsMemDir = mysql_query($query_rsMemDir);
                                    $totalRows_rsMemDir = mysql_num_rows($all_rsMemDir);
                                  }
                                  $totalPages_rsMemDir = ceil($totalRows_rsMemDir/$maxRows_rsMemDir)-1;

                                  $queryString_rsMemDir = "";
                                  if (!empty($_SERVER['QUERY_STRING'])) {
                                    $params = explode("&", $_SERVER['QUERY_STRING']);
                                    $newParams = array();
                                    foreach ($params as $param) {
                                      if (stristr($param, "pageNum_rsMemDir") == false &&
                                          stristr($param, "totalRows_rsMemDir") == false) {
                                        array_push($newParams, $param);
                                      }
                                    }
                                    if (count($newParams) != 0) {
                                      $queryString_rsMemDir = "&" . htmlentities(implode("&", $newParams));
                                    }
                                  }
                                  $queryString_rsMemDir = sprintf("&totalRows_rsMemDir=%d%s", $totalRows_rsMemDir, $queryString_rsMemDir);
                                  ?>

                                   

                                  If you don't see anything without wasting too much time, just let me know and I think I'll go back to the lastName only search, which works just fine.

                                   

                                  Many thanks for all your time.

                                   

                                  -wiz

                                  • 15. Re: Trouble with search form recordset WHERE...AND clause
                                    bregent CommunityMVP

                                    Please echo the value of $query_rsMemDir after it is populated, and also attach the url querystring so we can see how the values are populated from the form.

                                    • 16. Re: Trouble with search form recordset WHERE...AND clause
                                      wizbard Community Member

                                      Here are the respective results using Todd Schultz as the names and todd@ as the email:

                                       

                                      lastName:

                                           $query_rsMemDir:         SELECT * FROM member WHERE member_pub = 1 AND (lastName like '%schultz%' OR firstName like '%%' OR emailLogin like '%%') ORDER BY lastName ASC

                                           url query:                          http://localhost/iama_test/public_html/members_only/memb_directory.php?searchStr=schultz&s earchStr2=&searchStr3=&search=Search

                                       

                                      firstName:

                                           $query_rsMemDir:          SELECT * FROM member WHERE member_pub = 1 AND (lastName like '%%' OR firstName like '%Todd%' OR emailLogin like '%%') ORDER BY lastName ASC

                                           url query:                           http://localhost/iama_test/public_html/members_only/memb_directory.php?searchStr=&searchSt r2=Todd&searchStr3=&search=Search

                                       

                                      emailLogin:

                                           $query_rsMemDir:          SELECT * FROM member WHERE member_pub = 1 AND (lastName like '%%' OR firstName like '%%' OR emailLogin like '%todd@%') ORDER BY lastName ASC

                                           url query:                           http://localhost/iama_test/public_html/members_only/memb_directory.php?searchStr=&searchSt r2=&searchStr3=todd@&search=Search

                                      • 17. Re: Trouble with search form recordset WHERE...AND clause
                                        bregent CommunityMVP

                                        OK, there's your problem. First of all, I'm not a php programmer.

                                         

                                        >$colname3_rsMemDir = "-1";

                                         

                                        Here you are setting the default value to -1.

                                         

                                         

                                        >if (isset($_GET['searchStr3'])) {

                                        >  $colname3_rsMemDir = $_GET['searchStr3'];

                                         

                                        Here you test if the field is set. If it is, you assign it to the variable, otherwise you keep the default value. Problem is that is appears isset() considers the empty string as set. So you really need to test if isset() and not equal to the empty string (!=""). Or consider using the empty() function rather than isset(). Again, I'm not a php programmer so I don't know if one method is better than another.

                                        • 18. Re: Trouble with search form recordset WHERE...AND clause
                                          wizbard Community Member

                                          bregent:

                                           

                                          Wanted to get back to you and say thanks again for helping me move forward on this issue.  At least you are more of a php expert than I am.  You pointed me in the right direction.  FYI (and any others who might stumble in, here is what finally worked:

                                           

                                          $colname_rsMemDir = "-1";
                                          if ($_GET['searchStr']) {
                                            $colname_rsMemDir = $_GET['searchStr'];
                                          }
                                          $colname2_rsMemDir = "-1";
                                          if ($_GET['searchStr2']) {
                                            $colname2_rsMemDir = $_GET['searchStr2'];
                                          }
                                          $colname3_rsMemDir = "-1";
                                          if ($_GET['searchStr3']) {
                                            $colname3_rsMemDir = $_GET['searchStr3'];
                                          }

                                          mysql_select_db($database_iama_test, $iama_test);

                                          if((!isset($_GET['searchStr']))&&(!isset($_GET['searchStr2']))&&(!isset($_GET['searchStr3' ]))) {
                                          //this will fetch ALL members who check the include box
                                          //this is the "first load scenario"
                                          $query_rsMemDir = "SELECT * FROM member WHERE member_pub = 1 ";
                                          } else {

                                           

                                          $query_rsMemDir = sprintf("SELECT * FROM member WHERE member_pub = 1 AND (lastName like %s OR firstName like %s OR emailLogin like %s) ORDER BY lastName ASC", GetSQLValueString("%" . $colname_rsMemDir . "%", "text"),GetSQLValueString("%" . $colname2_rsMemDir . "%", "text"),GetSQLValueString("%" . $colname3_rsMemDir . "%", "text")); }

                                           

                                          I salute you!

                                           

                                          -wiz