Skip navigation
Currently Being Moderated

Trouble with search form recordset WHERE...AND clause

May 14, 2012 3:53 PM

Tags: #dreamweaver_cs5.5 #recordset #search_box

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.

 
Replies
  • Currently Being Moderated
    May 15, 2012 11:10 AM   in reply to wizbard

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

     
    |
    Mark as:
  • Currently Being Moderated
    May 21, 2012 3:52 PM   in reply to wizbard

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    May 29, 2012 11:28 AM   in reply to wizbard

    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

     
    |
    Mark as:
  • Currently Being Moderated
    May 29, 2012 12:57 PM   in reply to wizbard

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    May 30, 2012 2:27 PM   in reply to wizbard

    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'];

     
    |
    Mark as:
  • Currently Being Moderated
    May 30, 2012 3:53 PM   in reply to wizbard

    You got it.

     
    |
    Mark as:
  • Currently Being Moderated
    May 31, 2012 3:04 PM   in reply to wizbard

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    May 31, 2012 3:48 PM   in reply to wizbard

    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.

     
    |
    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