Searchbox values do not work correctly

Report · Sep 21, 2007

Hi,
I have a search box that has basic search for Property type, No of Beds, max and Min price.
But I have had problems with the search to do with Min and Max value as it does not display everything from minimum to maximum values.
I have now been asked to add Any to the search options making the SQL very complicated, but only certain options work

Please take a look:
<form action="2.php" method="get" name="form1">
<table width="124" border="1" cellpadding="2" cellspacing="2" class="style1" BORDERCOLOR="Black">
<tr>
<td width="124"><table width="122" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="122" class="style1">Property Type: </td>
</tr>
<tr>
<td><select name="PropType" id="PropType">
<option value="0">Any</option>
<option value="Houses">Houses</option>
<option value="Flats/Apartments">Flats/Apartments</option>
<option value="Bunglows">Bunglows</option>
<option value="Character Property">Character Property</option>
<option value="Commercial">Commercial</option>
<option value="Land">Land</option>
<option value="Investment Properties">Investment Properties</option>
</select></td>
</tr>
<tr>
<td class="style1">Bedrooms: </td>
</tr>
<tr>
<td><select name="NoBeds" id="NoBeds">
<option value="0">ANY</option>
<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></td>
</tr>
<tr>
<td class="style1">Min Price £: </td>
</tr>
<tr>
<td><select name="minPrice" id="minPrice">
<option value="Any">Any</option>
<option value="25.000">£25.000</option>
<option value="50.000">£50.000</option>
<option value="100.000">£100.000</option>
<option value="150.000">£150.000</option>
<option value="200.000">£200.000</option>
<option value="250.000">£250.000</option>
<option value="300.000">£300.000</option>
<option value="350.000">£350.000</option>
<option value="400.000">£400.000</option>
<option value="450.000">£450.000</option>
<option value="600.000">£600.000</option>
<option value="900.000">£900.000</option>
</select></td>
</tr>
<tr>
<td class="style1">Max Price £:</td>
</tr>
<tr>
<td><select name="maxPrice" id="maxPrice">
<option value="Any">Any</option>
<option value="25.000">£25.000</option>
<option value="50.000">£50.000</option>
<option value="100.000">£100.000</option>
<option value="150.000">£150.000</option>
<option value="200.000">£200.000</option>
<option value="250.000">£250.000</option>
<option value="300.000">£300.000</option>
<option value="350.000">£350.000</option>
<option value="400.000">£400.000</option>
<option value="450.000">£450.000</option>
<option value="600.000">£600.000</option>
<option value="900.000">£900.000</option>
</select></td>
</tr>
<tr>
<td><input name="Reset" type="reset" class="style2" value="Reset">
<input name="Submit" type="submit" class="style2" value="Submit"></td>
</tr>
</table></td>
</tr>
</table>
</form>

That is the searchbox.php page

The Price values should be eg. £200,000.00 But I have not really worked on these. I have at the moment only used values like £200.000 I suspect that is the problem with the search based on price problem

The page of display is:
$sql = "SELECT * FROM property WHERE B_R = 'BUY' AND Accepted = 'Yes' AND PropType = '$PropType' AND (NoBeds ='$NoBeds' or NoBeds >'0' or '$NoBeds' ='0') AND (Cost >='$minPrice' AND Cost <='$maxPrice') AND Country = 'UK'";
$result = mysql_query($sql);
?>
<?php
if ($myrow = mysql_fetch_array($result)) { ?>
<?php do
{
$PropType=$myrow["PropType"];
$NoBeds=$myrow["NoBeds"];
$City=$myrow["City"];
$Price=$myrow["Cost"];
$address = $myrow["Address"];
$area = $myrow["Area"];
$image = $myrow["Lrg_Image"];
$propID = $myrow["PropID"];
$ref = $myrow["Ref"];
$propBuild = $myrow["PropBuild"];
?>

<?php }
while ($myrow = mysql_fetch_array($result));
}
?>

Where am I going wrong?

Report · Sep 21, 2007

The_FedEx_Guy wrote:
> <td><select name="minPrice" id="minPrice">
> <option value="Any">Any</option>
> <option value="25.000">£25.000</option>
> <option value="50.000">£50.000</option>
>
> The Price values should be eg. ?200,000.00 But I have not really worked on
> these. I have at the moment only used values like ?200.000 I suspect that is
> the problem with the search based on price problem

Numbers stored in a database should not contain any punctuation other
than a decimal point. By using 25.000, you are specifying 25, not
25,000. You are creating unnecessary difficulties for yourself. There's
nothing wrong with displaying the figures with commas as the thousands
separator in the drop-down menu, but leave them out in the value like this:

<option value="25000">£25,000</option>
<option value="50000">£50,000</option>

> $sql = "SELECT * FROM property WHERE B_R = 'BUY' AND Accepted = 'Yes' AND
> PropType = '$PropType' AND (NoBeds ='$NoBeds' or NoBeds >'0' or '$NoBeds' ='0')
> AND (Cost >='$minPrice' AND Cost <='$maxPrice') AND Country = 'UK'";

