Page 1 of 1

Search code giving funny results

Posted: Fri Apr 11, 2008 2:32 pm
by khushbush
~pickle | Please use [ code=html ], [ code=php ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.


I'm creating a search functionality where users can select the type of property they want to buy, the price range, the number of bedrooms and the area in which they want to search for a house.

My code seems to work as it shows up the desired results. However, it also shows ALL the properties stored in the database, which is not something I want it to do.

I am including the code for the search function below:

Code: Select all

 
<?
$proptype = $_POST['propertyType'];
$postcode = $_POST['postcode'];
$max = $_POST['propertyPriceMax'];
$min = $_POST['propertyPriceMin'];
$rooms = $_POST['propertyBedrooms'];
 
if(!$proptype == "House" && !$proptype == "Flat" && !$proptype == "Bungalow" && $rooms){
$query = mysql_query("SELECT * FROM property WHERE propertyBedrooms = '$rooms'");
 
if (mysql_num_rows($query) > 0){
while ($row = mysql_fetch_array($query, MYSQL_ASSOC))
{
?>
<tr align = "LEFT">
<td>
<BR>
<BR>
<font face="Arial" color="#0B3A62">
<?
// Echo them out
   echo "<a href=\"searchprocess.php?id=$row[propertyID]\">$row[propertyType]". ", £". $row[propertyPrice] ." in " . $row[propertyArea];
   }
   }
   else{?>
<font face="Arial" color="#0B3A62">
<?
 echo "There are no records returned for your search query.";  }
   }
   
   ?>
</a>
</font>
</BR>
</BR>
</td>
</tr> 
 
<?
if(!$proptype == "House" && !$proptype == "Flat" && !$proptype == "Bungalow" && $max ){
$query = mysql_query("SELECT * FROM property WHERE propertyPrice<'$max'");
 
if (mysql_num_rows($query) > 0){
while ($row = mysql_fetch_array($query, MYSQL_ASSOC))
{
?>
<tr align = "LEFT">
<td>
<BR>
<BR>
<font face="Arial" color="#0B3A62">
<?
// Echo them out
   echo "<a href=\"searchprocess.php?id=$row[propertyID]\">$row[propertyType]". ", £". $row[propertyPrice] ." in " . $row[propertyArea];
   }
   }
   else{?>
<font face="Arial" color="#0B3A62">
<?
 echo "There are no records returned for your search query.";  }
   }
   
   ?>
</a>
</font>
</BR>
</BR>
</td>
</tr> 
 
<?
if(!$proptype == "House" && !$proptype == "Flat" && !$proptype == "Bungalow" && $min){
$query = mysql_query("SELECT * FROM property WHERE propertyPrice>'$min'");
 
if (mysql_num_rows($query) > 0){
while ($row = mysql_fetch_array($query, MYSQL_ASSOC))
{
?>
<tr align = "LEFT">
<td>
<BR>
<BR>
<font face="Arial" color="#0B3A62">
<?
// Echo them out
   echo "<a href=\"searchprocess.php?id=$row[propertyID]\">$row[propertyType]". ", £". $row[propertyPrice] ." in " . $row[propertyArea];
   }
   }
   else{?>
<font face="Arial" color="#0B3A62">
<?
 echo "There are no records returned for your search query.";  }
   }
   
   ?>
</a>
</font>
</BR>
</BR>
</td>
</tr> 
 
What the code is doing is that it first displays all the properties within the database and then it displays the real search results. It also displays the 'no records returned' error several times on the page. So where does the problem lie?


~pickle | Please use [ code=html ], [ code=php ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.

Re: Search code giving funny results

Posted: Fri Apr 11, 2008 3:41 pm
by onion2k
You're querying the database 3 times. So it's displaying 3 things. If you want to display one thing, query the database once.

Re: Search code giving funny results

Posted: Fri Apr 11, 2008 3:50 pm
by khushbush
I'm querying the database for different results according to the different information entered into the search form...hence there are three different queries.

Re: Search code giving funny results

Posted: Sun Apr 13, 2008 9:37 am
by khushbush
So can anyone find a way around the repeated results? I need filtered results according to the conditions entered in the if statement without the results coming out repeated...

Re: Search code giving funny results

Posted: Sun Apr 13, 2008 2:31 pm
by Mordred
It looks like you need to think this through, by doing several queries you're OR-ing the search parameters, while it sounds like you need AND-ing them (i.e. use a single query as onion2k adviced you).

Also there's SQL injection all over the place.

Re: Search code giving funny results

Posted: Sun Apr 20, 2008 7:13 am
by khushbush
Back to this problem again.

I've tried a different approach, by separating my queries and then using include in the results page, but that doesn't seem to work...for obvious reasons, as I have now realised.
I can't redirect the page as it will give me header errors and I really cannot change the headers. Sooo...any other ideas?

I'm posting my code:

Code: Select all

 
<?
$proptype = addslashes($_POST['propertyType']);
$postcode = addslashes($_POST['postcode']);
$max = addslashes($_POST['propertyPriceMax']);
$min = addslashes($_POST['propertyPriceMin']);
$rooms = addslashes($_POST['propertyBedrooms']);
 
if(!$proptype == "House" && !$proptype == "Flat" && !$proptype == "Bungalow" && $rooms){
include "advsearch1.php";}
?>
<?
if(!$proptype == "House" && !$proptype == "Flat" && !$proptype == "Bungalow" && $max ){
include "advsearch2.php";
}?>
 
<?
if(!$proptype == "House" && !$proptype == "Flat" && !$proptype == "Bungalow" && $min){
include "advsearch3.php";
}?>
 
<?
if(!$proptype == "House" && !$proptype == "Flat" && !$proptype == "Bungalow" && $postcode){
include "advsearch4.php";
}?>
 
Code from advsearch1.php (code from advsearch 2,3 and 4 are pretty similar):

Code: Select all

 
$query = mysql_query("SELECT * FROM property WHERE propertyBedrooms = '$rooms'");
 
while ($row = mysql_fetch_array($query, MYSQL_ASSOC))
{
?>
<tr align = "LEFT">
<td>
<BR>
<BR>
<font face="Arial" color="#0B3A62">
 
     <tr><td><? echo "<a href=\"searchprocess.php?id=$row[propertyID]\">" . " £". $row[propertyPrice] ." in " . $row[propertyArea];?></td></tr>
   <br>
   <br>
   <tr><tr><td><?echo "<img src =\"showimage.php?id=$row[propertyID]\">";?></td></tr></tr>
   </br></br>
   <? }
 
   
   ?>
 
Thanks. :)

Re: Search code giving funny results

Posted: Sun Apr 20, 2008 9:15 am
by aceconcepts
I think what you should do is determine what search criteria has been specified and then query according to the criteria.

Your query seems too long and drawn out.

Re: Search code giving funny results

Posted: Sun Apr 20, 2008 3:28 pm
by khushbush
My login?? :?

Erm...there are various search criteria...not just one. The funny thing is that for what I'm building, there is a quicksearch where you specify a property type and a property area and I've put all the search criteria into one php file, as well as the queries and it works perfectly fine. There are no problems whatsoever. No search overlaps.

I can't seem to do the same for an advanced search.

I want to at least be able to refer to other search files without including them.

Re: Search code giving funny results

Posted: Sun Apr 20, 2008 6:00 pm
by Kieran Huggins
Check out the MySQL SELECT options - the more you do in SQL the better.

your query might end up looking like

Code: Select all

SELECT * FROM properties WHERE `proptype` IN (bungalow,flat,etc) AND `num_rooms` >= 3 AND `price` BETWEEN '200000' AND '400000'
http://dev.mysql.com/doc/refman/5.0/en/ ... ators.html

Re: Search code giving funny results

Posted: Mon Apr 21, 2008 4:18 am
by khushbush
Cool!! Thanks, Kieran! I'll look into it ASAP!! :)