[SOLVED] Multiple item database search

[Merdok]

Hi Guys,

 

A Client asked me to add a search function to his website to allow users to search for properties, I cannot get my head around how to write the code for it, here is the search form:

 

<form id="propSearch" name="propSearch" method="post" action="property_search.php">
    <p>Search Area</p>
  <select name="location" id="location">
    <option value="">all locations</option>
    <option value="City Centre">City Centre</option>
  </select>
  
    <p>Property Type</p>
  <select name="propertytype" id="propertytype">
    <option value="">all property types</option>
    <option value="13">Apartment</option>
    <option value="14">Duplex</option>
    <option value="17">Flat Share</option>
    <option value="16">Penthouse</option>
    <option value="18">Studio</option>
    <option value="15">Town House</option>
  </select>
  
    <p>Minimum Bedrooms</p>
  <select name="bedrooms" id="bedrooms">
    <option value="">no minimum</option>
    <option value="1">1</option>
    <option value="2">2</option>
    <option value="3">3</option>
  </select>
  
    <p>Price Range</p>
  <select name="minprice" id="minprice">
    <option value="">no preference</option>
    <option value="£300-£400 ">£300-£400 </option>
    <option value="£400-£500 ">£400-£500 </option>
    <option value="£500-£600">£500-£600</option>
    <option value="£600-£700 ">£600-£700 </option>
    <option value="£700-£800">£700-£800</option>
    <option value="£800-£1000">£800-£1000</option>
    <option value="£1000-£2000">£1000-£2000</option>
    <option value="£2000-£3000">£2000-£3000</option>
  </select>
  
  
  <input name="parking" type="checkbox" id="parking" value="parking" />
  <p class="bold inline">Parking</p>
  <input name="furnished" type="checkbox" id="furnished" value="furnished" />
  <p class="bold inline">Furnished </p>
  
  <label>
  <input class="submit_btn float_right" type="submit" name="submit" id="submit" value="Submit" />
  </label>
</form>

 

If it helps, here is the structure of the property database:

 

`propertyID` int(4) NOT NULL auto_increment,

  `propertyCode` varchar(20) NOT NULL,

  `bond` int(5) NOT NULL,

  `rent` int(5) NOT NULL,

  `rentDue` varchar(100) NOT NULL,

  `propertyName` varchar(100) NOT NULL,

  `propertyStreet1` varchar(200) NOT NULL,

  `propertyStreet2` varchar(200) NOT NULL,

  `propertyTown` varchar(100) NOT NULL,

  `propertyNumber` varchar(20) NOT NULL,

  `postCode` varchar(20) NOT NULL,

  `description` text NOT NULL,

  `features` text NOT NULL,

  `image1` varchar(200) NOT NULL,

  `image2` varchar(200) NOT NULL,

  `image3` varchar(200) NOT NULL,

  `image4` varchar(200) NOT NULL,

  `bedrooms` int(2) NOT NULL,

  `bathrooms` int(2) NOT NULL,

  `parking` tinyint(1) NOT NULL,

  `pets` tinyint(1) NOT NULL,

  `smokers` tinyint(1) NOT NULL,

  `dss` tinyint(1) NOT NULL,

  `let_agreed` tinyint(1) NOT NULL,

  `archived` tinyint(1) NOT NULL,

  `notes` text NOT NULL,

 

Appreciate the help guys.

 

Alex.

[printf]

What columns do your form inputs propertytype & location refer to in your database... And does the form input minprice refer to the column rent in your database. Answer those questions and I will give you a query to access those results...

[Merdok]

Sorry, thats an out-of-date form, here is the current version:

 

