Page 1 of 1

how to stop an option value searching mysql

Posted: Thu Feb 12, 2009 8:07 pm
by djjamiegee
hi

am am still very new to mysql and php coding. I am making an estate agent site with a property search feature. i hate it all working how i want it to but now i have thought of another option for them to choose but dont know how to go about it.

Code: Select all

<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>&nbsp;</legend></td>
  </tr>
  <tr>
    <td><select name="type" id="select" class="select">
      <option value="no">No pref</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="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="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>&nbsp;</td>
  </tr>
</table>
as you can see i have 4 'option' drop down boxes and they can choose which options they want and it will search mysql but now i want it to so they can have one box as 'NO Pref' so it will not search that dropdown box but will still use the other 3 dropdown boxes to search my sql. i think i need and if statment but not sure how to code.

Code: Select all

<?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("djjamiegee1"); 
 
//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>";
}
?>
many thanks in advance sorry if i havent explained very well.

jamie

Re: how to stop an option value searching mysql

Posted: Thu Feb 12, 2009 8:12 pm
by John Cartwright
Please remove your database credentials (for your own protection) before posting in the future. I've removed it already for you this time.

If you want to selectively choose which variables to search for, you will need to dynamically build your SQL query.

Code: Select all

$sql = "
   SELECT * 
   FROM properties
   WHERE 1
";
 
if (!empty($type)) {
   $sql .= " AND type = '". mysql_real_escape_string($type) ."'";
}
 
//... other vars
 
$sql .= "
   ORDER BY  bedrooms
";
 
$result = mysql_query($sql) or die(mysql_error());
In this example, if the $type variable is empty (assuming your "not specified" value is blank), it will not be included in the query.

Re: how to stop an option value searching mysql

Posted: Thu Feb 12, 2009 8:51 pm
by djjamiegee
hiya

thank for your quick reply and erasing the credentials didnt even realise. i have added the code and the first bit works great just want i want it to do but when it comes to the bedrooms bit it doesnt filter them. i.e if i put 3 or more bedrooms it still displays the 1 and 2 bedroom properties.


many thanks in advance

jamie

Re: how to stop an option value searching mysql

Posted: Thu Feb 12, 2009 8:52 pm
by John Cartwright
Post your code in proper tags

Re: how to stop an option value searching mysql

Posted: Thu Feb 12, 2009 8:56 pm
by djjamiegee
hiya

i have just sorted it myself i used the following code

Code: Select all

$sql = "
  SELECT * 
  FROM properties
  WHERE 1
";
 
if (!empty($type)) {
   $sql .= " AND type = '". mysql_real_escape_string($type) ."'";
}
 
//... other vars
 
$sql .= "
  and bedrooms >= '$bedrooms' ORDER BY bedrooms
";
 
$result = mysql_query($sql) or die(mysql_error());
if i want to do that for the other search boxes do i just create another query the same but change the varibles??

many thanks for your help
jamie

Re: how to stop an option value searching mysql

Posted: Thu Feb 12, 2009 10:01 pm
by John Cartwright
Please post in proper php tags (as shown above).

I can't be bothered if you can't either.

Re: how to stop an option value searching mysql

Posted: Fri Feb 13, 2009 5:23 am
by djjamiegee

Code: Select all

<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>&nbsp;</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>&nbsp;</td>
  </tr>
</table>

Code: Select all

<?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
;");
 
$sql = "
  SELECT * 
  FROM properties
  WHERE 1
";
 
if (!empty($type)) {
   $sql .= " AND type = '". mysql_real_escape_string($type) ."'";
}
 
//... other vars
 
$sql .= "
  AND bedrooms >= '$bedrooms' ORDER BY bedrooms
";
 
$result = mysql_query($sql) or die(mysql_error());
 
//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>";
}
?>
hey sorry for all the confusing i am very new to php and have only just joint this forum, i have posted my php code with the bit of code you have already done i was now just wondering how i can apply the same rule to all the option boxes. i.e if type blnak and bedrooms blank search maxprice and area.

cheers jamie

Re: how to stop an option value searching mysql

Posted: Fri Feb 13, 2009 8:51 am
by John Cartwright
Looks carefully at the circumstances in which I outlined for the $type variable, and apply that same logic.

Hint: You only want to each variable when they are not empty.

Re: how to stop an option value searching mysql

Posted: Fri Feb 13, 2009 10:09 am
by djjamiegee
i have tried a number of differnet ways using else and else if but still cant work it out, i know i am missing something obvious but just cant put me finger on it

would i create another

Code: Select all

$sql = "
  SELECT * 
  FROM properties
  WHERE 1
";
 
if (!empty($type)) {
   $sql .= " AND type = '". mysql_real_escape_string($type) ."'";
   
}
 
//... other vars
 
$sql .= "
  AND bedrooms >= 'bedrooms'
";
 
$result = mysql_query($sql) or die(mysql_error());
or would i add more circumstances to the one above.

cheers jamie

Re: how to stop an option value searching mysql

Posted: Fri Feb 13, 2009 10:16 am
by John Cartwright

Code: Select all

$sql = "
 SELECT *
 FROM properties
 WHERE 1
";
 
if (!empty($type)) {
   $sql .= " AND type = '". mysql_real_escape_string($type) ."'";
}
 
if (!empty($bedrooms)) { 
   $sql .= " AND bedrooms >= ". (int)$bedrooms;
}
 
//... other vars

:?