[SOLVED] MYSQL Query Help

[djjamiegee]

hey

 

i am having real problems with this issue as i am still very new to PHP and MYSQL.

 

i am making an estate agents site and putting a seach finder on it i have the folloing form on my page.

 

<form action="finderresults.php" method="get">
<table width="608" border="0" cellpadding="1">
  <tr>
    <td width="109"><legend>Type</legend></td>
    <td width="100"><legend>Min. Bedrooms</legend></td>
    <td width="88"><legend>Max. Price</legend></td>
    <td width="120"><legend>Area [eg. basildon]</legend></td>
    <td width="153"><legend></legend></td>
    <td width="12"><legend> </legend></td>
  </tr>
  <tr>
    <td><select name="type" id="select" class="select">
      <option value="">Any</option>
  <option value="house">House</option>
      <option value="flat">Flat</option>
      <option value="bungalow">Bungalow</option>
      <option value="mid">Mid Terrace</option>
      <option value="end">End Terrace</option>
      <option value="semi">Semi Detached</option>
      <option value="detached">Detached</option>
      <option value="cottage">Cottage</option>
      <option value="other">Other</option>
      </select></td>
    <td><select name="bedrooms" id="select" class="select">
     <option value="">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">5+ Bedrooms</option>
      </select></td>
    <td><select name="maxprice" id="select" class="select">
    <option value="">Any</option>
      <option value="50000">£50,000</option>
      <option value="100000">£100,000</option>
      <option value="150000">£150,000</option>
      <option value="200000">£200,000</option>
      <option value="250000">£250,000</option>
      <option value="300000">£300,000</option>
      <option value="350000">£350,000</option>
      <option value="400000">£400,000</option>
      <option value="450000">£450,000</option>
      <option value="500000">£500,000 +</option>
      </select></td>
    <td><label>
      <input type="text" name="area" id="label" />
    </label></td>
    <td valign="middle"><input name="submit" type="submit"/></td>
    <td> </td>
  </tr>
</table>

 

i have got the php code sorted so it will search what values are selected. i now what it to search mysql but if they select the any value from the search box it will find all the properties in that row but will still use the other options boxes to filter

 

i.e they select 'any' on type but min 3 bedrooms and maxprice 350,000 or they select any on type and any on min bedrooms but maxprice is less then 100,000.

 

i hope this make sense here is my php code

 

<?php

echo "<table border='0' cellpadding='0' align='center' width='600'>";
  echo "<tr>";
    echo "<td width=100' align='center'></td>";
   echo "<td width='120' align='left'>Address</td>";
   echo  "<td width='60' align='center'>Bedrooms</td>";
    echo  "<td width='60' align='center'>Type</td>";
    echo  "<td width='70' align='center'>Area</td>";
echo  "<td width='80' align='center'>Price</td>";
echo  "<td width='80' align='center'></td>";
  echo  "</tr>";
echo  "</table>";

$type=$_GET['type'];
$bedrooms=$_GET['bedrooms'];
$maxprice=$_GET['maxprice'];
$area=$_GET['area'];

//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("*****","****","****"); 

//select which database you want to edit
mysql_select_db("****"); 

//get the mysql and store them in $result
//change whatevertable to the mysql table you're using
//change whatevercolumn to the column in the table you want to search  

$result = mysql_query("SELECT * from properties WHERE type = '$type' AND bedrooms >= '$bedrooms' AND maxprice < '$maxprice' AND area LIKE '$area' ORDER BY  bedrooms
;");

//grab all the content
while($r=mysql_fetch_array($result))
{	
   $type=$r["type"];
   $bedrooms=$r["bedrooms"];
   $maxprice=$r["maxprice"];
   $area=$r["area"];
   $first_address=$r["first_address"];
   $link=$r["link"];
   $image=$r["image"];
   $postcode=$r["postcode"];

//display the row
echo "<table border='0' cellpadding='0' align='center' width='600' class='search'>";
  echo "<tr'>";
    echo "<td width='100'><img src=\"" . $r["image"] .  "\"></td>";
   echo "<td width='120'>$first_address<br>$postcode</td>";
   echo  "<td width='60' align='center'>$bedrooms</td>";
    echo  "<td width='60' align='center'>$type</td>";
    echo  "<td width='70' align='center'>$area</td>";
echo  "<td width='80' align='center'>£$maxprice</td>";
echo  "<td width='80' align='center'><a href=\"" . $r["link"] . "\">
<img src=\"info.png" . "\" border=0 alt=\"" . "\">
</a></td>" ;

  echo  "</tr>";
echo  "</table>";
echo "<br>";
}
?>

 

cheers jamie

[Maq]

replace:

 

$type=$_GET['type'];

 

with this:

 

$type= ($_GET['type']=="") ? "" : "type = '{$_GET['type']}'";

 

And replace:

 

$result = mysql_query("SELECT * from properties WHERE type = '$type' AND bedrooms >= '$bedrooms' AND maxprice 
 

with this:
 

[code]$result = mysql_query("SELECT * from properties WHERE $type AND bedrooms >= '$bedrooms' AND maxprice 

 
 

[djjamiegee]

hey i have just entered the new code you send me and i now get the error

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in E:\domains\d\djjamiegee.com\user\htdocs\pps\finderresults.php on line 67

 

cheers

[Maq]

Yeah sorry, didn't compensate for the 'AND'.

 

Use this:

 

$type= ($_GET['type']=="") ? "" : "type = '{$_GET['type']}' AND";

 

and:

 

$result = mysql_query("SELECT * from properties WHERE $type bedrooms >= '$bedrooms' AND maxprice 

[djjamiegee]

excellent works great now is there anyway i can do that for the rest of the boxes?? i.e if 'any' is selected in type box and 'any' selected in bedrooms box 'any'seclected in maxprice box so it will just search for area??

 

thank jamie