[RESOLVED] Filtering a database

[RicScott]

Hi all, this has probably been posted in the past, but after 3 days of trawling through pages for advice, I decided to post it instead.

I am trying to create a database filter for a property site, http://www.head4themed-homes.co.uk

I've got the options set:

<form action="view.php" method="GET" target="_self" class="style6">
<select name="area" class="style1">
<option value="0">Any</option>
<label for="area">
Area
</label>
<option value="1">Southern Costa Blanca</option>
<option value="2">Costa Calida</option>
</select>
<br />
<br />
</strong>Price Range (in &euro;uros) <strong>
<select name="price" class="style1">
<option value="0">Any</option>
<label for="price">

    Price Range (in €)

              </label>
              <br />
              <option value="1">Up to 100,000</option>
              <option value="2">100,000 to 200,000</option>
              <option value="3">200,000 to 300,000</option>
              <option value="4">300,000 to Above</option>
            </select>
          </strong></span></p>
            <p align="center"> <span class="style1">
            <label for="beds">Number of Bedrooms</label>
            <strong>
            <select name="bedrooms" class="style1">
              <option value="0">Any</option>
              <option value="1">1 Bedroom</option>
              <option value="2">2 Bedrooms</option>
              <option value="3">3 Bedrooms</option>
              <option value="4">4 Bedrooms</option>
              <option value="5">More Bedrooms</option>
            </select>

What I want to do now is obviously show a page with the desired results..ie: A property in Costa Calida that's priced between €100,000 and €200,000 with 3 bedrooms.. I have tried using WHERE area = $area AND price LIKE $price etc and even tried using a full breakdown of IF statements but to no avail. I have managed to get the full search working ok..Just the filter I am struggling with.
I know it works somehow...Just how?? Any suggestions??

Thanks

[kireol]

RicScott wrote:

I have tried using WHERE area = $area AND price LIKE $price etc

you were on the right track here. more or less what you need to do is....

1)get the arguments passed from the form
2)formulate a SQL statement using the arguments in a where clause assuming your "database" is a SQL database.
3)perform the query
4)get the results
5)print the results.

[RicScott]

The DB is MySQL, I'll show you what I've got for a non filtering formula,(a this will have to do until I sort it code..)

// link to DB
//The usual connect

//Beginning
$area = $GET["area"];
$price = $GET["price"];
$bedrooms = $_GET["bedrooms"];

$sql = "SELECT * FROM tblprops"; // it's this bit I'm struggling with!!
$result = @ ($sql); // Run the query.
//End

echo '<table align="center" cellspacing="0" cellpadding="3" border="1" width="100%">
';

// Fetch and print all the records.
$bg = '#ffffff'; // Set the background color.
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$bg = ($bg=='#ffffff' ? '#ffffff' : '#ffffff');
echo '<tr bgcolor="' . $bg . '" class="style1">

<td align="left" valign="middle"><img src="' . $row['image_link'] .'" width="175px" height="128px"></td>

	<td align="left" align="left" valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="0">
	<tr><td align="left" valign="top" class="style5">Property ID: <b> ' . $row['propertyID'] . '</b> :: Priced From: <b>€ ' . $row['price'] . '</b> :: Type: <b> ' . $row['type'] . '</b> :: Name: <b>' . $row['name'] . '</b><br>

Area: <b> ' . $row['area'] . '</b> :: Bedrooms: <b> ' . $row['bedrooms'] . '</b> :: Bathrooms: <b> ' . $row['bathrooms'] . '</b><br>Pool:<b> ' . $row['pool'] . '</b>:: Garden:<b> ' . $row['garden'] . '</b>:: parking:<b> ' . $row['parking'] . '</b><br></td></tr>

<tr><td align="left" valign="top" class="style1">' . $row['description'] . '
</td>
</tr></table>
';
}

echo '</table>';

mysql_free_result ($result); // Free up the resources.

mysql_close(); // Close the database connection.
?>

I have tried:

<?php

$type=$GET[type];
$price_range=$GET[price];
$beds=$_GET[beds];
//if a plot no beds
if($type=="1" and $beds > 0)
{
$beds="0";
}

// link to data
$link_id = mysql_connect("$db_host","$db_user","$db_password");
if (mysql_select_db("$db_database", $link_id));
else die ("connection failed.") ;
$flag = 0;
$query_search="";
$message="You requested ";
if($type=="0")$message.= "any type of property ";
if($type !=="0")// if search is NOT for all a bit more needs to be added
{
if($type=="1")
{
$prop_type="Plot";
$message.="a plot ";
}
if($type=="2")
{
$prop_type="Apartment";
$message.="an apartment ";
}
if($type=="3")
{
$prop_type="Villa";
$message.="a villa ";
}
$query_search.= " WHERE prop_type = '$prop_type' ";
// now set a flag so we know that a WHERE has been addeed
$flag = 1;
}

