Using an ALL statement in a form

[Jamier101]

I have been trying to use a * value to return all result from my script but it doesn't seem to carry forward correctly, any ideas?

PS. I've actually been out looking for reference books today :)

search_villa.php

<form action="villa_search.php" method="post" name="form1">
Location:
<select name="location">
<option value='Florida Keys'>Florida Keys</option> 
<option value='Atlantic Coast'>Atlantic Coast</option> 
<option value='Gulf Coast'>Gulf Coast</option> 
<option value='Orlando Disney Area'>Orlando Disney Area</option> 
</select>
<br />
Property Type:
<select name="type"> 
 <option value="*">Any</option>
<option value="Detached Villa">Detached Villa</option> 
<option value="House">House</option> 
<option value="Townhome">Townhome</option> 
<option value="Terraced House">Terraced Home</option> 
<option value="Condo">Condo</option> 
<option value="Flat">Flat</option> 
</select> 
<br />
Bedrooms:
<select name="bedrooms">
<option value ="*">Any</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8+</option> 
</select> 
<br />
Villa ID:
<input name="id" type="text" />
<input name="Submit" type="submit" value="submit"/>
</form>

The processing form is as follows:
villa_search.php

<?php 
//Turn error reporting on, this will help loads when your developing! 
error_reporting(E_ALL); 

//Catch the form being processed 
if(isset($_POST['Submit']) && ($_POST['Submit'] == "submit")){ 

$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password="password"; // Mysql password 
$db_name="test"; // Database name 
$tbl_name="villas"; // Table name 

// Connect to server and select database. 
$conn = mysql_connect($host, $username, $password) or die("cannot connect"); 
mysql_select_db($db_name, $conn)or die("cannot select DB"); 

//Assign the vars from the previous page 
//still assign the security functions to the $_POST value 
//Be aware though, that 'mysql_real_escape_string()' requires a DB connection to work 
//but because there is already a connection going at this point in the script, the function 
//will automatically pick it up without you needing to reference it in the function. 
//if it doesn't pick it up just change the comments over 

//they should both work though! 
//$name = strip_tags(mysql_real_escape_string($_POST['name'], $conn)); 
$location = strip_tags(mysql_real_escape_string($_POST['location']));
$area = strip_tags(mysql_real_escape_string($_POST['area']));
$community = strip_tags(mysql_real_escape_string($_POST['community']));
$type = strip_tags(mysql_real_escape_string($_POST['type']));
$bedrooms = strip_tags(mysql_real_escape_string($_POST['bedrooms']));
$id = $_POST['id'];

//SQL query conducts a search according to the request
$sql = "SELECT `id`, `owner`, `location`, `area`, `community`, `type`, `bedrooms` FROM `".$tbl_name."` WHERE `location` = '".$location."' && `bedrooms` = '".$bedrooms."' || `id` = '".$id."'"; 

$query = mysql_query($sql, $conn) or die("cannot connect");

//see if the query returned anything ie > 'more than' 0 rows 
if(mysql_num_rows($query) > 0){ 

//loop through the results
echo "<table border='1'>";
// keeps getting the next row until there are no more to get
while ($row = mysql_fetch_array($query)) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['id']."<br>";
echo "<tr><td>";
echo $row['owner']."<br>";
echo "<tr><td>";
echo $row['location']."<br>";
echo "<tr><td>";
echo $row['area']."<br>";
echo "<tr><td>";
echo $row['community']."<br>";
echo "<tr><td>";
echo $row['type']."<br>";
echo "<tr><td>";
echo $row['bedrooms']."<br>";
} 
//close while loop

}
else{ 
//set up the handler just in case the query returns no results! 
echo "The query returned no results";

//optional, but if there is nothing else to do with the script you can kill it 
exit; 
} 

//close the if 
} 
else{
//set the 'error handler' part of the script up 
//if these files are in the same directory, and 'name' is the form this will redirect you back there 
header("location: error.php"); 
} 
//close the else 
?> 

Everything works fine if I complete all the fields but to have any 'any' option doesn't work :(

[penders]

The problem is not the passing back of the '*' (asterisk) value to your server-side script in your form submission - this should be passed back OK. The problem appears to be the construction of your SQL statement using what you appear to think is a 'wildcard' character...

... `bedrooms` = '*' ...

The asterisk is not a wildcard character in SQL, so this will literally look for the value '*' in the bedrooms column, which I assume there are none?

If you want to search for all bedrooms (ie. you are not bothered about how many bedrooms there are) then you probably want to simply exclude the 'bedrooms' comparison from the WHERE clause. (The same for property type, although this does not appear to be part of your WHERE clause currently.)

[rocknbil]

First,

$username="root"; // Mysql username

I hope that's just an example. Never, ever, ever use a web app with root as the mysql user.

Second, you have to be cautious with or. if you do

.... where a=1 and b=2 and c=3 or id=1234

it will match on a and b and c OR c or 1234. You need to bind the and's with parentheses.

.... where (a=1 and b=2 and c=3) or id=1234

Third, if you're using double quotes with scalar $variables, you do not need to complicate by concatenation. See example below.


Instead do something like this. I'm in a bit of a rush so this probably contains errors, for example only. I'm just using these three for example only.

<select name="location">
<option value="">Select</option>
...

<select name="type">
<option value="">Any</option>
...


<select name="bedrooms">
<option value ="">Any</option>
...


Now build your where statement based on the input. If all are left at default, it will give you all records.

// By setting these null at the outset is simplifies your if's - see below. 
$where=$location=$bedrooms=$type=null; 
// 
// Always check isset and empty. 
if (isset($_POST['location']) and ! empty($_POST['location'])) { 
 $location = strip_tags(mysql_real_escape_string($_POST['location'], $conn)); 
} 
// 
if (isset($_POST['type']) and ! empty($_POST['type'])) { 
 $type = strip_tags(mysql_real_escape_string($_POST['type'], $conn)); 
} 
if (isset($_POST['bedrooms']) and ! empty($_POST['bedrooms'])) { 
 $location = strip_tags(mysql_real_escape_string($_POST['bedrooms'], $conn)); 
} 
if (isset($_POST['id']) and is_numeric($_POST['id'])) { 
 $id = $_POST['id']; 
} 
// 
if ($location) { 
 // We need to parenthesize if there's an or 
 if ($id) { $where .= ' ('; } // note SPACE 
 $where .= " `location` = '$location'"; 
} 
// We only need an AND if where has been started. 
if ($type) { 
 if ($where) { $where .= ' and'; } // again, SPACE 
 else {  
 if ($id) { $where .= ' ('; } 
 } 
 $where .= " `type` = '$type'"; 
} 
// Ditto 
if ($bedrooms) { 
 // ( not needed **if** there's only one - this is the last one, 
 // but we'll add it anyway so it matches the ) below. 
 if ($where) { $where .= ' and'; } // again, SPACE 
 else {  
 if ($id) { $where .= ' ('; } 
 } 
 $where .= " `bedrooms` = '$bedrooms '"; 
} 
// If $where and $id, obviously we started ( 
if ($where and $id) { $where .= ") or `id`='$id'"; } 
else if ($id) { $where .= " `id`='$id'"; } 
// 
$sql = "SELECT `id`, `owner`, `location`, `area`, `community`, `type`, `bedrooms` FROM `$tbl_name`"; 
// 
if ($where) { $sql .= " where $where"; } // again, SPACE 
// 
// Add an order and limit clause here. 

That will give you several variations on the select, if nothing selected . . . all records.

[Jamier101]

Thanks, I think I understand now :)

I've expanded on it a little and included a few more selection fields to complete my form, thank you.