Price Range List Search

[JakeinCLT]

Im a complete newbie!
Im building a real estate site and having trouble with the price range drop down menu. I am using a search page and a results page(Both HTML). I am using Dreamweaver's advanced recordset box. The trouble seems to be(if not my complete ignorance) every time I add a variable, the other values move, change names, or completely disappear.

This thread is what I'm trying to do:
http://www.phpbuilder.com/board/showthread.php?t=10287567&highlight=price+range
I have stared at this for 2 days with no luck. Atttached is my code.

<?php require_once('Connections/connBelle.php'); ?>
<?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;


$maxRows_belleResults = 10;
$pageNum_belleResults = 0;
if (isset($_GET['pageNum_belleResults'])) {
  $pageNum_belleResults = $_GET['pageNum_belleResults'];
}
$startRow_belleResults = $pageNum_belleResults * $maxRows_belleResults;

$colname_belleResults = "-1";
if (isset($_GET['baths'])) {
  $colname_belleResults = $_GET['baths'];
}
$varBeds_belleResults = "-1";
if (isset($_GET['beds'])) {
  $varBeds_belleResults = $_GET['beds'];
}
$varPrice_belleResults = $_GET['ListPrice']; 
switch($varPrice) { 
    case "any": 
        $range = ""; 
        break; 

case "0": 
    $range = " AND (price >= 0) AND (price <= 50000)"; 
    break; 

case "1": 
    $range = " AND (price > 50000) AND (price <= 150000)"; 
    break; 

case "2":
    $range = " AND (price > 150000) AND (price <= 200000)"; 
    break; 

case "3": 
    $range = " AND (price > 200000) AND (price <= 250000)"; 
    break; 

case "4": 
    $range = " AND (price > 300000) AND (price <= 400000)"; 
    break; 

default: 
    $range = ""; 
} 
mysql_select_db($database_connBelle, $connBelle);
$query_belleResults = sprintf("SELECT BathsTotal, Beds, ListPrice, Remarks FROM Condos WHERE BathsTotal = %s AND Beds = %s AND ListPrice %s ORDER BY ListPrice DESC", GetSQLValueString($colname_belleResults, "int"),GetSQLValueString($varBeds_belleResults, "int"),GetSQLValueString($varPrice_belleResults, "int"));
$query_limit_belleResults = sprintf("%s LIMIT %d, %d", $query_belleResults, $startRow_belleResults, $maxRows_belleResults);
$belleResults = mysql_query($query_limit_belleResults, $connBelle) or die(mysql_error());
$row_belleResults = mysql_fetch_assoc($belleResults);

if (isset($_GET['totalRows_belleResults'])) {
  $totalRows_belleResults = $_GET['totalRows_belleResults'];
} else {
  $all_belleResults = mysql_query($query_belleResults);
  $totalRows_belleResults = mysql_num_rows($all_belleResults);
}
$totalPages_belleResults = ceil($totalRows_belleResults/$maxRows_belleResults)-1;

?>

 <?php do { ?>
        <tr>
          <td><?php echo $row_belleResults['BathsTotal']; ?></td>
          <td><?php echo $row_belleResults['Beds']; ?></td>
          <td><?php echo $row_belleResults['ListPrice']; ?></td>
          <td><?php echo $row_belleResults['Remarks']; ?></td>
        </tr>
        <?php } while ($row_belleResults = mysql_fetch_assoc($belleResults)); ?>
    </table>

I apologize in advance if this does not post in the forum PHP format.
-Thanks, Jake

[spufi]

In your switch statement, do you mean to check against $varPrice_belleResults instead of $varPrice? I don't see where $varPrice is ever set.

[Derokorian]

Unless I'm missing a brace somewhere, it doesn't look like you ever close the function definition

<?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;

// SHOULD HAVE TWO (2) RIGHT BRACES HERE } I BELIEVE

$maxRows_belleResults = 10; 

[spufi]

Derokorian;10994399 wrote:

Unless I'm missing a brace somewhere, it doesn't look like you ever close the function definition

That was some of the other stuff I was confused about since it looked like a return statement was either in the middle of a function, or the function wasn't properly closed. Looking at it again, it's obvious the brackets are missing after the return statement. I don't use Dreamweaver, but I would see if there is a way for it to tell you if you have matching opening and closing brackets. I also don't see the need for checking the version of PHP.

I would rework the one part of your code to this:

if (isset($_GET['baths']))
  $colname_belleResults = $_GET['baths'];
else
  $colname_belleResults = "-1";

if (isset($_GET['beds']))
  $varBeds_belleResults = $_GET['beds'];
else
  $varBeds_belleResults = "-1";

To me it logically flows better.

[Derokorian]

Well TBH I would actually do it with ternary:

$colname_belleResults = isset($_GET['baths']) ? $_GET['baths'] : -1;
$varBeds_belleResults = isset($_GET['beds']) ? $_GET['beds'] : -1;

[JakeinCLT]

Thank you so much for the replies! I have been stuck on this since Sunday.

Spufi: I dont think I have set varPrice either, or at least dreamweaver wont let me. Every time I try to add an extra value in the recordset SQL dialog box it resets or renames my other values. Working in code view has me pretty confused. I entered the code for the price range search directly into the code view from the attached thread. Then I changed the value to varPrice. Can you help me set varPrice? The column name in my DB is 'ListPrice'.

Derokorian: I'm guilty..... I removed the two brackets in my desperation. I couldn't find their counterpart in the code. I put them back in.

I really appreciate any help you guys can offer. I feel like I'm pretty close, and with some advice I can get this to work.
-Jake

[JakeinCLT]

I reworked the code and added the brackets as suggested. I thought I would try to clear up some of the names:

• belleResults = the name of my recordset(Dreamweaver)

• varBeds = searches the column Beds in my DB, this value works, the form name is "beds"

• v****hs = searches the column BathsTotal in my DB, this value works, the form name is "baths"
  

• varPrice = is supposed to search ListPrice in my DB. the form name is "price".
  

I am using the URL GET method.

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

$maxRows_belleResults = 10;
$pageNum_belleResults = 0;
if (isset($_GET['pageNum_belleResults'])) {
  $pageNum_belleResults = $_GET['pageNum_belleResults'];
}
$startRow_belleResults = $pageNum_belleResults * $maxRows_belleResults;

$colname_belleResults = "-1";
if (isset($_GET['baths'])) {
  $colname_belleResults = $_GET['baths'];
}
$varBeds_belleResults = "-1";
if (isset($_GET['beds'])) {
  $varBeds_belleResults = $_GET['beds'];
}
$varPrice_belleResults = "1";
if (isset($_GET['price'])) {
  $varPrice_belleResults = $_GET['price'];
}
switch($varPrice) { 
    case "any": 
        $range = ""; 
        break; 

case "0": 
    $range = " AND (price >= 0) AND (price <= 50000)"; 
    break; 

case "1": 
    $range = " AND (price > 50000) AND (price <= 150000)"; 
    break; 

case "2":
    $range = " AND (price > 150000) AND (price <= 200000)"; 
    break; 

case "3": 
    $range = " AND (price > 200000) AND (price <= 250000)"; 
    break; 

case "4": 
    $range = " AND (price > 300000) AND (price <= 400000)"; 
    break; 

default: 
    $range = ""; 
} 
mysql_select_db($database_connBelle, $connBelle);
$query_belleResults = sprintf("SELECT BathsTotal, Beds, ListPrice, Remarks FROM Condos WHERE BathsTotal = %s AND Beds = %s AND ListPrice = %s ORDER BY ListPrice DESC", GetSQLValueString($colname_belleResults, "int"),GetSQLValueString($varBeds_belleResults, "int"),GetSQLValueString($varPrice_belleResults, "int"));
$belleResults = mysql_query($query_belleResults, $connBelle) or die(mysql_error());
$row_belleResults = mysql_fetch_assoc($belleResults);
$totalRows_belleResults = mysql_num_rows($belleResults);

?>

Thanks for viewing and or helping.
-Jake

[spufi]

Sub out $varPrice for $varPrice_belleResults.