if($price !=="0")//same again for price
{
if($price_range=="1")
{
$range=" < 100000 ";
$message.="priced less than 100,000 € ";
}
if($price_range=="2")
{
$range=" between 100000 and 200000";
$message.="priced between 100,000 € and 200,000 € ";
}
if($price_range=="3")
{
$range=" between 200000 and 300000";
$message.="priced between 200,000 € and 300,000 € ";
}
if($price_range=="4")
{
$range=" between 300000 and 400000";
$message.="priced between 300,000 € and 400,000 € ";
}
if($price_range=="5")
{
$range=" between 400000 and 500000";
$message.="priced between 400,000 € and 500,000 € ";
}
if($price_range=="6")
{
$range=" between 500000 and 600000";
$message.="priced between 500,000 € and 600,000 € ";
}
if($price_range=="7")
{
$range=" between 600000 and 700000";
$message.="priced between 600,000 € and 700,000 € ";
}
if($price_range=="8")
{
$range=" between 700000 and 800000";
$message.="priced between 700,000 € and 800,000 € ";
}
if($price_range=="9")
{
$range=" between 800000 and 900000";
$message.="priced between 800,000 € and 900,000 € ";
}
if($price_range=="10")
{
$range=" between 900000 and 1000000";
$message.="priced between 900,000 € and 1,000,000 € ";
}
if($price_range=="11")
{
$range=" > 1000000";
$message.="priced over 1,000,000 € ";
}
if($flag ==1)// we already added the WHERE
{
$query_search.= " AND price $range ";
}else{
// if this bit is actioned we need to add WHERE and set the flag
$query_search.= " WHERE price $range ";
$flag = 1;
}
}
if($beds !=="0")//same again for pattern
{
$message.="with at least $beds bedrooms ";
if($flag ==1)// we already added the WHERE
{
$query_search.= " AND beds >= $beds ";
}else{
$query_search.= " WHERE beds >= $beds ";
// as this is last dont need flag
}
}

(I did change it slightly to suit the DB I am using.

[Rodney_H_]

/ off topic /

RicScott:

Please do us all a favor and post your code in the [ PHP ] bb-code tags for correct syntax highlighting. It makes it MUCH easier to read.

Thanks.

[RicScott]

// link to DB
//The usual connect

//Beginning
$area = $_GET["area"];
$price = $_GET["price"];
$bedrooms = $_GET["bedrooms"];

$sql = "SELECT * FROM tblprops"; // it's this bit I'm struggling with!!
$result = @mysql_query ($sql); // Run the query.
//End

echo '<table align="center" cellspacing="0" cellpadding="3" border="1" width="100%">
';

// Fetch and print all the records.
$bg = '#ffffff'; // Set the background color.
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$bg = ($bg=='#ffffff' ? '#ffffff' : '#ffffff');
echo '<tr bgcolor="' . $bg . '" class="style1">

<td align="left" valign="middle"><img src="' . $row['image_link'] .'" width="175px" height="128px"></td>

<td align="left" align="left" valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr><td align="left" valign="top" class="style5">Property ID: <b> ' . $row['propertyID'] . '</b> :: Priced From: <b>€ ' . $row['price'] . '</b> :: Type: <b> ' . $row['type'] . '</b> :: Name: <b>' . $row['name'] . '</b><br>
Area: <b> ' . $row['area'] . '</b> :: Bedrooms: <b> ' . $row['bedrooms'] . '</b> :: Bathrooms: <b> ' . $row['bathrooms'] . '</b><br>Pool:<b> ' . $row['pool'] . '</b>:: Garden:<b> ' . $row['garden'] . '</b>:: parking:<b> ' . $row['parking'] . '</b><br></td></tr>

<tr><td align="left" valign="top" class="style1">' . $row['description'] . '
</td>
</tr></table>
';
}

echo '</table>';

mysql_free_result ($result); // Free up the resources.

mysql_close(); // Close the database connection.
?>


I have tried:

<?php

$type=$_GET[type];
$price_range=$_GET[price];
$beds=$_GET[beds];
//if a plot no beds
if($type=="1" and $beds > 0)
{
$beds="0";
}

// link to data
$link_id = mysql_connect("$db_host","$db_user","$db_password");
if (mysql_select_db("$db_database", $link_id));
else die ("connection failed.") ;
$flag = 0;
$query_search="";
$message="You requested ";
if($type=="0")$message.= "any type of property ";
if($type !=="0")// if search is NOT for all a bit more needs to be added
{
if($type=="1")
{
$prop_type="Plot";
$message.="a plot ";
}
if($type=="2")
{
$prop_type="Apartment";
$message.="an apartment ";
}
if($type=="3")
{
$prop_type="Villa";
$message.="a villa ";
}
$query_search.= " WHERE prop_type = '$prop_type' ";
// now set a flag so we know that a WHERE has been addeed
$flag = 1;
}