You have a lot of variables in there. You're also doing nothing to
prevent SQL injection. Filter the user input like this to build the SQL
query.

// define an array of acceptable property types
$propTypes = array('Houses', 'Flats/Apartments', 'Bunglows',
'Character Property', 'Commercial', 'Land', 'Investment Properties');
// if submitted value is in the array of acceptable types, use it
// otherwise, use a wildcard character
if (isset($_GET['PropType']) && in_array($_GET['PropType'], $propTypes)) {
$PropType = "= '".$_GET['PropType']."'";
} else {
$PropType = 'LIKE %';
}

// check that the number of bedrooms is a number
// if it's zero, search for all numbers
if (isset($_GET['NoBeds']) && is_numeric($_GET['NoBeds']) &&
$_GET['NoBeds'] == 0) {
$NoBeds = '> 0';
} elseif (isset($_GET['NoBeds']) && is_numeric($_GET['NoBeds']) &&
$_GET['NoBeds'] > 0) {
$NoBeds = '= '.$_GET['NoBeds'];
} else {
$NoBeds = '> 0';
}

// if minPrice is not a number, set it to zero
// otherwise, use the submitted value
if (isset($_GET['minPrice']) && !is_numeric($_GET['minPrice']) ||
!isset($_GET['minPrice'])) {
$minPrice = 0;
} elseif (isset($_GET['minPrice']) && is_numeric($_GET['minPrice'])) {
$minPrice = $_GET['minPrice'];
}

// if maxPrice is not a number, set it to 100 million
// otherwise, use the submitted value
if (isset($_GET['maxPrice']) && !is_numeric($_GET['maxPrice']) ||
!isset($_GET['maxPrice'])) {
$minPrice = 100000000;
} elseif (isset($_GET['maxPrice']) && is_numeric($_GET['maxPrice'])) {
$minPrice = $_GET['maxPrice'];
}

$sql = "SELECT * FROM property WHERE B_R = 'BUY' AND Accepted = 'Yes'
AND PropType $PropType AND NoBeds $NoBeds AND Country = 'UK'
AND Cost BETWEEN $minPrice AND $maxPrice";

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Sep 21, 2007

David Powers wrote:
> if (isset($_GET['PropType']) && in_array($_GET['PropType'], $propTypes)) {
> $PropType = "= '".$_GET['PropType']."'";
> } else {
> $PropType = 'LIKE %';
> }

I missed out the quotes around the wildcard character. Change the
preceding section to this:

if (isset($_GET['PropType']) && in_array($_GET['PropType'], $propTypes)) {
$PropType = "= '".$_GET['PropType']."'";
} else {
$PropType = "LIKE '%'";
}

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Sep 21, 2007

Hi David,

When I leave the whole selection as "Any" I get this: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/unitedpr/public_html/2.php on line 178

Also I was wondering how do I format the displayed price as "£200,000.00" and or if I can enter it as a currency value in the database?

Report · Sep 21, 2007

The_FedEx_Guy wrote:
> When I leave the whole selection as "Any" I get this: Warning:
> mysql_fetch_array(): supplied argument is not a valid MySQL result resource in
> /home/unitedpr/public_html/2.php on line 178

Use echo to display the SQL query. It's possible that I have made a
mistake in the code, resulting in a failed query.

> Also I was wondering how do I format the displayed price as "?200,000.00" and
> or if I can enter it as a currency value in the database?

As I said before, all numbers must be stored in a database with no
punctuation other than the decimal point. Use the PHP function
number_format() to format the figure.

echo '£' . number_format($Cost, 2);

http://uk.php.net/manual/en/function.number-format.php

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Sep 22, 2007

Hi David,
I have changed the database prices to numbers without anything like comma's or decimal points.

Regarding the search, no properties are being shown, and I still get that same MySQL error.

I have gone through the code, and I cannot locate the problem

Here is what I have:

// define an array of acceptable property types
$propTypes = array('Houses', 'Flats/Apartments', 'Bunglows',
'Character Property', 'Commercial', 'Land', 'Investment Properties');
// if submitted value is in the array of acceptable types, use it
// otherwise, use a wildcard character
if (isset($_GET['PropType']) && in_array($_GET['PropType'], $propTypes)) {
$PropType = "= '".$_GET['PropType']."'";
} else {
$PropType = "LIKE '%'";
}

// check that the number of bedrooms is a number
// if it's zero, search for all numbers
if (isset($_GET['NoBeds']) && is_numeric($_GET['NoBeds']) &&
$_GET['NoBeds'] == 0) {
$NoBeds = '> 0';
} elseif (isset($_GET['NoBeds']) && is_numeric($_GET['NoBeds']) &&
$_GET['NoBeds'] > 0) {
$NoBeds = '= '.$_GET['NoBeds'];
} else {
$NoBeds = '> 0';
}

