Looking for some assistance with a Search Tool I'm constructing

[darkestknight43]

Hello everyone. I am generally new to PHP but so far have managed to grasp all the basics quite well, and am having a little trouble adapting to some of the logic. I've taken my problem elsewhere and received solutions, but they've managed to fix certain problems while creating others. The basic working of my Search Tool are as such: The main page has all of the search criteria for Mobile Homes - Beds, Baths, Width, Manufacturer, Home Type (New or Used), On Lot, Homes with Pictures, & Homes with Tours. These criteria are sent through and processed by homeresults.php, where it should query the database pertinent to what the user selected, and return all relevant home listings. As it stands right now, if I leave only the Beds and Baths criteria, and leave all options to "Any", it will successfully return all homes, but when I begin to add additional variables into the mix, it defaults to an error. If anyone can take a look at my current code just to see if I've made any simple mistakes in my logic or code, it would be greatly appreciated as I've been working hard on this since early January, with no finalized result. To be honest I am desperately seeking a solution to get this up and running. Thank you for your time and patience. Here are the code snippets:

 

The Search Tool Criteria (Homesearch.php)

 include("misc.inc");                                 #12

  $cxn = mysqli_connect($host,$user,$passwd,$dbname)   #14
         or die ("couldn't connect to server");

  /* Select all categories from Homes table */
  $query = "SELECT * FROM Homes";    #18
  $result = mysqli_query($cxn,$query)
            or die ("Couldn't execute query.");       
while($row = mysqli_fetch_assoc($result)) 
{
extract($row);
}         

//Negligible Formatting Code Omitted for Cleanliness//

echo "<form action='homeresults.php' method='POST'>\n";
echo "  <select name=\"Beds\">\n";
echo "	<option selected=\"selected\" value=\"0\">Any</option>\n";
echo "	<option  value=\"1\">1</option>  \n";
echo "	<option  value=\"2\">2</option> \n";
echo "	<option  value=\"3\">3</option> \n";
echo "	<option  value=\"4\">4</option>\n";
echo "        <option  value=\"5\">5</option></select>\n";

echo "  <b><u>Baths:\n";
echo "</td>\n";
echo "<td>\n";
echo "<select name=\"Baths\">\n";
echo "	<option selected=\"selected\" value=\"0\">Any</option>\n";
echo "	<option  value=\"1\">1</option> \n";
echo "	<option  value=\"1.5\">1.5</option> \n";
echo "	<option  value=\"2\">2</option> \n";
echo "	<option  value=\"2.5\">2.5</option> \n";
echo "	<option  value=\"3\">3</option> \n";
echo "	<option  value=\"3.5\">3.5</option>\n";
echo "	<option  value=\"4\">4</option></select>\n";

echo "<select name='Width'>\n";
echo "	<option selected=\"selected\" value=\"\">Any</option>\n";
echo "	<option  value='Single-Wide'>Single-Wide</option> \n";
echo "	<option  value='Double-Wide'>Double-Wide</option> \n";
echo "	<option  value='Triple-Wide'>Triple-Wide</option> </select>\n";

echo " <b><u>Manufacturer:\n";
echo "</td>\n";
echo "<td>\n";
echo "<select name=\"Manufacturer\">\n";
echo "	<option selected=\"selected\" value=\"\">Any</option>\n";
echo "	<option  value='SE Homes'>SE Homes</option> \n";
echo "	<option  value='Champion'>Champion</option> \n";
echo "	<option  value='Legacy'>Legacy</option> \n";
echo "	<option  value='Redman'>Redman</option> \n";
echo "	<option  value='Silvercreek'>Silvercreek</option>\n";
echo "	<option  value='Palm Harbor'>Palm Harbor</option>  </select>\n";

echo "<input type=\"checkbox\" name=\"HomeType\" value=\"New\"> <b>New Homes|
<input type=\"checkbox\" name=\"HomeType\" value=\"Used\"> <b>Used \n";echo "Homes|
<input type=\"checkbox\" name=\"OnLot\" value=\"Yes\"><b> Available on Lot?<br>\n";
echo "<input type=\"checkbox\" name=\"Tours\" value=\"Y\"> <b>View Homes with Tours<br>
<input type=\"checkbox\" name=\"Photo\" value=\"Yes\"> \n";
echo "<b>View Homes with Pictures<br>\n";
echo "<td colspan=\"2\" align=\"center\"><input type=\"submit\" value=\"Search Homes\">\n";
echo "</td>\n";
echo "</tr>	 \n";
echo "</form>\n";

 

Here is the Snippet for the Results page (homeresults.php)

 

include("misc.inc");

  $cxn = mysqli_connect($host,$user,$passwd,$dbname)
         or die ("couldn't connect to server");

  /* Select homes of the given type */
/*Need extra form cleansing to avoid injection*/
//Need extra form cleansing to avoid injection
$beds = $_POST['Beds'];
$baths = $_POST['Baths'];
$width = $_POST['Width'];

//String concat idea 
$where = '';
if(!empty($beds))  
{
$where .= "Beds >= $beds,";
}

if(!empty($baths)) 
{
$where .= "Baths >= $baths,";
} 

if(!empty($width)) 
{
$where .= "'Width = $width,'";
} 


$where = rtrim($where,', ');
if(!empty($where))  
{
$query .= 'SELECT * FROM Homes WHERE '.$where;
}
  $result = mysqli_query($cxn,$query)
            or die ("Couldn't execute query.");

  /* Display results in a table */
  echo "<br>\n";
  while($row = mysqli_fetch_assoc($result))            #36
  {
    extract($row);
    /* display row for each Home */
//Following Code has been omitted, as it is used for Formatting, and works fine when code above is functioning.//

 

And that's the codes in a nutshell. For a client-side view, the address is www.ziafactorynm.com/homesearch.php

 

Thank you once again for your time and patience

 

[AdRock]

Looks yo me like you're checking if $where isn't empty and do the query but what if $where is empty?  There is no query to start

 

if(!empty($where))  
{
$query .= 'SELECT * FROM Homes WHERE '.$where;
}
else {
$query .= 'SELECT * FROM Homes
}

[scvinodkumar]

I think it will work, just go ahead and test it...

 

$beds = $_POST['Beds'];
   $baths = $_POST['Baths'];
   $width = $_POST['Width'];

   //String concat idea 
   $where = '';
   if(!empty($beds))  
      $where .= " AND Beds >= $beds";
   if(!empty($baths)) 
      $where .= " AND Baths >= $baths";
   if(!empty($width)) 
      $where .= " AND Width = $width";

   //$where = rtrim($where,', ');
   if(!empty($where))  
      $query = 'SELECT * FROM Homes WHERE 1 '.$where;
   else
      $query = 'SELECT * FROM Homes WHERE 1';

[MatthewJ]

Like scvinodkumar pointed out, you were also missing the AND's from the where clause. Where you query would have been WHERE beds>=$beds, baths>=$baths... it should have been WHERE beds >= $beds AND baths >= $baths.

[darkestknight43]

Excellent! Thank you all so much for your input, I've been trying to get this thing going for months now, and you guys are all lifesavers. You have my gratitude all around.