PDA

View Full Version : Php forms & sql


Ash
11-17-2008, 12:31 PM
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 swimming 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 checked 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 a 3rd option to the radio group for a selection of 'no preference' which will return results for all properties within the database that either have 'yes' or 'no' values for 'pool' in the database i.e select all properties whether they have a pool or not. So firstly, what checked value do i give to this 3rd radio button and secondly, 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?

lux
11-18-2008, 10:01 AM
The aim of the third option is to say "hey I want all the possible values for this column".

Therefore it is better to not search/filter on this database column because there is no point in looking for all possible values because that will return the same results as not searching in the first place.

What your code needs to do is recognise a value (possibly "ALL" in the third radio button) and use PHP to look at the value and determine what to do with it. This could be done with a series of PHP if statements or a switch statement.
Then you can dynamically build your SQL statement from there.

If you're using DW's wizards to build this you may have found a sticking point and will need to consider learning to hand code some PHP. If you're already hand coding post you code and we can have a look for you.

Ash
11-18-2008, 11:42 AM
I will have a look into this. Do you know of any good writted tutorials or video tutorial websites about PHP if statements or a switch statement?

I had another reply to this question on another forum and he said:

"A very simple way of doing it would be to create two separate SQL
queries: one that doesn't include the pool choice, and one that does.
Use a conditional statement to decide which SQL query to use:"

if ($_GET['pool'] == 'any') {
// use the SQL with no pool preference
} else {
// use the SQL that states a preference
}

So im presuming this is a similar thing to what you suggested.

So any good tutorial sites would be great as im new to this and dont know how to write the sql for both of the above examples!

Thanks

lux
11-18-2008, 01:01 PM
there's are great set of video tutorials here under the tutorials and articles tab

Ash
11-19-2008, 08:36 AM
I may have to take you up on your offer to perhaps look at the code for me! I l 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 both my search page and 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!

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">&nbsp;</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

lux
11-19-2008, 09:26 AM
this is untested code, but go through it and see how I have approached the situation:


<?php


//check to see if the pool value is available via GET method
if(isset($_GET['pool'])){

$pool = $_GET['pool'];

}

//use if statement to determine which query to execute
if($pool == "Yes" || $pool == "No")
{
$query = "SELECT * FROM property WHERE pool = '$pool'";

}else{

$query = "SELECT * FROM property";

}

//query the database, and calcualte the number of rows in the result
$pool_result = mysql_query($query) or die(mysql_error());
$pool_num_rows = mysql_num_rows($pool_result);

?>

<!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">&nbsp;</td>
</tr>
</table>
<?php } while ($row_results = mysql_fetch_assoc($pool_result)); ?></body>
</html>

Ash
11-19-2008, 10:16 AM
I greatly appreciate your prompt reply and assitance with this. I will take a look and try the code on my computer at home tonight. Wish me luck!