Question 113650 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – unable to create a price filter using php and html form

I’m trying to create a price filter using a html form and php prepared statements. My form looks like this:

   <form class="hotel-form" action="#" method="get">
   <label for="price-per-night" class="land-label">Price-per-night:</label>
    <select class="filter-form" name="price" id="price-per-night">
     <option value="100">£0-100</option>
     <option value="101-200">£101-200</option>
     <option value="300">£201-300</option>
    </select>
    <input type="submit" value="Filter" class="filter">
   </form>

and my prepared statement looks like this:

if ($price = $_REQUEST['price']) {
  $asql = "SELECT * FROM accommodation WHERE price_per_night < ? ORDER BY price_per_night";
  $stmt = mysqli_prepare($conn, $asql);
  mysqli_stmt_bind_param($stmt, "i", $price);
  mysqli_stmt_execute($stmt);
  $queryresults3 = mysqli_stmt_get_result($stmt);
}
elseif ($price = $_REQUEST['price']) {
  $asql = "SELECT * FROM accommodation WHERE price_per_night > ? ORDER BY price_per_night";
  $stmt = mysqli_prepare($conn, $asql);
  mysqli_stmt_bind_param($stmt, "i", $price);
  mysqli_stmt_execute($stmt);
  $queryresults4 = mysqli_stmt_get_result($stmt);
}

the if statement successfully displays all accommodation with a price less than $100, however the elseif statement does not work. It displays all accommodation. Is there a way to display accommodation correctly based on the values in the html form?

Tags:

2

Answers


  1. 0

    You need to uniform your HTML values. So; 0-100 , 101-200 , 201-300 etc. and then which ever value is selected and submitted in the HTML form, then you run some string functions (such as explode) to split on the - character and take the left side value as the minimum and the right side value as the maximum and plug them both into your SQL accordingly.

    Your else/if also looks like code repetition which is indicative of bad design.

    EXAMPLE:

    HTML

    <select class="filter-form" name="price" id="price-per-night">
     <option value="0-100">&#x00A3;0-100</option>
     <option value="101-200">&#x00A3;101-200</option>
     <option value="201-300">&#x00A3;201-300</option>
    </select>

    PHP

    $price = explode("-",$_POST['price'],2);
// price[0] is the min, price[1] is the max.

  $asql = "SELECT * FROM accommodation WHERE price_per_night => ? AND price_per_night <= ? ORDER BY price_per_night";
  $stmt = mysqli_prepare($conn, $asql);
  mysqli_bind_param($stmt, "ii", (int)$price[0], (int)$price[1]);
  mysqli_execute($stmt);
    Login or Signup to reply.
  2. 0

    You have used the assignment operator for comparison, change the = to == like this

    if ($price == $_REQUEST['price']) {  //<--------------------
  $asql = "SELECT * FROM accommodation WHERE price_per_night < ? ORDER BY price_per_night";
  $stmt = mysqli_prepare($conn, $asql);
  mysqli_stmt_bind_param($stmt, "i", $price);
  mysqli_stmt_execute($stmt);
  $queryresults3 = mysqli_stmt_get_result($stmt);
}
elseif ($price == $_REQUEST['price']) { //<--------------------
  $asql = "SELECT * FROM accommodation WHERE price_per_night > ? ORDER BY price_per_night";
  $stmt = mysqli_prepare($conn, $asql);
  mysqli_stmt_bind_param($stmt, "i", $price);
  mysqli_stmt_execute($stmt);
  $queryresults4 = mysqli_stmt_get_result($stmt);
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search