<?php require_once('Connections/connBelle.php'); ?>
<?php
switch($varPrice_belleResults) { 
    case "any": 
        $range = ""; 
        break; 

case "0": 
    $range = " AND (price >= 0) AND (price <= 50000)"; 
    break; 

case "1": 
    $range = " AND (price > 50000) AND (price <= 150000)"; 
    break; 

case "2":
    $range = " AND (price > 150000) AND (price <= 200000)"; 
    break; 

case "3": 
    $range = " AND (price > 200000) AND (price <= 250000)"; 
    break; 

case "4": 
    $range = " AND (price > 300000) AND (price <= 400000)"; 
    break; 

default: 
    $range = ""; 
}
?>

[JakeinCLT]

Thanks agin Spufi.
I changed out varPrice to VarPrice_belleResults like you recommended. Now my search page is show no results. The table is still there, it just has no data in it. I feel like your original post about setting the action is the problem. The other two form fields were working(v**hs,varBeds) but I cant seem to find the code where they are set(v**hs = BathsTotal). Dreamweaver wrote most of this code, I'm trying understand it. Thanks again!
-Jake

[spufi]

JakeinCLT;10994426 wrote:

I feel like your original post about setting the action is the problem.

I'm not sure what you mean here.

[JakeinCLT]

I don't see where $varPrice is ever set.

I dont see where varPrice = the search results of my DB column ListPrice.
-Jake

[spufi]

Since you are getting empty tables, you might want to echo out some variables in places to make sure what values are actually stored in them.

IE:

echo "The Type: ".$theType."<br />;
switch ($theType) { 
etc....

[JakeinCLT]

I'm still stumped......
I removed the other search functions to help simplify the code. DWCS4 seems to write the PHP code a different way than most of the tutorials I have watched. Any help would be greatly appreciated.

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

$maxRows_belleResults = 10;
$pageNum_belleResults = 0;
if (isset($_GET['pageNum_belleResults'])) {
  $pageNum_belleResults = $_GET['pageNum_belleResults'];
}
$startRow_belleResults = $pageNum_belleResults * $maxRows_belleResults;

$colname_belleResults = "-1";
if (isset($_GET['price'])) {
  $colname_belleResults = $_GET['price'];
}
switch($colname_belleResults) { 
    case "any": 
        $range = ""; 
        break; 

case "0": 
    $range = " AND (price >= 0) AND (price <= 50000)"; 
    break; 

case "1": 
    $range = " AND (price > 50000) AND (price <= 150000)"; 
    break; 

case "2":
    $range = " AND (price > 150000) AND (price <= 200000)"; 
    break; 

case "3": 
    $range = " AND (price > 200000) AND (price <= 250000)"; 
    break; 

case "4": 
    $range = " AND (price > 300000) AND (price <= 400000)"; 
    break; 

default: 
    $range = ""; 
}

mysql_select_db($database_connBelle, $connBelle);
$query_belleResults = sprintf("SELECT ListPrice, Remarks FROM Condos WHERE ListPrice = %s ORDER BY ListPrice DESC", GetSQLValueString($colname_belleResults, "int"));
$query_limit_belleResults = sprintf("%s LIMIT %d, %d", $query_belleResults, $startRow_belleResults, $maxRows_belleResults);
$belleResults = mysql_query($query_limit_belleResults, $connBelle) or die(mysql_error());
$row_belleResults = mysql_fetch_assoc($belleResults);

if (isset($_GET['totalRows_belleResults'])) {
  $totalRows_belleResults = $_GET['totalRows_belleResults'];
} else {
  $all_belleResults = mysql_query($query_belleResults);
  $totalRows_belleResults = mysql_num_rows($all_belleResults);
}
$totalPages_belleResults = ceil($totalRows_belleResults/$maxRows_belleResults)-1;
?>

      <?php do { ?>
        <tr>
          <td><?php echo $row_belleResults['ListPrice']; ?></td>
          <td><?php echo $row_belleResults['Remarks']; ?></td>
        </tr>
        <?php } while ($row_belleResults = mysql_fetch_assoc($belleResults)); ?>