List items from a form query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

List items from a form query

Author

Topic

TheMD
Starting Member

12 Posts

Posted - 2008-02-24 : 18:29:19

Hi,
I am a newbie, this is my first post (please go easy).

Iam at the moment trying to set up a query for someone looking for a property on an estate agents website.

From a drop down menu, the user can:
select an area (where they may like to live) from a list of areas.
select an amount of bedrooms from a list of bedrooms
select a minimum price from a list of prices
select a maximum price from a list of prices.

The query I worked out for this is as follows:
$data = mysql_query("SELECT * FROM property WHERE area like '$area' and bedrooms like '$bedrooms' AND price BETWEEN '$min_price' AND '$max_price'") or die(mysql_error());

This seems to work fine and shows all the properties that meet the criteria onto my webpage.

However, I then thought, someone may not care which area they live in and want to see all properties in all the areas, so I decided to add the option 'All areas' to my 'areas' list, I then did the same for the other lists, eg 'all bedrooms' option to my bedrooms list and so on.

I am now trying to write a query that incorporates where the 'all..' option is selected and have become very stuck!
Can someone set me off in the right direction for this.
I hope that makes sense?!?!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-24 : 20:15:57

Since you are building the sql statement from the application (rather than calling a parameterized stored procedure) can't you simply eliminate from the statement whichever criteria is "All <something>"?

Be One with the Optimizer
TG

TheMD
Starting Member

12 Posts

Posted - 2008-02-25 : 04:32:51

Hi TG thanks for the response. I am totally new to sql and php and Im not sure what you mean.
Thanks
mark

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-02-25 : 07:20:36

He means this..

lets say you want to display all areas having a the number of bedrooms and price range as selected by user,Then your query can be->

$data = mysql_query("SELECT * FROM property WHERE bedrooms like '$bedrooms' AND price BETWEEN '$min_price' AND '$max_price'") or die(mysql_error());

note: I just removed the area filter from your sql statement

Incase you want to neglect the bedroom filter,then->

$data = mysql_query("SELECT * FROM property WHERE area like '$area' AND price BETWEEN '$min_price' AND '$max_price'") or die(mysql_error());

and likewise...

You can alternatively do this as well-->

Incase you want all areas to be considered and use the same query that is-->

$data = mysql_query("SELECT * FROM property WHERE area like '$area' and bedrooms like '$bedrooms' AND price BETWEEN '$min_price' AND '$max_price'") or die(mysql_error());

just have $area passed as %

and likewise again ...

Hope am clear there.

TheMD
Starting Member

12 Posts

Posted - 2008-02-25 : 08:55:52

I think its me not being clear...

I found this website that has a similar principle to what I want in terms of function:

http://www. greaterlondonproperties .co.uk/saleproperty.php

Hope this makes it clearer?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-02-25 : 09:23:01

try this and dont leave the min_price and and max price as blank.

$data = mysql_query("SELECT * FROM property WHERE area =replace('$area','ALL','%') and bedrooms =replace('$bedrooms','ALL','%') AND price BETWEEN '$min_price' AND '$max_price'") or die(mysql_error());

TheMD
Starting Member

12 Posts

Posted - 2008-02-25 : 09:37:31

Thanks Sakets, will try when I get home (in about 3 hours). Will this work if I have options for 'no minimum' and 'no maximum' in my minimum price and maximum price columns (should the user not care how much they pay either).

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-02-25 : 09:57:25

This should take care of that -->

$data = mysql_query("SELECT * FROM property WHERE area =replace('$area','ALL','%') and bedrooms =replace('$bedrooms','ALL','%') AND price BETWEEN replace('$min_price','no minimum','0') AND replace('$max_price','no maximum',99999999999999999999999999999999999999)") or die(mysql_error());

All assuming you don't have house listed whose price is lesser than 0 or greater than 99999999999999999999999999999999999999

Let me know if this works for you and also incase you have a house listed with a price lesser than 0.I'll buy all of them :)

TheMD
Starting Member

12 Posts

Posted - 2008-02-25 : 10:42:55

lol! You'll be in the queue behind me!!

I will let yiou know how I get on.. not long till i get home!

TheMD
Starting Member

12 Posts

Posted - 2008-02-25 : 14:04:01

