problem with select statement filter thru GET url...

[websurfer]

www.sampelsite.com/workCategories.php?region=Florida&city=Hollywood&type=condo&button=Search%21#

In turn the mysql select statatement I have is:

SELECT *
FROM item
WHERE category = colname AND city = cityName OR type = typeProp
ORDER BY price

This is not filtering the data just right, cause I need to bring back, let's say all items under "Florida" if somebody just chooses region (and leaves "city" or "type" unselected); in other words, the filter should scale down as one selecs deeper, so if only Florida, then bring up just Florida, and if one chooses "Florida" and "Miami" for city it shouth gather as such, and so on with "type" ... what's happening now is, ifI leave city emtpy it also brings all entries since it's reading the url as "city=" and then gets bunch of records...

Appreciate any help...

[Fungus1487]

this is where you should use a normalised database with seperate tables for category, city and type etc.

but to get round redesigning ur DB. when u create ur statement check to see if the "cityName" and "type" variable length is greater than 0 and print a statement accordingly.

example:

if(isset($_GET["category"]) && isset($_GET["cityName"]) && isset($_GET["typeProp"])) {
    $cat = $_GET["category"];
    $city = $_GET["cityName"];
    $typ = $_GET["typeProp"];
    $sql = "SELECT * FROM item WHERE category = $cat ";
    if(strlen($city) > 0) {
        $sql .= " AND city = $city ";
        if(strlen($typ) > 0) {
            $sql .= " AND type = $typ ";
        }
    }
    $sql .= " ORDER BY price";
    // Perform query and fetch results
}

[websurfer]

Thanks a lot Fungus!
Gonna try this shortly...

I get what you are saying, but not sure where I am supposed to put things.. so wish me luck. Let you knwo how it goes...!

[Fungus1487]

if u get stuck just post up some of ur code where you are creating your SQL statement and ill have a look for you. hope it goes well.

[websurfer]

Hey, fungus.. well, I tried to make it work, but pretty much I am just to "green" at this php stuff... my main problem is that most of my code and recordsets are automatically created by Dreamweaver CS3, right?? that includes all repeat-regions, record-pagination and stuff.. so it get just too convoluted for me to try to patch-up...

Let me try to set a clean page (without all the other design hmtl junk) with all corresponding recordset/repeat-regiosn/record navigation, so you can have the whole page setup and I'll set up a select-menu with the diferent search options I wan it to do... and maybe you figure out how to implement it within the dreamweaver code...

I'll post it back later today, but is it OK if I email it to you as opposed to psoting here, not too sure I want to post it openly here in case I forget to "erase" any of my specific database info???

Thanks!!

[websurfer]

Hey, Fungus: see here below complete code for php page. Just a few notes on this:

The recordset in the page here right now just brings all listings into a table/record-pagination setup with no extra filtering. On the left is the "Search" form with the options I need to filter. The way it shoudl work is that the recorset shoudl automatically know if some of these search options were left blank then it shoudl bring entries accordingly. When I did this earlier it kept picking up the variable, example if city was left empty, it showed in url as: city=

Many times the way I do this is I have the form "action" pointing to another page that has a more customized-filter recordset, but no matter what I did it keept doing it wrong.

One more thing: in the "search" form, the regions (categories) and the Cities (subcategories) are linked by Javascript in a way that if one chooses "Argentina" then it automatically shows only cities for Argentina and so on. You'll see the Javascript code just before the HEAD closing tag. For the "type" search menu I created another recorset to dinamically populate the menu...

You can also see a workign url for this page at:
http://www.sitetemplates101.com/recordset-SearchTemplate.php

Thanks a lot for your help! much appreciated...

//// PAGE CODE FOLLOWS BELOW:


