PDA

View Full Version : help to filter results on 4 dynamic list menus


LoobyLou
10-21-2009, 04:38 PM
Hi,

I have 4 list menus and 1 submit button posting to a recordset in my results page to get the data from my database, i want the user to be able to find the data based on a selection in all 4 menus or just based on a selction in 1, 2 or 3 of the menus and at the moment i only know how to filter the information by passing all 4 variables and doing query on all 4 using AND or doing the query based on 3 menus selected using AND and OR sql and the same when 2 menus selected, i can also write the OR sql if only 1 menu selected, HOW DO I QUALIFY HOW MANY OPTIONS FROM THE MENUS HAVE BEEN SELECTED? Im not even sure if i should be looking at a more advanced sql query or if in fact there is some can of If else php i should be doing, or something else. Im quite used to dreamweaver but this is my first dynamic site and am still learning php. Can anyone offer any help please?

edbr
10-22-2009, 01:34 AM
are you using dreamweaver to create this? meaning their database wizard?

LoobyLou
10-22-2009, 06:03 AM
Hey there, thanks for replying,

Yes i am using Dreamweaver to create my site but not using the database wizzard, I created 4 list menus on a search page with a sub,it button in a form then POSTED the results to the results page where i created a Dreamweaver recordset (did sql myself as need to create a couple of joins on my tables - using XAMPP for my database ), have pasted sql below as i know this is why im not getting the results i want but im not sure how to get the right results. Basically if a user selects an option from all four list menu catagories i want the search to find only the rows which have ALL four of the criteria, and if the user selects from only 3 menus then again i want the results to find only the rows with all 3 options equal etc.. at the moment if 3 options selected the recordset cannot differentiate betwwwn how many variables have been passed and my sql code allows for all options hence on a user sekecting from 3 menus the results include rows with data matching ANY of the colums rather than ALL colums matching.
This database wizzard, is it in CS3 version? and would that do what i need? please explain more ... sql recordsey below:
SELECT trueprice,`desc`, `propid`, `bathrooms`, `photo1`, locationtable.loc, typetable.style, bedtable.`number`
FROM detailstable JOIN locationtable ON detailstable.location=locationtable.locid JOIN typetable ON detailstable.type=typetable.typeid JOIN pricetable ON detailstable.price=pricetable.priceid JOIN bedtable ON detailstable.beds=bedtable.bedid
WHERE (location=varloc AND price = varprice AND type=vartype AND beds=varbed ) OR (price=varprice AND location=varloc AND type=vartype) OR (price=varprice AND location=varloc AND beds=varbed) OR (price=varprice AND beds=varbed AND type=vartype) OR ( location=varloc AND type=vartype AND beds=varbed) OR (price=varprice AND location=varloc) OR (price=varprice AND type=vartype) OR (price=varprice AND beds=varbed) OR (type=vartype AND location=varloc) OR (type=vartype AND beds=beds) OR (location=varloc AND beds=varbed) OR (price = varprice OR beds=varbed OR type=vartype OR location=varloc)
ORDER BY detailstable.trueprice ASC

look forward to your reply, thanks again,

LoobyLou

edbr
10-22-2009, 07:10 AM
right . wow thats some select statement or at least the where condition.
i would approach it as

select all then if( location==$value && price == $value) {echo ,,,,,,;}
if( location==$value || price == $value) {echo ,,,,,,;}

though neater with a switch statement may be clearer to work with 'if's' to get it right as there are so many possibilities

LoobyLou
10-22-2009, 02:46 PM
Hi,

So good of you to help me, much appreciated,
Yeah the WHERE statement is a bit mind blowing, and i know thats part of the problem as if only 2 options selected from the menus the query is still looking for records with either option and i want only records with both options if you know what i mean but if i dont put the OR statement bit in when only 1 menu selected there would be no results found.

I do follow the logic of what you have written regarding the if statement, so far most of my site has been coded by Dreamweaver and ive spent weeks looking at the php code trying to edit it with if else statements to get the right results and getting no where (my first time working with php), i have even tried creating 4 recordsets dependant on how many menus have been selected and that works to a better degree apart from all the duplicated code but still not producing what im after.

What im not sure about is where abouts in my php code i would do the if statements that you suggest, ive pasted the php from the results page that i have so far, i would appreciate it so much if you would take a look at what ive done so far and advise where i can make amendments with the IF statements and exactly what do i echo, the recordset?

PHP:<?php require_once('Connections/propertypages.php'); ?>
<?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;
}
}

$currentPage = $_SERVER["PHP_SELF"];

$maxRows_RSsearchforsale = 10;
$pageNum_RSsearchforsale = 0;
if (isset($_GET['pageNum_RSsearchforsale'])) {
$pageNum_RSsearchforsale = $_GET['pageNum_RSsearchforsale'];
}
$startRow_RSsearchforsale = $pageNum_RSsearchforsale * $maxRows_RSsearchforsale;

$varloc_RSsearchforsale = "mpl";
if (isset($_POST['location'])) {
$varloc_RSsearchforsale = $_POST['location'];
}
$vartype_RSsearchforsale = "vil";
if (isset($_POST['type'])) {
$vartype_RSsearchforsale = $_POST['type'];
}
$varprice_RSsearchforsale = "p9";
if (isset($_POST['price'])) {
$varprice_RSsearchforsale = $_POST['price'];
}
$varbed_RSsearchforsale = "b5";
if (isset($_POST['beds'])) {
$varbed_RSsearchforsale = $_POST['beds'];
}
mysql_select_db($database_propertypages, $propertypages);
$query_RSsearchforsale = sprintf("SELECT trueprice,`desc`, `propid`, `bathrooms`, `photo1`, locationtable.loc, typetable.style, bedtable.`number` FROM detailstable JOIN locationtable ON detailstable.location=locationtable.locid JOIN typetable ON detailstable.type=typetable.typeid JOIN pricetable ON detailstable.price=pricetable.priceid JOIN bedtable ON detailstable.beds=bedtable.bedid WHERE (location=%s AND price = %s AND type=%s AND beds=%s ) OR (price=%s AND location=%s AND type=%s) OR (price=%s AND location=%s AND beds=%s) OR (price=%s AND beds=%s AND type=%s) OR ( location=%s AND type=%s AND beds=%s) OR (price=%s AND location=%s) OR (price=%s AND type=%s) OR (price=%s AND beds=%s) OR (type=%s AND location=%s) OR (type=%s AND beds=beds) OR (location=%s AND beds=%s) OR (price = %s OR beds=%s OR type=%s OR location=%s) ORDER BY detailstable.trueprice ASC", GetSQLValueString($varloc_RSsearchforsale, "text"),GetSQLValueString($varprice_RSsearchforsale, "text"),GetSQLValueString($vartype_RSsearchforsale, "text"),GetSQLValueString($varbed_RSsearchforsale, "text"),GetSQLValueString($varprice_RSsearchforsale, "text"),GetSQLValueString($varloc_RSsearchforsale, "text"),GetSQLValueString($vartype_RSsearchforsale, "text"),GetSQLValueString($varprice_RSsearchforsale, "text"),GetSQLValueString($varloc_RSsearchforsale, "text"),GetSQLValueString($varbed_RSsearchforsale, "text"),GetSQLValueString($varprice_RSsearchforsale, "text"),GetSQLValueString($varbed_RSsearchforsale, "text"),GetSQLValueString($vartype_RSsearchforsale, "text"),GetSQLValueString($varloc_RSsearchforsale, "text"),GetSQLValueString($vartype_RSsearchforsale, "text"),GetSQLValueString($varbed_RSsearchforsale, "text"),GetSQLValueString($varprice_RSsearchforsale, "text"),GetSQLValueString($varloc_RSsearchforsale, "text"),GetSQLValueString($varprice_RSsearchforsale, "text"),GetSQLValueString($vartype_RSsearchforsale, "text"),GetSQLValueString($varprice_RSsearchforsale, "text"),GetSQLValueString($varbed_RSsearchforsale, "text"),GetSQLValueString($vartype_RSsearchforsale, "text"),GetSQLValueString($varloc_RSsearchforsale, "text"),GetSQLValueString($vartype_RSsearchforsale, "text"),GetSQLValueString($varloc_RSsearchforsale, "text"),GetSQLValueString($varbed_RSsearchforsale, "text"),GetSQLValueString($varprice_RSsearchforsale, "text"),GetSQLValueString($varbed_RSsearchforsale, "text"),GetSQLValueString($vartype_RSsearchforsale, "text"),GetSQLValueString($varloc_RSsearchforsale, "text"));
$query_limit_RSsearchforsale = sprintf("%s LIMIT %d, %d", $query_RSsearchforsale, $startRow_RSsearchforsale, $maxRows_RSsearchforsale);
$RSsearchforsale = mysql_query($query_limit_RSsearchforsale, $propertypages) or die(mysql_error());
$row_RSsearchforsale = mysql_fetch_assoc($RSsearchforsale);

if (isset($_GET['totalRows_RSsearchforsale'])) {
$totalRows_RSsearchforsale = $_GET['totalRows_RSsearchforsale'];
} else {
$all_RSsearchforsale = mysql_query($query_RSsearchforsale);
$totalRows_RSsearchforsale = mysql_num_rows($all_RSsearchforsale);
}
$totalPages_RSsearchforsale = ceil($totalRows_RSsearchforsale/$maxRows_RSsearchforsale)-1;