if($price !=="0")//same again for price
{
if($price_range=="1")
{
$range=" < 100000 ";
$message.="priced less than 100,000 € ";
}
if($price_range=="2")
{
$range=" between 100000 and 200000";
$message.="priced between 100,000 € and 200,000 € ";
}
if($price_range=="3")
{
$range=" between 200000 and 300000";
$message.="priced between 200,000 € and 300,000 € ";
}
if($price_range=="4")
{
$range=" between 300000 and 400000";
$message.="priced between 300,000 € and 400,000 € ";
}
if($price_range=="5")
{
$range=" between 400000 and 500000";
$message.="priced between 400,000 € and 500,000 € ";
}
if($price_range=="6")
{
$range=" between 500000 and 600000";
$message.="priced between 500,000 € and 600,000 € ";
}
if($price_range=="7")
{
$range=" between 600000 and 700000";
$message.="priced between 600,000 € and 700,000 € ";
}
if($price_range=="8")
{
$range=" between 700000 and 800000";
$message.="priced between 700,000 € and 800,000 € ";
}
if($price_range=="9")
{
$range=" between 800000 and 900000";
$message.="priced between 800,000 € and 900,000 € ";
}
if($price_range=="10")
{
$range=" between 900000 and 1000000";
$message.="priced between 900,000 € and 1,000,000 € ";
}
if($price_range=="11")
{
$range=" > 1000000";
$message.="priced over 1,000,000 € ";
}
if($flag ==1)// we already added the WHERE
{
$query_search.= " AND price $range ";
}else{
// if this bit is actioned we need to add WHERE and set the flag
$query_search.= " WHERE price $range ";
$flag = 1;
}
}
if($beds !=="0")//same again for pattern
{
$message.="with at least $beds bedrooms ";
if($flag ==1)// we already added the WHERE
{
$query_search.= " AND beds >= $beds ";
}else{
$query_search.= " WHERE beds >= $beds ";
// as this is last dont need flag
}
}

[kireol]

wihout reading over your entire code and just focussing on a few lines, here's where you should start. I dont know what your data looks like but here.

//Beginning
$area = $_GET["area"];
$price = $_GET["price"];
$bedrooms = $_GET["bedrooms"];

$sql = "SELECT * FROM tblprops where area = '$area' and price < '$price' and numberofbedrooms <= '$bedrooms'"; // changing this line will do what you want.  hard to say without seeing the data though
$result = @mysql_query ($sql); // Run the query. 

edit I left 3 different types in there for you. 1 where it's must be exactly = to. 1 where it must be greater than, and 1 where it's less than or equal to.

[Rodney_H_]

(Thanks)

Um, before you assign the GET values to variables, I would make sure that they are set, or PHP will throw out a notice. Look here: http://www.php.net/isset

Make sure you do not pass values supplied by a user directly into an SQL statement, but escape those strings so the query won't fail. (Especially important when the data is being INSERTED into the db.)

Also, your mysql query statement is going to become dynamic based on which of the values have been passed through the query string, and whose GET value is-set.

You will run some conditional statements something LIKE this:

<?php
// mysql Query statement:
$sql = "SELECT * FROM tblprops "; 

if(isset($_GET["area"]) || isset($_GET["price"]) || isset($_GET["bedrooms"])) 
{ 
     $sql.= " WHERE ";

if(isset($_GET["area"]))
{   
    $sql.= " `area` <= '".mysql_escape_string($_GET["area"])."' "; 

	if(isset($_GET["price"]))
	{
		$sql.= " AND  `price` <= '".mysql_escape_string($_GET["area"])."' "; 
	}
	if(isset($_GET["bedrooms"]))
	{
		$sql.= " AND  `bedrooms` <= '".mysql_escape_string($_GET["bedrooms"])."' "; 
	}		
}
elseif(isset($_GET["price"]))
{
	$sql.= "  `price` <= '".$_GET["price"]."' ";

	if(isset($_GET["bedrooms"]))
	{
		$sql.= " AND `bedrooms` <= '".mysql_escape_string($_GET["bedrooms"])."' ";
	}
}
else
{
	$sql.= " `bedrooms` <= '".mysql_escape_string($_GET["bedrooms"])."' ";
}
}

?>

EDIT: Obviously you will have to do a little bit of tweaking cos I did this off the top of my head, but it shows you how you can build a dynamic query based on which values the customer is interested in and has selected on the form...

[RicScott]

Thanks Rodney, but when I query the filter it displays all the database on one page...
When I use kireol solution, I get errors relating to the query.

Is there anything else I could try?? Is there something I have wrong on the html form perhaps??