<?php require_once('Connections/artStore.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_listingsRS = 5;
$pageNum_listingsRS = 0;
if (isset($_GET['pageNum_listingsRS'])) {
  $pageNum_listingsRS = $_GET['pageNum_listingsRS'];
}
$startRow_listingsRS = $pageNum_listingsRS * $maxRows_listingsRS;

mysql_select_db($database_artStore, $artStore);
$query_listingsRS = "SELECT * FROM item";
$query_limit_listingsRS = sprintf("%s LIMIT %d, %d", $query_listingsRS, $startRow_listingsRS, $maxRows_listingsRS);
$listingsRS = mysql_query($query_limit_listingsRS, $artStore) or die(mysql_error());
$row_listingsRS = mysql_fetch_assoc($listingsRS);

if (isset($_GET['totalRows_listingsRS'])) {
  $totalRows_listingsRS = $_GET['totalRows_listingsRS'];
} else {
  $all_listingsRS = mysql_query($query_listingsRS);
  $totalRows_listingsRS = mysql_num_rows($all_listingsRS);
}
$totalPages_listingsRS = ceil($totalRows_listingsRS/$maxRows_listingsRS)-1;

mysql_select_db($database_artStore, $artStore);
$query_type_RS = "SELECT * FROM item_type ORDER BY type_name ASC";
$type_RS = mysql_query($query_type_RS, $artStore) or die(mysql_error());
$row_type_RS = mysql_fetch_assoc($type_RS);
$totalRows_type_RS = mysql_num_rows($type_RS);

$queryString_listingsRS = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_listingsRS") == false && 
        stristr($param, "totalRows_listingsRS") == false) {
      array_push($newParams, $param);
    }
  }
  if (count($newParams) != 0) {
    $queryString_listingsRS = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_listingsRS = sprintf("&totalRows_listingsRS=%d%s", $totalRows_listingsRS, $queryString_listingsRS);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
<style type="text/css">
<!--
p {
    font-family: Arial, Helvetica, sans-serif;
    font-size: 12px;
}
.style1 {font-family: Arial, Helvetica, sans-serif}
-->
</style>





<SCRIPT language=JavaScript>
function reload(form)
{
var val=form.cat.options[form.cat.options.selectedIndex].value;
self.location='?cat=' + val ;
}

</script>






</head>

<body>
<p align="center">&nbsp;</p>
<h1 align="center" class="style1">RECORSET PAGE TEMPLATE</h1>
<table width="1054" height="430" border="0" align="center" cellpadding="0" cellspacing="0">
  <tr>
    <td width="203" valign="top" bgcolor="#CCCCCC"><form id="form1" name="form1" method="get" action="">
      <p><strong>Search Listings:</strong></p>
      <p><?

@$cat= $_GET['cat']; // Use this line or below line if register_global is off
//@$cat=$HTTP_GET_VARS['cat']; // Use this line or above line if register_global is off

///////// Getting the data from Mysql table for first list box//////////
$quer2=mysql_query("SELECT DISTINCT category_name,category_id FROM item_category order by category_name"); 
///////////// End of query for first list box////////////

/////// for second drop down list we will check if category is selected else we will display all the subcategory///// 
if(isset($cat) and strlen($cat) > 0){
$quer=mysql_query("SELECT DISTINCT city_name FROM item_subcategory where country_code=$cat order by city_name"); 
}else{$quer=mysql_query("SELECT DISTINCT city_name FROM item_subcategory order by city_name"); } 
////////// end of query for second subcategory drop down list box ///////////////////////////

/// Add your form processing page address to action in above line. Example  action=dd-check.php////
//////////        Starting of first drop downlist /////////
echo "<br />Region/Category::<br /><select name='cat' id='cat' onchange=\"reload(this.form)\"><option value=''>Select one</option>";
while($noticia2 = mysql_fetch_array($quer2)) { 
if($noticia2['category_id']==@$cat){echo "<option selected value='$noticia2[category_name]'>$noticia2[category_name]</option>"."<BR>";}
else{echo  "<option value='$noticia2[category_id]'>$noticia2[category_name]</option>";}
}
echo "</select> <br><br>";
//////////////////  This will end the first drop down list ///////////

//////////        Starting of second drop downlist /////////
echo "<br />City:<br /><select name='subcat' id='subcat' ><option value=''>Select one</option>";
while($noticia = mysql_fetch_array($quer)) { 
echo  "<option value='$noticia[city_name]'>$noticia[city_name]</option>";
}
echo "</select>";
//////////////////  This will end the second drop down list ///////////
//// Add your other form fields as needed here/////
?><br />
        <br />
        Type:<br />
        <select name="type" id="type">
          <option value="">Choose Type</option>
          <?php
do {  
?>
          <option value="<?php echo $row_type_RS['type_name']?>"><?php echo $row_type_RS['type_name']?></option>
          <?php
} while ($row_type_RS = mysql_fetch_assoc($type_RS));
  $rows = mysql_num_rows($type_RS);
  if($rows > 0) {
      mysql_data_seek($type_RS, 0);
      $row_type_RS = mysql_fetch_assoc($type_RS);
  }
?>
                </select>
        <br />
<br />
        <br />
        <input type="submit" name="button" id="button" value="Search" />
        <br />
        </p>
    </form>
    </td>
    <td width="851" valign="top"><table width="851" height="38" border="1" align="center" cellpadding="5" cellspacing="0">
      <tr>
        <td width="58" valign="top" bgcolor="#999999"><p><strong>item_id</strong></p></td>
        <td width="121" valign="top" bgcolor="#999999"><p><strong>title</strong></p></td>
        <td width="92" valign="top" bgcolor="#999999"><p><strong>category</strong></p></td>
        <td width="97" valign="top" bgcolor="#999999"><p><strong>city</strong></p></td>
        <td width="260" valign="top" bgcolor="#999999"><p><strong>description</strong></p></td>
        <td width="86" valign="top" bgcolor="#999999"><p><strong>price</strong></p></td>
        <td width="121" valign="top" bgcolor="#999999"><p><strong>type</strong></p></td>
      </tr>
      <?php do { ?>
      <tr>
        <td valign="top"><p><?php echo $row_listingsRS['item_id']; ?></p></td>
        <td valign="top"><p><?php echo $row_listingsRS['title']; ?></p></td>
        <td valign="top"><p><?php echo $row_listingsRS['category']; ?></p></td>
        <td valign="top"><p><?php echo $row_listingsRS['city']; ?></p></td>
        <td valign="top"><p><?php echo $row_listingsRS['description']; ?></p></td>
        <td valign="top"><p><?php echo $row_listingsRS['price']; ?></p></td>
        <td valign="top"><p><?php echo $row_listingsRS['type']; ?></p></td>
      </tr>
      <?php } while ($row_listingsRS = mysql_fetch_assoc($listingsRS)); ?>
    </table>
      <br />
      <table border="0" align="center">
        <tr>
          <td width="40"><p>
              <?php if ($pageNum_listingsRS > 0) { // Show if not first page ?>
              <a href="<?php printf("%s?pageNum_listingsRS=%d%s", $currentPage, 0, $queryString_listingsRS); ?>">First</a>
              <?php } // Show if not first page ?>
          </p></td>
          <td width="40"><p>
              <?php if ($pageNum_listingsRS > 0) { // Show if not first page ?>
              <a href="<?php printf("%s?pageNum_listingsRS=%d%s", $currentPage, max(0, $pageNum_listingsRS - 1), $queryString_listingsRS); ?>">Previous</a>
              <?php } // Show if not first page ?>
          </p></td>
          <td width="40"><p>
              <?php if ($pageNum_listingsRS < $totalPages_listingsRS) { // Show if not last page ?>
              <a href="<?php printf("%s?pageNum_listingsRS=%d%s", $currentPage, min($totalPages_listingsRS, $pageNum_listingsRS + 1), $queryString_listingsRS); ?>">Next</a>
              <?php } // Show if not last page ?>
          </p></td>
          <td width="40"><p>
              <?php if ($pageNum_listingsRS < $totalPages_listingsRS) { // Show if not last page ?>
              <a href="<?php printf("%s?pageNum_listingsRS=%d%s", $currentPage, $totalPages_listingsRS, $queryString_listingsRS); ?>">Last</a>
              <?php } // Show if not last page ?>
          </p></td>
        </tr>
        <tr>
          <td colspan="4" align="center"><p>Records <?php echo ($startRow_listingsRS + 1) ?> to <?php echo min($startRow_listingsRS + $maxRows_listingsRS, $totalRows_listingsRS) ?> of <?php echo $totalRows_listingsRS ?></p></td>
        </tr>
      </table></td>
  </tr>
</table>
</body>
</html>
<?php
mysql_free_result($listingsRS);

mysql_free_result($type_RS);
?>

[websurfer]

Hey, Fungus!!

I was able to figure it out and was actually even able to adapt it within the Dreamweaver code, which allowed me to keep all repeat/page recordset navigation!

Anyways, I did use your code, but there was a slight problem with the "POST" variables. They needed to have single quotes so php didnt read them as "columns" like this:

<?php



if(isset($_GET["cat"]) && isset($_GET["subcat"]) && isset($_GET["typeProp"])) {
    $cat = $_GET["cat"];
    $city = $_GET["subcat"];
    $typ = $_GET["typeProp"];
    $sql = "SELECT * FROM item WHERE category = '$cat' ";
    if(strlen($city) > 0) {
        $sql .= " AND city = '$city' ";
        if(strlen($typ) > 0) {
            $sql .= " AND type = '$typ' ";
        }
    }
    $sql .= " ORDER BY price";
    // Perform query and fetch results
}

?>

So thanks a lot!

[Fungus1487]

great glad this worked for you. Feel free to give me some rep :D