It didnt work :( I got no error messages. My query still works though but only if I choose an option other than "ALL" Could it be the way my form is set up?

<label>Area</label>
<select name="area" class="featureprice" id="area">
<option value="ALL">Any area</option>
<option>Clayhall</option>
<option>Hainault</option>
<option>Barkingside</option>
<option>Chigwell</option>
</select><br />

<label>Bedrooms</label>
<select name="bedrooms" class="featureprice" id="bedrooms">
<option value="ALL">Dont mind</option>
<option>1</option>
<option>2</option>
<option>3</option>
</select><br />

<label>Min price</label>
<select name="min_price" class="featureprice" id="min_price">
<option>No Minimum</option>
<option value="100000">£100,000</option>
<option value="200000">£200,000</option>
</select><br />

<label>Max price</label>
<select name="max_price" class="featureprice" id="max_price">
<option>No Maximum</option>
<option value="200000">£200,000</option>
<option value="300000">£300,000</option>
<option value="400000">£400,000</option>
</select>

megbot
Starting Member

17 Posts

Posted - 2008-02-25 : 15:07:21

you could handle it with an if/else with the php

if $_POST area= all
select statement without area

else if $_POST min_price=all
select statement with only max price

etc

else
select statement with all constraints

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-02-25 : 15:38:51

Am sorry,I messed up one part there-->

try this:

$data = mysql_query("SELECT * FROM property WHERE area like replace('$area','ALL','%') and bedrooms like replace('$bedrooms','ALL','%') AND price BETWEEN replace('$min_price','no minimum','0') AND replace('$max_price','no maximum','99999999999999999999999999999999999999')") or die(mysql_error());

TheMD
Starting Member

12 Posts

Posted - 2008-02-25 : 15:43:36

Would that be like:
IF &area = 'ALL' select * from area
else SELECT * FROM area WHERE area like '$area'
AND
IF $bedrooms = 'ALL' select * from bedrooms
else SELECT * FROM bedrooms WHERE bedrooms like '$bedrooms'

Have no idea if the above is right or what the SQL language is for dealing with the prices bit! Am I anywhere near the right path? Or should I give up and take up smoking instead?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-02-25 : 15:52:27

What you wrote there won't work.

This should:
$data = mysql_query("SELECT * FROM property WHERE area like replace('$area','ALL','%') and bedrooms like replace('$bedrooms','ALL','%') AND price BETWEEN replace('$min_price','no minimum','0') AND replace('$max_price','no maximum','99999999999999999999999999999999999999')") or die(mysql_error());

TheMD
Starting Member

12 Posts

Posted - 2008-02-25 : 15:58:04

Max and min price are sorted!

Thanks Sasket

Its just I cant do the select all bedrooms and select all areas!
I think Im close.....

TheMD
Starting Member

12 Posts

Posted - 2008-02-25 : 16:00:02

Just attempting what you just wrote. We must have been on at the same time!

TheMD
Starting Member

12 Posts

Posted - 2008-02-25 : 16:05:57

It works!!!!!!!!!!!

I just went emoticon craaaaazzzzzyyyy!
Saskets.... My many thanks! I cant believe how many test properties I did! lol.. my page goes on forever!! It all works though. Though I cant have a zillion properties on one page thats rediculous! lol
Can you tell that Im excited.. If I get any nil value properties Sasket, I will keep you in mind. Though Il probably be back here again..

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-02-25 : 16:11:03

Am saket...saskets sounds just a bit weird..never mind ! lol

TheMD
Starting Member

12 Posts

Posted - 2008-02-25 : 16:20:35

Sorry Sakets!

Thanks again.. However, I would like to understand a little bit more how your code works. Im learning SQL on my own and I dont want to have to keep asking for help. If you get a chance and dont mind ofcourse, could you explain the logic of the.. replace('$area','ALL','%') part of the statement and the.. replace('$min_price','no minimum','0')
Thanks either way.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-02-25 : 16:54:22

replace is a function ,replaces a sequence of characters in a string with another.

replace ('Who is JOhn Tall?','Tall','Galt')
would give you
'Who is John Galt?'

% is a wildcard that represents 0 or more characters

In your case,If you were to select 'all' areas from the from drop down list

You will end up having something like

area like replace('ALL','ALL','%')
Or
area like %
which basically means anything and everything

Look up for all this online if you can.Search with the keywords I mentioned.They explain in a much better way.

TheMD
Starting Member

12 Posts

Posted - 2008-02-25 : 17:17:54

:) Thanks Saket, makes some sense, Il get googling. Take care.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources