homepage Welcome to WebmasterWorld Guest from 54.198.157.6
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Drop Down List Problem
Search using Drop Down list not working
AliTaylor4411




msg:4182827
 11:36 pm on Aug 5, 2010 (gmt 0)

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!

[edited by: tedster at 12:10 am (utc) on Aug 6, 2010]
[edit reason] obscure the login credentials [/edit]

 

LifeinAsia




msg:4182837
 11:52 pm on Aug 5, 2010 (gmt 0)

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

You presume correctly- you need to add a WHERE clause to your SELECT.

I'm not a PHP person, so maybe someone with some experience can give you more specifics. But basically, you would do:
SELECT *
FROM property
WHERE column1=value1

AliTaylor4411




msg:4183181
 4:46 pm on Aug 6, 2010 (gmt 0)

I thought was something like that! If I put the where clause in as you say would I have to do an else if for each possible selection which could be made from each drop down list?

LifeinAsia




msg:4183190
 5:02 pm on Aug 6, 2010 (gmt 0)

No, "value1" is the value passed from the form. For example:
WHERE area='Newsprings'

If you want to have additional parameters, you need to use OR or AND (depending on how you want to select).

For example, if you want to show all the 2-bedroom properties in Newsprings, it would be:
WHERE area='Newsprings' AND bedrooms=2

If you want to show all properties in Newsprings (regardless of number of bedrooms) as well as all 2-bedroom properties (regardless of location), it would be:
WHERE area='Newsprings' AND bedrooms=2

(When working with databases and selecting results, think back to the Venn diagrams that you probably studied in elementary school. That will help you visualize the data sets and construct your SELECT statements.)

Again, the actual way you represent those values in PHP can be explained by someone with PHP experience.

AliTaylor4411




msg:4183197
 5:15 pm on Aug 6, 2010 (gmt 0)

OK - think I get the idea!

Thanks again for your help

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved