Skip to main content
T
The_FedEx_Guy
Inspiring
September 21, 2007
Question

Searchbox values do not work correctly

  • September 21, 2007
  • 2 replies
  • 643 views
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 &pound;: </td>
</tr>
<tr>
<td><select name="minPrice" id="minPrice">
<option value="Any">Any</option>
<option value="25.000">&pound;25.000</option>
<option value="50.000">&pound;50.000</option>
<option value="100.000">&pound;100.000</option>
<option value="150.000">&pound;150.000</option>
<option value="200.000">&pound;200.000</option>
<option value="250.000">&pound;250.000</option>
<option value="300.000">&pound;300.000</option>
<option value="350.000">&pound;350.000</option>
<option value="400.000">&pound;400.000</option>
<option value="450.000">&pound;450.000</option>
<option value="600.000">&pound;600.000</option>
<option value="900.000">&pound;900.000</option>
</select></td>
</tr>
<tr>
<td class="style1">Max Price &pound;:</td>
</tr>
<tr>
<td><select name="maxPrice" id="maxPrice">
<option value="Any">Any</option>
<option value="25.000">&pound;25.000</option>
<option value="50.000">&pound;50.000</option>
<option value="100.000">&pound;100.000</option>
<option value="150.000">&pound;150.000</option>
<option value="200.000">&pound;200.000</option>
<option value="250.000">&pound;250.000</option>
<option value="300.000">&pound;300.000</option>
<option value="350.000">&pound;350.000</option>
<option value="400.000">&pound;400.000</option>
<option value="450.000">&pound;450.000</option>
<option value="600.000">&pound;600.000</option>
<option value="900.000">&pound;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?
This topic has been closed for replies.

2 replies

N
Newsgroup_User
Inspiring
September 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/
N
Newsgroup_User
Inspiring
September 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 '&pound;' . 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/
N
Newsgroup_User
Inspiring
September 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
N
Newsgroup_User
Inspiring
September 21, 2007
The_FedEx_Guy wrote:
> <td><select name="minPrice" id="minPrice">
> <option value="Any">Any</option>
> <option value="25.000">&pound;25.000</option>
> <option value="50.000">&pound;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">&pound;25,000</option>
<option value="50000">&pound;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/
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices