How to search based on HTML option tag

Question

I have my search form, where the cities are fetched from the database, but how can I make the search work when I select some option? I need to get "printed" everything about all houses that are in that particular selected city

HTML Form:

<form action="./server/Search.php" method="post">

<div class="col-auto mt-5">
  
<select name="city" class="form-select" aria-label="Default select example ">
  <option selected disabled>City</option>
  <?php  
    $query = $conn->query("SELECT * FROM `houses`") or die(mysqli_error());
    while($fetch = $query->fetch_array()){
    ?>
  <option value=""><?php echo $fetch['city']?></option>
  <?php
    }
    ?>
</select>
</div>

  <div class="col-auto mt-5">
    <button type="search" name="search" class="btn btn-primary"><i class='bx bx-search-alt-2'></i></button>
  </div>
</form>

Search.php

<?php
    $con= new mysqli("localhost","root","","KBHestate");
    $name = $_post['search'];

    if (mysqli_connect_errno())
      {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }

$result = mysqli_query($con, "SELECT * FROM houses
    WHERE city LIKE '%{$city}%'");

while ($row = mysqli_fetch_array($result))
{
        echo $row['city'];
        echo "<br>";
}
    mysqli_close($con);
    ?>

I don't know how to connect selected option with the query. Is there any way how to handle this the best way possible?

Answer 1

First change in the html form, to avoid selecting columns and rows you don't need:

<?php
  $query = $link->query("SELECT distinct city FROM `houses`");
  while($fetch = $query->fetch_array(MYSQLI_ASSOC)){
    echo "<option value='{$fetch['city']}'>{$fetch['city']}</option>\n";
  }
?>

Then search.php with bind variables.

<?php
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    $link = new mysqli("localhost","root","","KBHestate");
    
    $name = $_POST['city'];
    $query = $link->prepare("SELECT id FROM houses where city = ?");
    $query->bind_param("s", $name);
    $query->execute();
    $query->bind_result($id);
    while($query->fetch()){
        echo "{$id}</br>\n";
    }

?>

Answer 2

It seems that a few things are missing here:

The value is missing in the HTML Form so nothing gets posted

You should not use the cities name (because there are a lot of cities with problematic characters) but a numerical id for the value.

This also means that you need a seperate lookup table with cities and the city id and join this with the houses table.

">

Then your second script search.php does not use the posted value

 $city_id = $_POST['city_id'];

[...]
now you can use $city_id in the query:

$result = mysqli_query($con, "SELECT * FROM houses
    WHERE city_id ='{$city_id}'");

BUT in fact this is not the proper way to do it - use prepared statments instead.