$queryString_RSsearchforsale = "";
if (!empty($_SERVER['QUERY_STRING'])) {
$params = explode("&", $_SERVER['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
if (stristr($param, "pageNum_RSsearchforsale") == false &&
stristr($param, "totalRows_RSsearchforsale") == false) {
array_push($newParams, $param);
}
}
if (count($newParams) != 0) {
$queryString_RSsearchforsale = "&" . htmlentities(implode("&", $newParams));
}
}
$queryString_RSsearchforsale = sprintf("&totalRows_RSsearchforsale=%d%s", $totalRows_RSsearchforsale, $queryString_RSsearchforsale);
?>------------------------------------
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional.......html bit here then rest of PHP:---------------
<div id="mainContent">
<p class="records">SEARCH RESULTS</p>
<p class="records">Found <?php echo $totalRows_RSsearchforsale ?></p>

<?php if ($totalRows_RSsearchforsale > 0) { // Show if recordset not empty ?>
<p class="Propertiesright">Properties <?php echo ($startRow_RSsearchforsale + 1) ?> to <?php echo min($startRow_RSsearchforsale + $maxRows_RSsearchforsale, $totalRows_RSsearchforsale) ?></p>
<?php } // Show if recordset not empty ?>
<p>&nbsp;</p>
<p>&nbsp;</p>
<?php do { ?>
<?php if ($totalRows_RSsearchforsale > 0) { // Show if recordset not empty ?>
<div id="salesresults">
<p><img src="PhotosResults/<?php echo $row_RSsearchforsale['photo1']; ?>" alt="Photo Property For Sale" class="imageleft" /><span class="pararightloc"><?php echo $row_RSsearchforsale['loc']; ?></span><span class="price"><?php echo $row_RSsearchforsale['trueprice']; ?> </span></p>
<div id="topborder">
<p><span class="location"><?php echo $row_RSsearchforsale['style']; ?></span><span class="lineheight"><?php echo $row_RSsearchforsale['number']; ?> Beds</span> <span class="space"><?php echo $row_RSsearchforsale['bathrooms']; ?>Baths </span></p>
</div>
<p>&nbsp;</p>
<p>Ref : <?php echo $row_RSsearchforsale['propid']; ?></p>
<p>&nbsp;</p>
<p><?php echo $row_RSsearchforsale['desc']; ?></p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p><span class="space"><a href="detailsForSale.php?propid=<?php echo $row_RSsearchforsale['propid']; ?>" class="moreinfo">More Info</a></span> </p>
<p>&nbsp;</p>
</div>
<?php } // Show if recordset not empty ?>
<?php } while ($row_RSsearchforsale = mysql_fetch_assoc($RSsearchforsale)); ?><p>&nbsp;
<p>
<?php if ($totalRows_RSsearchforsale == 0) { // Show if recordset empty ?>
</p>
<div id="noResults">
<p>There were no results found for your search. </p>
<p>Please try again with new criteria.</p>
<p>&nbsp;</p>
</div>
<?php } // Show if recordset empty ?>
<?php if ($totalRows_RSsearchforsale > 0) { // Show if recordset not empty ?>
<div id="navigate">
<span class="records">Page Navigation</span>
<table border="0" class="centralise">
<tr>
<td><?php if ($pageNum_RSsearchforsale > 0) { // Show if not first page ?>
<a href="<?php printf("%s?pageNum_RSsearchforsale=%d%s", $currentPage, 0, $queryString_RSsearchforsale); ?>">First</a>
<?php } // Show if not first page ?> </td>
<td><?php if ($pageNum_RSsearchforsale > 0) { // Show if not first page ?>
<a href="<?php printf("%s?pageNum_RSsearchforsale=%d%s", $currentPage, max(0, $pageNum_RSsearchforsale - 1), $queryString_RSsearchforsale); ?>">Previous</a>
<?php } // Show if not first page ?> </td>
<td><?php if ($pageNum_RSsearchforsale < $totalPages_RSsearchforsale) { // Show if not last page ?>
<a href="<?php printf("%s?pageNum_RSsearchforsale=%d%s", $currentPage, min($totalPages_RSsearchforsale, $pageNum_RSsearchforsale + 1), $queryString_RSsearchforsale); ?>">Next</a>
<?php } // Show if not last page ?> </td>
<td><?php if ($pageNum_RSsearchforsale < $totalPages_RSsearchforsale) { // Show if not last page ?>
<a href="<?php printf("%s?pageNum_RSsearchforsale=%d%s", $currentPage, $totalPages_RSsearchforsale, $queryString_RSsearchforsale); ?>">Last</a>
<?php } // Show if not last page ?> </td>
</tr>
</table>
</div>
<?php } // Show if recordset not empty ?>
</p>
</div>

Thank you, looking forward to your reply,

LoobyLou

edbr
10-23-2009, 01:16 AM
i ll try to look at it at the weekend i just dot have time at present. you doing great by the way and yu may even be right with the select being complex i just went a bit cross eyed when i saw it :)

LoobyLou
10-23-2009, 02:08 PM
Thanks for the encouragement and i thank you in advanced if you do get the chance to look at it this weekend i would be really grateful.

Some thoughts i have had are perhaps i should have 4 recordsets, one that does var1+var2+var3+var4, another that does (var1+var2+var3) OR (var1+var2+var4) OR (var1+var3+var4) OR (var2+var3+var4), then the 3rd recorset that takes care of the query if only 2 menus are selected and the 4th recordset to take care of the query if only 1 menu selected. But again still not sure how to word an if else statement to clarify how many variables there are to know how many menus where selected and where to put it.

Ill keep trying and look forward to hearing from you when you get the chance,

Thank you

LoobyLou