1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 11:59 am Apr 28, 2026

Forum Moderators: coopster

Message Too Old, No Replies

Drop Down Lists

Selections made not working with Database

         

AliTaylor4411

3:58 pm on Aug 6, 2010 (gmt 0)

10+ Year Member



Another problem with my script

Basically I have five drop down lists where the user will choose

Minimum Price
Maximum Price
Bedrooms
Property Type
Area

When I make selections from the boxes and press search I just get a list of all the entries in the database and not as per the selections made.

Here is my form

<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Index Page</title>
<link href="homesltdstylesheet.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form method="post" action="1indexpageb.php">
<table width="80%" border="0" cellpadding="1" class="LOGO">
<tr class="logo_table">
<td width="14%" align="left" valign="middle"><img src="../images/home-logo.png" alt="LOGO" width="137" height="127" align="absmiddle" /> HOMES LIMITED ESTATE AGENTS</td>
</tr>
</table>
<table width="80%" border="0" cellpadding="0" class="navigationbar">
<tr class="navigation_bar">
<td width="16%">SALE</td>
<td width="16%">LET</td>
<td width="17%">VALUATION</td>
<td width="17%">CONVEYANCING</td>
<td width="16%">MORTGAGES</td>
<td width="16%"><p>CONTACT US</p></td>
</tr>
</table>
<table width="80%" border="0" cellpadding="0">
<tr>
<td colspan="5" align="left" valign="top" class="property_of_week"><p>&nbsp;</p></td>
</tr>
<tr>
<td colspan="2" align="left" valign="middle" class="property_of_week"><p>Property of the Week - 40 Pink Road, Whelley Wigan</p></td>
<td height="37" colspan="3" align="left" valign="middle" class="quicksearch_header" ><p>SEARCH PROPERTY DATABASE</p></td>
</tr>
<tr>
<td width="12%" rowspan="7" align="left" valign="top" class="key_features"><p class="key_features">&nbsp;</p>
<p class="key_features">Key Features </p>
<ul>
<li class="key_features">Detached</li>
<li class="key_features">Large Plot</li>
<li class="key_features">Double Glazed</li>
<li class="key_features">Gas Central Heating</li>
<li class="key_features">Mature Gardens</li>
<li class="key_features">Stamp Duty Paid</li>
</ul></td>
<td width="56%" rowspan="7" align="left" valign="top" class="picturebox"><img src="../images/detached1.jpg" alt="P4" width="510" height="338" align="right" class="key_features"></td>
<td height="5%" colspan="2" align="left" valign="top" class="quick_search">Minimum Price</span></td>
<td width="14%" align="left" valign="top" class="quick_search" >
<span class="quick_search">
<select name = "minprice">
<option value="no minimum">No Minimum</option>
<option value="£60,000">£60,000</option>
<option value="£100,000">£100,000</option>
<option value="£125,000">£125,000</option>
<option value="150,000">£150,000</option>
<option value="£175,000">£175,000</option>
<option value="£200,000">£200,000</option>
<option value="£225,000">£225,000</option>
<option value="£250,000">£250,000</option>
<option value="£275,000">£275,000</option>
<option value="£300,000+">£300,000+</option>
</select>
</span></td>
</tr>
<tr>
<td height="5%" colspan="2" align="left" valign="top" class="quick_search">Maximum Price</span></td>
<td align="left" valign="top" class="quick_search" >
<span class="quick_search">
<select name = "maxprice">
<option value="no maximum">No Maximum</option>
<option value="£60,000">£60,000</option>
<option value="£100,000">£100,000</option>
<option value="£125,000">£125,000</option>
<option value="150,000">£150,000</option>
<option value="£175,000">£175,000</option>
<option value="£200,000">£200,000</option>
<option value="£225,000">£225,000</option>
<option value="£250,000">£250,000</option>
<option value="£275,000">£275,000</option>
<option value="£300,000+">£300,000+</option>
</select>
</span></td>
</tr>
<tr>
<td height="5%" colspan="2" align="left" valign="top" class="quick_search">Bedrooms</span></td>
<td align="left" valign="top" class="quick_search" >
<span class="quick_search">
<select name = "bedrooms" class="quicksearch_header">
<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>
</select>
</span></td>
</tr>
<tr>
<td height="5%" colspan="2" align="left" valign="top" class="quick_search">Property Type</span></td>
<td align="left" valign="top" class="quick_search" ><span class="quick_search">
<select name = "property_type">
<option value="Apartment">Apartment</option>
<option value="Terraced">Terraced</option>
<option value="Semi-Detached">Semi-Detached</option>
<option value="Detached">Detached</option>
<option value="Bungalow">Bungalow</option>
</select>
</span></td>
</tr>
<tr>
<td height="5%" colspan="2" align="left" valign="top" class="quick_search">Area</span></td>
<td align="left" valign="top" class="quick_search" >
<span class="quick_search">
<select name = "area">
<option value="Newsprings">Newsprings</option>
<option value="Pemberton">Pemberton</option>
<option value="Whelley">Whelley</option>
<option value="Whitley">Whitley</option>
</select>
</span></td>
</tr>
<tr>
<td colspan="3" valign="top" class="block_colour_yellow"><input type="submit" value="SEARCH" />&nbsp;</td>
</tr>
<tr>
<td colspan="3" valign="top" class="block_colour_yellow">&nbsp;</td>
</tr>
<tr>
<td colspan="2" rowspan="17" align="left" valign="top"><span class="description_text">Are you looking for a family home with MORE than the X FACTOR? This could be the home for you... A stunning family home located in one of Wigan’s most prestigious locations within easy reach of Wigan town centre, Mesnes Park, Haigh Hall and all local amenities. Fully refurbished to high standards throughout with luxurious finishes in every room making this a truly elegant and stylish property. Its many features include: stunning Howarth kitchen with granite work surfaces, master suite with en-suite shower room and en-suite dressing room and high gloss porcelain marble flooring to the ground floor. Viewings are now invited to fully appreciate and we strongly recommended early viewings to avoid any disappointment.
</p>
</span>
<p class="description_text">Detached house comprising briefly of: hall, ground floor wc, boiler room, lounge, dining room, stunning kitchen, split level landing, master suite with en-suite shower room and en-suite dressing room, three additional bedrooms and principal bathroom. </p>
<p class="description_text">Access is via electric gates giving access to the spacious driveway and integral garage, providing off road parking space for multiple vehicles.</p>
<p></p></td>
<td colspan="3" valign="top" class="officeuse_header">OFFICE USE</td>
</tr>
<tr valign="top">
<td colspan="2" class="office_use">LOGIN</td>
<td class="office_use">&nbsp;</td>
</tr>
<tr valign="top">
<td colspan="2" class="office_use">PASSWORD</td>
<td class="office_use">&nbsp;</td>
</tr>
<tr valign="top">
<td height="25%" colspan="3" class="block_colour">&nbsp;</td>
</tr>
<tr valign="top">
<td colspan="3" class="office_text">OFFICES</td>
</tr>
<tr valign="top">
<td width="15%" class="office_text">1 King Street</td>
<td colspan="2" class="office_text">2 Queen Street</td>
</tr>
<tr valign="top">
<td class="office_text">Whitley</td>
<td colspan="2" class="office_text">Pemberton</td>
</tr>
<tr valign="top">
<td class="office_text">Wigan</td>
<td colspan="2" class="office_text">Wigan</td>
</tr>
<tr valign="top">
<td class="office_text">WN11NW</td>
<td colspan="2" class="office_text">WN22NW</td>
</tr>
<tr valign="top">
<td class="office_text">Tel: 01942666666</td>
<td colspan="2" class="office_text">Tel:01942555555</td>
</tr>
<tr valign="top">
<td colspan="3" class="block_colour_yellow">&nbsp;</td>
</tr>
<tr valign="top">
<td class="office_text">3 Jack Road</td>
<td colspan="2" class="office_text">4 Ace Road</td>
</tr>
<tr valign="top">
<td class="office_text">Whelley</td>
<td colspan="2" class="office_text">Standish</td>
</tr>
<tr valign="top">
<td class="office_text">Wigan</td>
<td colspan="2" class="office_text">Wigan</td>
</tr>
<tr valign="top">
<td class="office_text">WN33NW</td>
<td colspan="2" class="office_text">WN44NW</td>
</tr>
<tr valign="top">
<td class="office_text">Tel: 01942888888</td>
<td colspan="2" class="office_text">Tel: 01942999999</td>
</tr>
<tr valign="top">
<td colspan="3" class="block_colour">&nbsp;</td>
</tr>
</table>
<table width="80%" border="0" cellpadding="1" class="navigationbar">
<tr class="navigation_bar">
<td width="16%">SALE</td>
<td width="16%">LET</td>
<td width="17%">VALUATION</td>
<td width="17%">CONVEYANCING</td>
<td width="16%">MORTGAGES</td>
<td width="16%"><p>CONTACT US</p></td>
</tr>
</table>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
</body>
</html>


