Proper code to filter a price range in PHP / SQL Query

Question

Really hoping someone can help me with this. I'm building a PHP / MySQL search form that, hopefully, will allow users to search our wine database and filter the results according to a range of prices selected via a drop down menu.

The form works fine searching for, and returning, a nice list of accurate results. But it does NOT price filter the results.

After days of searching and experimenting, I've mashed together various code snippets to get this far but, overall, PHP is still very much a mystery to me.

It's the correct coding and syntax I struggle with.

How might I code the PHP posted here to properly integrate the Price Range filter? I suspect my inclusion of "pricerange" in the sql query is way off base.

• MySQL Server version: 5.1.65-cll
• Price column type: decimal(10,2)

Any help would be hugely appreciated. Please check the code blocks below.

Thanks a ton!

HTML

 <form  method="post" action="winesearch.php?go" id="searchform"> 
 <input  type="text" size="35" name="user-entry"/>
 <select name="pricerange" size="1" id="pricerange">
    <option value="">Price Range&nbsp;</option>
    <option value="1">$&nbsp;10 - $20</option>
    <option value="2">$&nbsp;21 - $30</option>
    <option value="3">$&nbsp;31 - $50</option>
    <option value="4">$&nbsp;51 - $75</option>
    <option value="5">$&nbsp;76 - $100</option>
    <option value="6">$101 - $200</option>
    <option value="7">$201 - Plus</option>
</select> 
<input  type="submit" name="submit" value="Wine Search"/> 
</form>

PHP

<?php

  if(isset($_POST['submit'])){
  if(isset($_GET['go'])){
  if(preg_match("/^[a-zA-Z0-9]+/", $_POST['user-entry'])){
  $cob=$_POST['user-entry'];
  $pricerange=$_POST['pricerange'];


  //connect to the database
  $db=mysql_connect  ("server", "user", "pass") or die (mysql_error());

  //-select the database to use
  $mydb=mysql_select_db("db_name");

  if($pricerange == 0) $pricerange = 1;

  switch ($pricerange) {
  case 1  :  $pricerange = " where Price BETWEEN 10.00 AND 20.00 ";  break; 
  case 2  :  $pricerange = " where Price BETWEEN 21.00 AND 30.00 ";  break;  
  case 3  :  $pricerange = " where Price BETWEEN 31.00 AND 50.00 ";  break;   
  case 4  :  $pricerange = " where Price BETWEEN 51.00 AND 75.00 ";  break;     
  case 5  :  $pricerange = " where Price BETWEEN 76.00 AND 100.00 ";  break;       
  case 6  :  $pricerange = " where Price BETWEEN 101.00 AND 200.00 ";  break;         
  case 7  :  $pricerange = " where Price > 200.00 ";  break;           
  }

  //-query the database table
  $sql="
    SELECT  ID, 
    CSPC, 
    Country,
    Producer,
    Wine,
    Year,
    Price 
    FROM winecellar WHERE 
    CSPC LIKE '%" . $cob .  "%' 
    OR 
    Country LIKE '%" . $cob ."%'
    OR 
    Producer LIKE '%" . $cob ."%'
    OR 
    Wine LIKE '%" . $cob ."%'
    OR 
    Year LIKE '%" . $cob ."%'
    OR 
    Price LIKE '%" . $pricerange ."%'
    ";

  //-run  the query against the mysql query function
  $result=mysql_query($sql);

  //-create  while loop and loop through result set
  while($row=mysql_fetch_array($result)){
    $CSPC=$row['CSPC'];
    $Country=$row['Country'];
    $Producer=$row['Producer'];
    $Wine=$row['Wine'];
    $Year=$row['Year']; 
    $Price=$row['Price'];
    $ID=$row['ID'];

    //-display the result of the array
echo  "<ul>\n";
echo  "<li>" . $CSPC . "</li>\n";
echo  "<li>" . $Country . "</li>\n";
echo  "<li>" . $Producer . "</li>\n";
echo  "<li>" . $Wine . "</li>\n";
echo  "<li>" . $Year . "</li>\n";
echo  "<li>" . "<a href=" . $Price .  ">" . "$" . $Price . "</a></li>\n";

echo  "</ul>";
  }
  }
  else{
  echo  "<p>Please enter a search query</p>";
  }
  }
  }
