Using Check Boxes to Search

Hi,
I have a table column in the database which holds more than one value (i.e, value 1, value 2, value 3)

In the search box I am using check boxes where I want the user to be able to view more than one value (value 1 and/or value 2, and/or value 3)
Currently, using the syntax below, the results will show results where all values are included, but not those where just one is included (hope this makes sense!)

An example of this can be viewed at:
v_properties_Results.php


<?php
//WA Database Search (Copyright 2005, WebAssist.com)
//Recordset: WADAproperties;
//Searchpage: properties_Search.php;
//Form: WADASearchForm;
$WADbSearch1_DefaultWhere = "";
if (!session_id()) session_start();
if ((((isset($_GET["submit"]))?$_GET["submit"]:"") != "")) {
$WADbSearch1 = new FilterDef;
$WADbSearch1->initializeQueryBuilder("MYSQL","1");
//keyword array declarations

//comparison list additions
$WADbSearch1->addComparison("PropertyType","".((isset($_GET["Assembly"]))?$_GET["Assembly"]:"") ."","AND","=",0);
$WADbSearch1->addComparison("PropertyType","".((isset($_GET["HealthDay"]))?$_GET["HealthDay"]:"") ."","AND","Includes",0);
$WADbSearch1->addComparison("PropertyType","".((isset($_GET["Industrial"]))?$_GET["Industrial"]:"") ."","AND","Includes",0);
$WADbSearch1->addComparison("PropertyType","".((isset($_GET["Investment"]))?$_GET["Investment"]:"") ."","AND","Includes",0);
$WADbSearch1->addComparison("PropertyType","".((isset($_GET["Land"]))?$_GET["Land"]:"") ."","AND","Includes",0);
$WADbSearch1->addComparison("PropertyType","".((isset($_GET["Licenced"]))?$_GET["Licenced"]:"") ."","AND","Includes",0);
$WADbSearch1->addComparison("PropertyType","".((isset($_GET["Mixed"]))?$_GET["Mixed"]:"") ."","AND","Includes",0);
$WADbSearch1->addComparison("PropertyType","".((isset($_GET["Motor"]))?$_GET["Motor"]:"") ."","AND","Includes",0);
$WADbSearch1->addComparison("PropertyType","".((isset($_GET["Office"]))?$_GET["Office"]:"") ."","AND","Includes",0);
$WADbSearch1->addComparison("PropertyType","".((isset($_GET["Residential"]))?$_GET["Residential"]:"") ."","AND","Includes",0);
$WADbSearch1->addComparison("PropertyType","".((isset($_GET["Retail"]))?$_GET["Retail"]:"") ."","AND","Includes",0);
$WADbSearch1->addComparison("PropertyType","".((isset($_GET["Storage"]))?$_GET["Storage"]:"") ."","AND","Includes",0);
$WADbSearch1->addComparison("RegionName","".((isset($_GET["region"]))?$_GET["region"]:"") ."","AND","=",0);
$WADbSearch1->addComparison("SaleORLease","".((isset($_GET["status"]))?$_GET["status"]:"") ."","AND","=",0);

//save the query in a session variable
if (1 == 1) {
$_SESSION["WADbSearch1_properties_Results"]=$WADbSearch1->whereClause;
}
}
else {
$WADbSearch1 = new FilterDef;
$WADbSearch1->initializeQueryBuilder("MYSQL","1");
//get the filter definition from a session variable
if (1 == 1) {
if (isset($_SESSION["WADbSearch1_properties_Results"]) && $_SESSION["WADbSearch1_properties_Results"] != "") {
$WADbSearch1->whereClause = $_SESSION["WADbSearch1_properties_Results"];
}
else {
$WADbSearch1->whereClause = $WADbSearch1_DefaultWhere;
}
}
else {
$WADbSearch1->whereClause = $WADbSearch1_DefaultWhere;
}
}
$WADbSearch1->whereClause = str_replace("\\''", "''", $WADbSearch1->whereClause);
$WADbSearch1whereClause = '';
?>
<?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;
}
}

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;
}
}
?>
<?php
$currentPage = $_SERVER["PHP_SELF"];
?>
<?php
$maxRows_WADAproperties = 5;
$pageNum_WADAproperties = 0;
if (isset($_GET['pageNum_WADAproperties'])) {
$pageNum_WADAproperties = $_GET['pageNum_WADAproperties'];
}
$startRow_WADAproperties = $pageNum_WADAproperties * $maxRows_WADAproperties;

mysql_select_db($database_DBNAME, $DBNAME);
$query_WADAproperties = "SELECT MainPropertiesID, MainPropertiesIDold, SLNNumberID, FirstName, LastName, Company, PropertyType, SaleORLease, RegionName, Address1, Address2, Address3, City, County, PostCode, Country, ThumbPath, Thumb, PicturePath, Picture, AgentLogo, PropertyDetails, `Size`, Sep, SizeMax, SizeType, shortDescription, longdescription, CURR, PriceDetails, RentalPeriod, Price, Status FROM properties WHERE Status = 'Approved' ORDER BY City ASC";
setQueryBuilderSource($query_WADAproperties,$WADbSearch1,false);
$query_limit_WADAproperties = sprintf("%s LIMIT %d, %d", $query_WADAproperties, $startRow_WADAproperties, $maxRows_WADAproperties);
$WADAproperties = mysql_query($query_limit_WADAproperties, $marle662) or die(mysql_error());
$row_WADAproperties = mysql_fetch_assoc($WADAproperties);

if (isset($_GET['totalRows_WADAproperties'])) {
$totalRows_WADAproperties = $_GET['totalRows_WADAproperties'];
} else {
$all_WADAproperties = mysql_query($query_WADAproperties);
$totalRows_WADAproperties = mysql_num_rows($all_WADAproperties);
}
$totalPages_WADAproperties = ceil($totalRows_WADAproperties/$maxRows_WADAproperties)-1;

Your help is appreciated!

it seams to work when I test it. If I select "Assembly and Leisure (D2)" the result returned includes 3 records



One thing I see is that you are using AND as the Separator for each of the search items. it sounds like you want to use OR.

Hi Jason,

I have a check boxes in a search form. Currently the field the check boxes refer to can have one or many values from a recordset. When the tick boxes are selected individually, they return the correct results, i.e. if check box 1 is selected, then if the value is in the field alone, or with other values, it returns the correct value. If however, more than one check box is selected, it returns the values where there is more than one in the field, and not the values where it is alone in the field (I hope this makes sense!).

I have included the file for you to look at.

In the DataAssist Search behavior, you have set each of the check box searches to use the AND separator. edit each one and select the Or Separator instead.

Got It (I think!)
I copied the AND "Includes"
for example:
$WADbSearch1->addComparison("PropertyType","".((isset($_GET["HealthDay"]))?$_GET["HealthDay"]:"") ."","AND","Includes",0);
$WADbSearch1->addComparison("PropertyType","".((isset($_GET["Industrial"]))?$_GET["Industrial"]:"") ."","AND","Includes",0);

and pasted at the end of the search server behaviour. Changed the properties "AND" to "OR" and "Includes" to = like this:
$WADbSearch1->addComparison("PropertyType","".((isset($_GET["HealthDay"]))?$_GET["HealthDay"]:"") ."","OR","=",0);
$WADbSearch1->addComparison("PropertyType","".((isset($_GET["Industrial"]))?$_GET["Industrial"]:"") ."","OR","=",0);

And it now gives me the results I want. - Thank you.