And here is my processessing script:

<?php
// This page retrieves data from the database table "property" and this code should be used to create the list of properties.

//Open the connection
$conn= mysql_connect("exampledb.com","exampledb", "examplepw");
if (!$conn)
{
die('Could not connect: ' . mysql_error());
}

//Select the Database
mysql_select_db("ahtaylor1db", $conn);


//Create the MySQL Command to retrieve the record
$sql = "SELECT * FROM property";


//Execute the MySQL statement and convert the result to an array
$result = mysql_query($sql, $conn);
while ($array = mysql_fetch_array($result)){
echo "Here are the results of your search"."<br>";
echo "<p>Property Address:" . $array[propertyadd1]. $array[propertyadd2]. $array[propertyadd3]. $array[postcode]. "<br>";
echo "Property Property Type" . $array[propertytype]."<br>";
echo "Property Bedrooms: " . $array[bedrooms]."<br>";
echo "Property Price: " . $array[price]."<br>";
echo "Description: " . $array[description]."</p>";
}
?>

I presume the $sql ="Select * from property"; is not correct but could anyone give me an idea on how I can get my search to work and only pull out the entries which correspond with the search selections?

HELP!

rocknbil

6:39 pm on Aug 6, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Code dump alert! :-)

Something along these lines. 


// Map the input to the fields. You aren't using field 
// names in your forms . . . ARE YOU? Don't. 
$expected_input = Array ( 
 'price' => 'pricefield', 
 'bedrooms => 'bedroomfield', 
 'type' => 'property_type' 
); 
// 
$select = "select from table"; 
$where = null; 
// 
foreach ($expected_input as $val => $fieldname) { 
 if (isset($_POST[$val]) and ! empty($_POST[$val])) { 
 // only need it if it's not the first one 
 if ($where) { $where .= ' and'; } 
 $where .= " $fieldname='$val'"; 
 } 
} 
// 
if ($where) { $select .= " where $where"; }