?>

Answer 1

<?php

  if(isset($_POST['submit'])){
  if(isset($_GET['go'])){
   // improved the filter to support space and -
   // Also closed critical security breache (SQL-injection)
  if(preg_match("/^[a-zA-Z0-9 -]+$/", $_POST['user-entry'])){
  $cob=$_POST['user-entry'];
  $pricerange=$_POST['pricerange'];


  //connect to the database
  $db=mysql_connect  ("server", "user", "pass") or die (mysql_error());

  //-select the database to use
  $mydb=mysql_select_db("db_name");

  switch ($pricerange) {
  case 2  :  $pricerange = " AND Price BETWEEN 21.00 AND 30.00 ";  break;  
  case 3  :  $pricerange = " AND Price BETWEEN 31.00 AND 50.00 ";  break;   
  case 4  :  $pricerange = " AND Price BETWEEN 51.00 AND 75.00 ";  break;     
  case 5  :  $pricerange = " AND Price BETWEEN 76.00 AND 100.00 ";  break;       
  case 6  :  $pricerange = " AND Price BETWEEN 101.00 AND 200.00 ";  break;         
  case 7  :  $pricerange = " AND Price > 200.00 ";  break;
  default :  $pricerange = " AND Price BETWEEN 10.00 AND 20.00 "; // covers all other cases
  }

  //-query the database table
  $sql="
    SELECT  ID, 
    CSPC, 
    Country,
    Producer,
    Wine,
    Year,
    Price 
    FROM winecellar WHERE 
    (CSPC LIKE '%" . $cob .  "%' 
    OR 
    Country LIKE '%" . $cob ."%'
    OR 
    Producer LIKE '%" . $cob ."%'
    OR 
    Wine LIKE '%" . $cob ."%'
    OR 
    Year LIKE '%" . $cob ."%')
    " . $pricerange;

  //-run  the query against the mysql query function
  $result=mysql_query($sql);

  //-create  while loop and loop through result set
  while($row=mysql_fetch_array($result)){
    $CSPC=$row['CSPC'];
    $Country=$row['Country'];
    $Producer=$row['Producer'];
    $Wine=$row['Wine'];
    $Year=$row['Year']; 
    $Price=$row['Price'];
    $ID=$row['ID'];

    //-display the result of the array
echo  "<ul>\n";
echo  "<li>" . $CSPC . "</li>\n";
echo  "<li>" . $Country . "</li>\n";
echo  "<li>" . $Producer . "</li>\n";
echo  "<li>" . $Wine . "</li>\n";
echo  "<li>" . $Year . "</li>\n";
echo  "<li>" . "<a href=" . $Price .  ">" . "$" . $Price . "</a></li>\n";

echo  "</ul>";
  }
  }
  else{
  echo  "<p>Please enter a search query</p>";
  }
  }
  }
?>

Answer 2

I would echo out your sql query before it is run so you can see what it looks like. But it appears that the SQL part is wrong for the pricerange section. Right now it would look like this:

OR Price LIKE '% where price BETWEEN 10.00 AND 20.00 %'

I would think you would want it to look like:

OR PRICE BETWEEN 10.00 AND 20.00

Do you want that to be an 'or' or an 'and'?

Answer 3

You had it right up until you put the query together. You don't need the "where" in the statements below since you already have it in the query you're building below.

switch ($pricerange) {
  case 1  :  $pricerange = " Price BETWEEN 10.00 AND 20.00 ";  break; 
  case 2  :  $pricerange = " Price BETWEEN 21.00 AND 30.00 ";  break;  
  case 3  :  $pricerange = " Price BETWEEN 31.00 AND 50.00 ";  break;   
  case 4  :  $pricerange = " Price BETWEEN 51.00 AND 75.00 ";  break;     
  case 5  :  $pricerange = " Price BETWEEN 76.00 AND 100.00 ";  break;       
  case 6  :  $pricerange = " Price BETWEEN 101.00 AND 200.00 ";  break;         
  case 6  :  $pricerange = " Price > 200.00 ";  break;           
}

OR 
Price LIKE '%" . $pricerange ."%'

should be

OR ". $pricerange ."

because you're already building the between statements.