Skip to main content
W
wizbard
Inspiring
May 14, 2012
Answered

Trouble with search form recordset WHERE...AND clause

  • May 14, 2012
  • 1 reply
  • 3777 views

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.

This topic has been closed for replies.
Correct answer 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.


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

1 reply

B
bregent
Participating Frequently
May 15, 2012

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

W
wizbardAuthor
Inspiring
May 21, 2012

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

B
bregent
Participating Frequently
May 21, 2012

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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices