how to stop an option value searching mysql

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
djjamiegee
Forum Newbie
Posts: 7
Joined: Thu Feb 12, 2009 7:57 pm

how to stop an option value searching mysql

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: how to stop an option value searching mysql

Post 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.
djjamiegee
Forum Newbie
Posts: 7
Joined: Thu Feb 12, 2009 7:57 pm

Re: how to stop an option value searching mysql

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: how to stop an option value searching mysql

Post by John Cartwright »

Post your code in proper tags
djjamiegee
Forum Newbie
Posts: 7
Joined: Thu Feb 12, 2009 7:57 pm

Re: how to stop an option value searching mysql

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: how to stop an option value searching mysql

Post by John Cartwright »

Please post in proper php tags (as shown above).

I can't be bothered if you can't either.
djjamiegee
Forum Newbie
Posts: 7
Joined: Thu Feb 12, 2009 7:57 pm

Re: how to stop an option value searching mysql

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: how to stop an option value searching mysql

Post 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.
djjamiegee
Forum Newbie
Posts: 7
Joined: Thu Feb 12, 2009 7:57 pm

Re: how to stop an option value searching mysql

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: how to stop an option value searching mysql

Post 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

:?
Post Reply