PHP help..(Once more)...

  RicScott 21:28 21 Aug 2006
Locked

I promise that this is the last time I shall bother the PHP community out there..
Your help over recent issues has been great..

Ok, the issue is:
I am trying to filter the database SELECT to give only a select few results, but when I run the script, every result gets returned.
Any Ideas??


On the index page I have:

form action="view.php" method="GET" target="_self" class="style6">
fieldset>
legend class="style5">Property Search /legend>
p align="center"> span class="style7">Area /span> span class="style1"> strong>
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 €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>
/strong> /span> span class="style1">
input name="Submit" type="submit" class="style1" value="Search" />
/span> /p>
/fieldset>
/form>

**************************************************

On the view.php I have the query:
(Original Script provided by HT..Thanks..)


// mysql Query statement:
$select = "SELECT * FROM tbl $query_search ORDER BY price ASC";

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



$flag = 0;
$query_search="";
$message="You requested ";
if($type=="0")$message.= "any area ";
if($type !=="0")// if search is NOT for all a bit more needs to be added
{
if($type=="1")
{
$prop_type="Southern Costa Blanca";
$message.="Southern Costa Blanca ";
}
if($type=="2")
{
$prop_type="Costa Calida";
$message.="Costa Calida ";
}

$query_search.= " WHERE area = '$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=" > 300000";
$message.="priced between 300,000 € and Above ";
}

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($bedrooms !=="0")//same again for pattern
{
$message.="with at least $bedroomss bedrooms ";
if($flag ==1)// we already added the WHERE
{
$query_search.= " AND bedrooms >= $bedroomss ";
}else{
$query_search.= " WHERE bedrooms >= $bedroomss ";
// as this is last dont need flag
}
}

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

  harristweed 08:53 22 Aug 2006

if($price !=="0")//same again for price
{
if($price_range=="1")

should be:
if($price !=="0")//same again for price
{
if($price=="1")
$range="< 100000 ";
note the 'less than' (<) added

if($flag ==1)// we already added the WHERE
{
$query_search.= " AND bedrooms >= $bedroomss ";
should be:
if($flag ==1)// we already added the WHERE
{
$query_search.= " AND bedrooms >= $bedrooms ";
note too many ss's on bedrooms


Also
$type = $_GET["area"];
$price = $_GET["price"];
$bedrooms = $_GET["bedrooms"];
is better to use...
$type = $_GET[area];
$price = $_GET[price"];
$bedrooms = $_GET[bedrooms];

gives php a bit less work to do.

  RicScott 21:00 22 Aug 2006

I changed the code but unfortunately it's
still returning all the results..

Anything else you can think of??
Have a glance click here

Thanks for all your help with this..much appreciated..

  harristweed 21:28 22 Aug 2006

email me the whole page code and i'll check it out!

  harristweed 08:04 23 Aug 2006

Without seeing the full code it is difficult for me to see why all the records are being returned from the database instead of the ones specifically requested. To find the problem, I would do the following.

1. examine the $select variable
$result = @mysql_query ($select); // Run the query.
Echo "Select = $select" ;
You may then spot the error.

2. Remove the @
$result = @mysql_query ($select); // Run the query.
$result = mysql_query ($select); // Run the query.
That will allow mysql error messages (I don't think you will get one!)

3. Reduce the $select variable to a smaller size until it works then rebuild it, bit by bit.
Ie. $select = "SELECT * FROM tbl WHERE price <100000 ORDER BY price ASC
If that works add something ..say…
$select = "SELECT * FROM tbl WHERE price <100000 AND area =’ Southern Costa Blanca’ ORDER BY price ASC and carry ion till it all works.

This is how I debug mysql statements as mine very rarely work first time. I usually have a typo somewhere!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?