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.

[bregent]

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

[wizbard]

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

[bregent]

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.

[wizbard]

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

[bregent]

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

[wizbard]

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:

Thanks for your patience.

[wizbard]

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

[bregent]

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.

[wizbard]

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? 

[wizbard]

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

[bregent]

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

[wizbard]

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?

[bregent]

You got it.

[wizbard]

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

[bregent]

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.

[wizbard]

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&searchStr2=...

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=&searchStr2=Todd&se...

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=&searchStr2=&search...

[bregent]

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.

[wizbard · Accepted solution]

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