Beginner sql query help

[Ash33]

Im new to php and sql and need some help on an sql query. I have a mysql database that i have created for a realestate database to hold details on houses for sale. I have called the database 'properties'. Within the database is the table 'property' and colums 'id', 'type', 'title', description', 'price' and 'pool'. 'Pool' values are 'yes' or 'no' to suggest whether the property has a pool or not. Using Dreamweaver, i have succesfully created a form and can search by property 'type' and 'price' by using some dropdown lists i have created. This all works perfectly but now i want to extend the search form to find properties by 'type' and 'price' and also by 'pool'. For example, i want to search for all 'Villas' between '100,000€' to '250,000€' (this i can get working) that have a 'pool'. The pool part i can also get partially working. I have created a radio group named 'pool' within the form with 2 values: 'yes' and 'no'. I can get results returned for both 'yes' and for 'no' selections in the query but i want to also add an option for a selection of 'no preference' which will return all properties with either 'yes' and 'no' values in the database. So, how do i write an sql statement that states something like: select all from database properties where pool is 'yes' and 'no', unless 'yes' is selected, or 'no' is selected? Do i use an sql "case" statement? If so, how would i write it?

[revraz]

Well if you want result regardless if there is a pool or not, then omit the pool completely from the query.  No reason to Search on both Yes and No, since that field will contain a yes or a no anyways.

[Ash33]

I don't really want to omit the pool option for 'no preference'. Because if i only have 'yes' and 'no' options then the query only finds properties with 'pool = yes' or 'pool = no' and some users may have no preference and want to search for a property regardless of whether it has a pool or not. I understand what you mean in that omitting it would search both yes and no. The problem is I'm using dreamweaver and inserting the 3 options 'yes', 'no' & 'any' into a radio group named 'pool' and dreamweaver requires a "checked value" for the button for the 'any' selection. I have set the checked values for 'yes' as 'yes and 'no' as 'no' but don't know what to write for 'any' and it is this value that is passed to the search page to find the appropriate records. Is it simple to hand code some php that says "if 'any' is selected then find all properties with either a pool or without a pool"? Are there any written or video tutorials on this? Ive found lots of written tutorials for creating a simple database search through dynamic web pages but they only usually use/search for 1 value and not multiple options / values!

[revraz]

You need to build a dynamic query based on your search criteria, sounds like you are just using a static query.

[Ash33]

I may have to ask you to look at the code for me if possible! I spent time researching switch codes and all kinds of other stuff and tried again last night without any success. So below is the code for results page. Note that in the results page i have created sql queries for poolany, poolyes and poolno. On the search page is a radio group named 'pool' with 3 buttons with check values 'Any', 'Yes' and 'No'. So of course, the results page needs to look at the passed value 'pool' and if 'Yes' then it needs to execute the sql query for 'poolyes', if 'no' then sql for 'poolno' and if 'any' then 'poolany'. Please remember that i have got the sql for 'type' working fine. Its only the 'pool' option i cant figure out!

 

Ive no idea how to write conditional statements / switch statements or whatever is needed to do this. And i dont even know if these statements go in the search page or the results page or even where they go in the appropriate page!

 

So here is the code on the results page. If you need the code for the search page then let me know and i can post it:

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

$vartype_results = "Villa";
if (isset($_GET['type'])) {
  $vartype_results = $_GET['type'];
}
$varpool_results = "-1";
if (isset($_GET['pool'])) {
  $varpool_results = $_GET['pool'];
}
mysql_select_db($database_properties, $properties);
$query_results = sprintf("SELECT * FROM property WHERE type = %s AND pool = %s", GetSQLValueString($vartype_results, "text"),GetSQLValueString($varpool_results, "text"));
$results = mysql_query($query_results, $properties) or die(mysql_error());
$row_results = mysql_fetch_assoc($results);
$totalRows_results = mysql_num_rows($results);


mysql_select_db($database_properties, $properties);
$query_poolany = "SELECT * FROM property WHERE (pool = 'Yes') Or (pool = 'No')";
$poolany = mysql_query($query_poolany, $properties) or die(mysql_error());
$row_poolany = mysql_fetch_assoc($poolany);
$totalRows_poolany = mysql_num_rows($poolany);

mysql_select_db($database_properties, $properties);
$query_poolyes = "SELECT * FROM property WHERE pool = 'Yes'";
$poolyes = mysql_query($query_poolyes, $properties) or die(mysql_error());
$row_poolyes = mysql_fetch_assoc($poolyes);
$totalRows_poolyes = mysql_num_rows($poolyes);

mysql_select_db($database_properties, $properties);
$query_poolno = "SELECT * FROM property WHERE pool = 'No'";
$poolno = mysql_query($query_poolno, $properties) or die(mysql_error());
$row_poolno = mysql_fetch_assoc($poolno);
$totalRows_poolno = mysql_num_rows($poolno);

?>



<!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>
</head>

<body>
<?php do { ?>
  <table width="600" border="0" align="center" cellpadding="0" cellspacing="0">
    <tr>
      <td width="100" rowspan="4"><img src="<?php echo $row_results['image_loc']; ?>" /></td>
      <td align="left"><div align="left"><?php echo $row_results['type']; ?></div></td>
    </tr>
    <tr>
      <td align="left"><div align="left"><?php echo $row_results['title']; ?></div></td>
    </tr>
    <tr>
      <td align="left"><div align="left"><?php echo $row_results['price']; ?></div></td>
    </tr>
    <tr>
      <td align="left"><div align="left"><?php echo $row_results['pool']; ?></div></td>
    </tr>
    <tr>
      <td colspan="2"> </td>
    </tr>
  </table>
  <?php } while ($row_results = mysql_fetch_assoc($results)); ?></body> 
</html>
<?php
mysql_free_result($results);

mysql_free_result($poolany);

mysql_free_result($poolyes);

mysql_free_result($poolno);
?>

Many thanks. Hope your able to help! If i can get this done then my web site will be pretty much finished!

 

Thanks

EDIT: please use the code tags

[fenway]

Yikes... are you really using "SELECT *" when you just want a count?