Pretty insecure, you need to cleanse the post variables, but that will get you started.

A side note, don't do stuff like this.

<option value="£125,000">

This means you'll be storing a decimal or numeric value as a varchar, and it will be much more difficult for sorting and most importantly a lot slower. Put your £'s an $'s and stuff like that in your programming.

AliTaylor4411

8:21 pm on Aug 6, 2010 (gmt 0)

10+ Year Member



Hi rocknbil,

Completely confused!

So I am firstly creating a new variable called $expected_input which is the result of an array which included the 5 different drop down boxes.

Is $where a new variable as well which = null? Which I presume deals with the situation where no selection is made.

Could you explain the rest of the code from foreach ($expected _input a bit more please.

Sorry for the very basic questions but I am a novice!

Thanks

rocknbil

6:04 pm on Aug 7, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Sure . . . ok my "$expected_input" array was arbitrary and only an example. Basically, 


// Map the input to the fields. You aren't using field  
// names in your forms . . . ARE YOU? Don't.  
$expected_input = Array (  
 'price' => 'pricefield',  
 'bedrooms' => 'bedroomfield',  
 'type' => 'property_type'  
);


I am making the assumption that you have a field named price in your form, and this field will query the database field named pricefield in your properties table. So you build this array associating form field => database field. This does a couple things, it allows you to eliminate any other input, increasing security *a little,* and it doesn't reveal your database table field names to the public, also increasing security a little. Best of all, it allows us to automate and easily update our select statement. Need to add a new form field and database field? We just add a new member to the array. Again, just an example, you'll need to build this yourself. 


$expected_input = Array (  
 'price' => 'pricefield',  
 'bedrooms' => 'bedroomfield',  
 'type' => 'property_type', 
 'sq_ft' => 'square_footage' 
);


So we start with this,

$select = "select * from table";

(note the correction, missed * in the original example)

and if nothing is input from the form, it will select all records, which is what you have now.

However, if the user selects something, we will add a where clause to that. We start by setting our $where variable to null.

$where = null;

The reason: we are going to concatenate (add to) this variable as needed. Concatenate is to add to a string.

$blah = 'This is';
echo $blah; // prints "This is"
$blah .= ' a sample';
echo $blah // prints "This is a sample"
$blah .= ' of concatenation';
echo $blah; // prints "This is a sample of concatenation"

If you concatenate to an undefined variable, you get an undefined variable warning, harmless but fugly. The other reason we want to preset $where to null is that we can simplify our code with

if ($where) {.....

Which returns false if it's set to null. Besides all that, it's just good practice to predefine your variables (however trivial.)

So let's build our where clause. We begin by looping through our "expected input" which follows rule 1 of 'net security (in a basic form): accept only what you expect and throw everything else away.

foreach ($expected_input as $val => $fieldname) {....

So the first one will be "price" in $val (form name "price") and "pricename" in $fieldname(database table field name.)

We see if 1) input from the form has been set (something selected/entered) and 2) if it is set, check if it's not empty. We need to check empty because text fields will still exist in $_POST/$_GET - they'll just be empty.

if (isset($_POST[$val]) and ! empty($_POST[$val])) { ....

If something is there, we can add it to our $where variable. Here's a couple examples.

... where this='that'

Note there's no "and" for the first one (duh. :-) ) But if we match on two fields, we need an "and."

... where this='that' and these='those'

So if a value has been set from the form, we add an "and" for everything but the first selection. Remember we set $where to null?

if ($where) { $where .= ' and'; }
$where .= " $fieldname='$val'";

If nothing is input, $where stays null. If something is input, it will now be set to a string. The first time through, it sets where to something, **if** there is input from the form. First time through, if price is entered,

pricefield='123000'

second time through, if property type selected,

pricefield='123000' and property_type='Residential'

.. and so on. So we finish with

if ($where) { $select .= " where $where"; }

So this can be any number of final selects:

select * from table // no input

select * from table where pricefield='123000' // price only

select * from table where pricefield='123000' and property_type='Residential'

select * from table where pricefield='123000' and property_type='Residential' and bedrooms='7' // got lots of kids huh?


The original code should do all that, just add the * I missed. It goes where your original statement is:

$sql = "SELECT * FROM property";

The only difference being, I used the variable $select out of habit, use whatever you like.

There are other things you'll need to learn, like cleansing your input, and how to add order and limit clauses with pagination . . . but get past this first.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 11:59 am Apr 28, 2026