Please find below all code in it's correct format...

Html form:

 <form action="view.php" method="GET" target="_self" class="style6">
                <select name="area" class="style1">
                  <option value="0">Any</option>
                  <label for="area">
                   Area 
		</label>
                  <option value="1">Southern Costa Blanca</option>
                  <option value="2">Costa Calida</option>
                </select>
                <br />
                <br />
                </strong>Price Range (in &euro;uros) <strong>
                <select name="price" class="style1">
                  <option value="0">Any</option>
                  <label for="price">

    Price Range (in €)

              </label>
              <br />
              <option value="1">Up to 100,000</option>
              <option value="2">100,000 to 200,000</option>
              <option value="3">200,000 to 300,000</option>
              <option value="4">300,000 to Above</option>
            </select>
          </strong></span></p>
            <p align="center"> <span class="style1">
            <label for="beds">Number of Bedrooms</label>
            <strong>
            <select name="bedrooms" class="style1">
              <option value="0">Any</option>
              <option value="1">1 Bedroom</option>
              <option value="2">2 Bedrooms</option>
              <option value="3">3 Bedrooms</option>
              <option value="4">4 Bedrooms</option>
              <option value="5">More Bedrooms</option>
            </select>

On the view.php there is an include file call view_inc.php which reads:

<?php


// link to DB
require("inc/conn.php");

//Beginning
$area = $_GET["area"];
$price = $_GET["price"];
$bedrooms = $_GET["bedrooms"]; 
// mysql Query statement:
$sql = "SELECT * FROM tblprops";
$result = @mysql_query ($sql); // Run the query.

if(isset($_GET["area"]) || isset($_GET["price"]) || isset($_GET["bedrooms"]))
{
     $sql.= " WHERE ";

if(isset($_GET["area"]))
{   
    $sql.= " `area` = '".mysql_escape_string($_GET['area'])."' ";

    if(isset($_GET["price"]))
    {
        $sql.= " AND  `price` LIKE '".mysql_escape_string($_GET['price'])."' ";
    }
    if(isset($_GET["bedrooms"]))
    {
        $sql.= " AND  `bedrooms` = '".mysql_escape_string($_GET['bedrooms'])."' ";
    }        
}
elseif(isset($_GET['price']))
{
    $sql.= "  `price` <= '".$_GET['price']."' ";

    if(isset($_GET['bedrooms']))
    {
        $sql.= " AND `bedrooms` <= '".mysql_escape_string($_GET['bedrooms'])."' ";
    }
}
else
{
    $sql.= " `bedrooms` <= '".mysql_escape_string($_GET['bedrooms'])."' ";
}
} 

//End

echo '<table align="center" cellspacing="0" cellpadding="3" border="1" width="100%">
';

// Fetch and print all the records.
$bg = '#ffffff'; // Set the background color.
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
	$bg = ($bg=='#ffffff' ? '#ffffff' : '#ffffff'); 
	echo '<tr bgcolor="' . $bg . '" class="style1">

<td align="left" valign="middle"><img src="' . $row['image_link'] .'" width="175px" height="128px"></td>

	<td align="left" align="left" valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="0">
	<tr><td align="left" valign="top" class="style5">Property ID: <b> ' . $row['propertyID'] . '</b> :: Priced From: <b>€ ' . $row['price'] . '</b> :: Type: <b> ' . $row['type'] . '</b> :: Name: <b>' . $row['name'] . '</b><br>
Area: <b> ' . $row['area'] . '</b> :: Bedrooms: <b> ' . $row['bedrooms'] . '</b> :: Bathrooms: <b> ' . $row['bathrooms'] . '</b><br>Pool:<b> ' . $row['pool'] . '</b>:: Garden:<b> ' . $row['garden'] . '</b>:: parking:<b> ' . $row['parking'] . '</b><br></td></tr>

<tr><td align="left" valign="top" class="style1">' . $row['description'] . '
</td>
	</tr></table>
	';
}

echo '</table>';

mysql_free_result ($result); // Free up the resources.	

mysql_close(); // Close the database connection.
?>

No matter what I try I seem to be hitting brick wall.. As you can see from the http://www.head4themed-homes.co.uk/ , if you click on View all properties, that's what the result sohuld look like, only filtered. I have managed to include a 'sort' filter to the view all page but it's just this one that's proving a little tricky.
Many thanks for your help so far..

[RicScott]

I have also uploaded the above script to the site, so if you use the filter search, you will see what I mean...

[Rodney_H_]

You are running the query of selecting EVERYTHING in your database BEFORE you every filter...

Move THIS line:

$result = @mysql_query ($sql); // Run the query.

to AFTER the filter/conditionals!

Otherwise you are running the query before adding anything to the WHERE clause, right??!!

[RicScott]

No worries Rodney...will try it out...Many thanks for your help..