// if minPrice is not a number, set it to zero
// otherwise, use the submitted value
if (isset($_GET['minPrice']) && !is_numeric($_GET['minPrice']) ||
!isset($_GET['minPrice'])) {
$minPrice = 0;
} elseif (isset($_GET['minPrice']) && is_numeric($_GET['minPrice'])) {
$minPrice = $_GET['minPrice'];
}

// if maxPrice is not a number, set it to 100 million
// otherwise, use the submitted value
if (isset($_GET['maxPrice']) && !is_numeric($_GET['maxPrice']) ||
!isset($_GET['maxPrice'])) {
$minPrice = 100000000;
} elseif (isset($_GET['maxPrice']) && is_numeric($_GET['maxPrice'])) {
$minPrice = $_GET['maxPrice'];
}

$sql = "SELECT * FROM property WHERE B_R = 'BUY' AND Accepted = 'Yes'
AND PropType $PropType AND NoBeds $NoBeds AND Country = 'UK'
AND Cost BETWEEN $minPrice AND $maxPrice";

$result = mysql_query($sql);
?>

<?php
if ($myrow = mysql_fetch_array($result)) { ?>
<?php do
{
$PropType=$myrow["PropType"];
$NoBeds=$myrow["NoBeds"];
$City=$myrow["City"];
$Price=$myrow["Cost"];
$address = $myrow["Address"];
$area = $myrow["Area"];
$image = $myrow["Lrg_Image"];
$propID = $myrow["PropID"];
$ref = $myrow["Ref"];
$propBuild = $myrow["PropBuild"];
?>

<?php }
while ($myrow = mysql_fetch_array($result));
}
?> Text <?php

Report · Sep 22, 2007

The_FedEx_Guy wrote:
> Regarding the search, no properties are being shown, and I still get that same
> MySQL error.
>
> I have gone through the code, and I cannot locate the problem

Have you tried echoing the SQL to the browser, like I suggested, so you
can see how the query is built? Also use mysql_error() to find out what
the SQL problem is. Change this:

> $result = mysql_query($sql);

to this:

$result = mysql_query($sql) or die(mysql_error());

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Sep 23, 2007

On Sat, 22 Sep 2007 19:17:07 +0100, David Powers <david@example.com>
wrote:

>$result = mysql_query($sql) or die(mysql_error());

$result = mysql_query($sql) or die(mysql_error()."<br>$sql");

Gary

Report · Feb 04, 2008

Hi all,
I have been trying to implement this code since I used it last. For some reason it is not working.

It needs to only give back the searched for criteria, but it shows all records from the database.

// define an array of acceptable property types
$propTypes = array('Houses', 'Flats/Apartments', 'Bungalows','Commercial');
// if submitted value is in the array of acceptable types, use it
// otherwise, use a wildcard character
if (isset($_REQUEST['PropType']) && in_array($_REQUEST['PropType'], $propTypes)) {
$PropType = "= '".$_REQUEST['PropType']."'";
} else {
$PropType = "LIKE '%'";
}

// check that the number of bedrooms is a number
// if it's zero, search for all numbers
if (isset($_REQUEST['NoBeds']) && is_numeric($_REQUEST['NoBeds']) &&
$_REQUEST['NoBeds'] == 0) {
$NoBeds = '> 0';
} elseif (isset($_REQUEST['NoBeds']) && is_numeric($_REQUEST['NoBeds']) &&
$_REQUEST['NoBeds'] > 0) {
$NoBeds = '= '.$_REQUEST['NoBeds'];
} else {
$NoBeds = '> 0';
}

// if minPrice is not a number, set it to zero
// otherwise, use the submitted value
if (isset($_REQUEST['minPrice']) && !is_numeric($_REQUEST['minPrice']) ||
!isset($_REQUEST['minPrice'])) {
$minPrice = 0;
} elseif (isset($_REQUEST['minPrice']) && is_numeric($_REQUEST['minPrice'])) {
$minPrice = $_REQUEST['minPrice'];
}

// if maxPrice is not a number, set it to 100 million
// otherwise, use the submitted value
if (isset($_REQUEST['maxPrice']) && !is_numeric($_REQUEST['maxPrice']) ||
!isset($_REQUEST['maxPrice'])) {
$minPrice = 100000000;
} elseif (isset($_REQUEST['maxPrice']) && is_numeric($_REQUEST['maxPrice'])) {
$minPrice = $_REQUEST['maxPrice'];
}
mysql_select_db($database_db, $db);
$query_Recordset1 = "SELECT * FROM property WHERE B_R = 'BUY' AND Accepted = 'Yes' AND
City LIKE '%$q' Or Postcode LIKE '%$q' Or Area LIKE '%$q' AND
PropType $PropType AND NoBeds $NoBeds AND Country = 'UK' AND
Cost BETWEEN $minPrice AND $maxPrice ORDER BY Cost $sort";