Jump to content

Web Design help


Recommended Posts

One for the it boffins! cough Ant (help please!)

 

Im creating an estate agents website as part of my uni course and it obviously needs a search feature.

 

Im using HTML and have a small SQL database,

 

Basically I have 5 or 6 locations and they work fine from the drop down menu, however i want an option that will return all locations.

 

For example i have

 

<select name = "city" id = "city">

<option value="Newcastle">Newcastle City Centre</option>

<option value="Gosforth">Gosforth</option>

<option value="Jesmond">Jesmond</option>

<option value="Gateshead">Gateshead</option>

<option value="Heaton">Heaton</option>

<option value=" ">All Locations </option>

</select>

 

Is there a way you can select all the options? as no matter what i try i cant seem to get it to work?

 

 

My SQL statement is this

 

$type= $_REQUEST ["type"];

$city= $_REQUEST ["city"];

$nobedrooms= $_REQUEST ["nobedrooms"];

$price= $_REQUEST ["price"];

$typeselect=("SELECT * FROM property WHERE type LIKE'$type' AND city LIKE'$city' AND nobedrooms ='$nobedrooms'");

//

execute sql statement

$rstype

= mysql_query($typeselect);

//

construct sql statement

Thanks in advance

Edited by JaMoUsE
Link to post
Share on other sites

naughty JaMouse! strongly typed stored procedures if you please (you can use this as a "security hardening factor to protect against sql injection then, be something to add in why you used certain methods)

 

in your sqldb have a "locations" table with the name and an int/id

 

populate your dropdown using the table as your datasource rather than hardcoding the values, have the vcrLocationName as the text and the int/id as the value

 

that way you can add more locations just by updating the table rather than the webpage and having to redeploy etc

have

 

ID - vcrLocation

0 - Search All

1 - gateshead

etcetc

 

that way when you run your search you're going to write you can pick up on the data and do a check for 0 to search everything or use the other values like 1 for whatever to filter it, which will in turn be quicker, could add indexes and whatnot as well then.

you could also use these ID's as a foreignkey then if you wanted to tie them into a more specific address table (bit like picking a county)

 

so you'd have say customers table

CustomerID - vcrforename-vcrsurname-dteDateCreated-intAddressID(FK)

 

Address table

intAddressID(PK), AddressLine1,2,3,Postcode,Region(fk)

region being the table i mentioned first.

 

as for how to do all the options you've loads of ways to do it a few for example

#1 wildcard search - where city like '%' when you detect a certain flag(do not do anything like this using your method above, only do it in the stored-proc if you have to)

#2 iterate through all the available locations and build up the array to bring back

#3 when you do your search/if it's a search by all simply don't have the "city" as part of your where clause for that version of the search)

 

Even for a smallDB you should always be thinking about scale-ability and how it might grow even if it's unlikely, because it's a lot easier to deal with something when you've planned for it rather than try fudge/hack it in later. (again something else you can stick down why you've done things a certain way/an answer for any "what if" questions that come up)

 

edit i'm going stop now before i get carried away and do your project for you and the fact i've been in work all day doing my head in databases and other shite ;)

(btw your problem above is because your doing where city like ' ' effectively so it's going where city is like a blank space, rather than city like '%' which is the wildcard symbol)

Link to post
Share on other sites

Like so...

 

$type= $_REQUEST ["type"];

$city= $_REQUEST ["city"];

$nobedrooms= $_REQUEST ["nobedrooms"];

$price= $_REQUEST ["price"];

 

if($city == ' ' ){$where = " AND city LIKE '$city' ";}else{$where ='';}

$typeselect=("SELECT * FROM property WHERE type LIKE'$type' $where AND nobedrooms ='$nobedrooms'");

 

Edited by Ruler of Planet Houston
Link to post
Share on other sites

Like so...

 

$type= $_REQUEST ["type"];

$city= $_REQUEST ["city"];

$nobedrooms= $_REQUEST ["nobedrooms"];

$price= $_REQUEST ["price"];

 

if($city == ' ' ){$where = " AND city LIKE '$city' ";}else{$where ='';}

$typeselect=("SELECT * FROM property WHERE type LIKE'$type' $where AND nobedrooms ='$nobedrooms'");

 

 

This code does return all locations but even if I select a specific location it still returns all locations

Edited by JaMoUsE
Link to post
Share on other sites

got it backwards by the looks of it, the way thats set at the min, Switch the two $where lines around

 

if($city == ' ' )
{
 --If your city is "blank/search all then then you don't want to use the city for the where
$where ='';  
}
else
{
--if city has value, then you do want to use it for the where
$where = " AND city LIKE '$city' ";
}

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...