<form id="propSearch" name="propSearch" method="post" action="property_search.php">
    <p class="bold margin_bottom_5px">Search Area</p>
  <select name="location" class="width_full margin_bottom_5px" id="location">
    <option value="">all locations</option>
    <option value="Leeds">Leeds</option>
        <option value="Hull">Hull</option>
            <option value="Manchester">Manchester</option>
  </select>
  
    <p class="bold margin_bottom_5px">Minimum Bedrooms</p>
  <select name="minBedrooms" class="width_full margin_bottom_5px" id="minBedrooms">
    <option value="">no minimum</option>
    <option value="1">1</option>
    <option value="2">2</option>
    <option value="3">3</option>
  </select>
  
    <p class="bold margin_bottom_5px">Price Range</p>
  <select name="rentRange" class="width_full margin_bottom_5px" id="rentRange">
    <option value="">no preference</option>
    <option value="300-400 ">£300-£400 </option>
    <option value="400-500 ">£400-£500 </option>
    <option value="500-600">£500-£600</option>
    <option value="600-700 ">£600-£700 </option>
    <option value="700-800">£700-£800</option>
    <option value="800-1000">£800-£1000</option>
    <option value="1000-2000">£1000-£2000</option>
    <option value="2000-3000">£2000-£3000</option>
  </select>
  
  
  <input name="parking" type="checkbox" id="parking" value="1" />
  <p class="bold inline">Parking</p>
  <input name="let_agreed" type="checkbox" id="let_agreed" value="1" />
  <p class="bold inline">Include let agreed properties? </p>
  
  <label>
  <input class="submit_btn float_right" type="submit" name="submit" id="submit" value="Submit" />
  </label>
</form>

 

My apologies.

[printf]

Okay, so what column does the form input "location" refer to in your database...

[Merdok]

location refers to propertyTown

[Merdok]

bump

[Merdok]

Any help on this would be great guys, I've totally hit a roadblock here and I can't finish this project without the search function!

[Yesideez]

What code do you have so far that is using that form?

 

I mean, in property_search.php are you parsing any of the data so far or are you just looking for a MySQL query?

[Merdok]

At the moment I've got nothing. I really don't know where to start on this

[Yesideez]

OK first thing I suggest is change the values in the minprice and add a maxprice as well.

 

At the moment you're using this:

<option value="300-400 ">£300-£400 </option>

 

It'd be better to use something like this:

<option value="300">£300</option>

 

That way you are passing a "direct value" (ie. an integer) and your code doesn't have to worry about parsing the data to extract the figures. Duplicate the code for the maxprice as well just changing the relevant fields from "minprice" to "maxprice".

 

I'll be able to offer you LOADS of help later when I get home from work - time is restricted here, got a few projects to be working on.

 

The sort of MySQL query (off the top of my head) would be something like this:

 

SELECT * FROM properties WHERE location='$location' AND propertytype='$propertytype' AND bedrooms='$bedrooms' AND parking='$parking' AND minprice>'$minprice' AND maxprice<'$maxprice'

 

Not sure if I got the min and max price the right way round but the option I'd take is to build the query up depending on what data is being searched on - not everyone will search with all options (some may not care about price)

[Yesideez]

Forgot to ask - how versed are you with PHP?

 

Are you able to write the code to read the data from the form and build the query or would you like assistance in that as well?

[Merdok]

Yeah once I've pulled it from the database I know what to do with it... I'm reasonably good at PHP but I'm a web designer really so I don't have a mass of experience with it beyond writing a fairly basic CMS. I've never actually written a search function before!

[Yesideez]

Let's say you've got your data read from the form stored into variables named the same as for the form names.

 

Eg.

$location=$_POST['location'];

 

When pulling the numerical values just run intval() like this:

$minprice=intval($_POST['minprice']);

Just to be sure you get a number.

 

You could build the query with something like this:

$sql="SELECT * FROM properties WHERE 1";
if (strlen($location)>0) {$sql.=" AND propertyTown='".$location."'";}
if (strlen($propertytype)>0) {$sql.=" AND propertyType='".$propertytype."'";}

 

That would slowly build your query up containing only the data you want to search with. With regard to the min and max price just check if both are set that max is higher than min.

 

Once the query is built run a while() loop to extract the data from the database:

$query=mysql_query($sql);
while ($row=mysql_fetch_assoc($query)) {
  //display data here
}

[Yesideez]

ooo I've got another 10 minutes

 

The reason for the "WHERE 1" is so that pulls every record and without having to write code to check which (if any) search fields are being used and use them we can just add them onto the end of the query using AND.

 

Using = in MySQL will match exactly (case insensitive I believe) and if you want to search for some text inside a textual datatype (varchar, text etc.) you can use LIKE.

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

 

Eg. search for everything starting with "the"

SELECT * FROM table WHERE fieldname LIKE "the%"

 

Eg. search for everything ending with "the"

SELECT * FROM table WHERE fieldname LIKE "%the"

 

Eg. search for everything containing "the"

SELECT * FROM table WHERE fieldname LIKE "%the%"

 

Using strlen() to check if a search field has been populated means you need to have the option available to ignore that (ALL) particular field - you have done this already:

<option value="">all locations</option>

[Merdok]

That worked like a charm!

 

Thank you so much!!