Multiple Fields search MySQL database using php

Question

I'm almost there, my code it doesn't show any results.
it's a basic form where users search by postcode and property type.
they should be able to search by entering only the postcode or both.
I'm working in localhost php7
here the html

<form action="phpSearch.php" method="post">
    <input type="text" placeholder="Search" name="postcode" id="postcode">
    <select name="type" id="type">
        <option value="Terraced">Terraced</option>
        <option value="Detached">Detached</option>
    </select>
    <button type="submit" name="submit">Search</button>
</form>

here the php

<?php
$postcode = $_POST['postcode'];
$type = $_POST['type'];


$servername = "localhost";
$username = "root";
$password = "";
$db = "priceverification";

$conn = new mysqli($servername, $username, $password, $db);

if ($conn->connect_error){
    die("Connection failed: ". $conn->connect_error);
}

$sql = "SELECT * from house WHERE $type like '%$postcode%'";

$result = $conn->query($sql);
  if($result){
if ($result->num_rows > 0){
while($row = $result->fetch_assoc()){
    echo $row["postcode"]."  ".$row["type"]."  ".$row["town"]."<br>";
}
} else {
    echo "0 records";
}
 }else {
                echo "<br> Database error.";
            }
$conn->close();
?>

database here

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Nov 11 '21 at 14:18
`$type like '%$postcode%'` This doesn't look correct. You should compare each value to a column in your database. — Dharman, Nov 11 '21 at 14:19

score 0 · Accepted Answer · answered Nov 11 '21 at 14:26

$type like '%$postcode%' is invalid code for multiple reasons. You need to build the search criteria based on the values coming from the form.

Here is how the code should look like properly:

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = new mysqli("localhost", "root", "", "priceverification");
$conn->set_charset('utf8mb4'); // always set the charset

$postcode = $_POST['postcode'] ?? '';
$type = $_POST['type'] ?? '';

$wheres = [];
$values = [];
if ($postcode) {
    $wheres[] = 'postcode LIKE ?';
    $values[] = '%'.$postcode.'%';
}
if ($type) {
    $wheres[] = 'type = ?';
    $values[] = $type;
}
$where = implode(' AND ', $wheres);
if ($where) {
    $sql = 'SELECT * from house WHERE '.$where;
} else {
    $sql = 'SELECT * from house';
}

$stmt = $conn->prepare($sql);
$stmt->bind_param(str_repeat('s', count($values)), ...$values);
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows > 0) {
    foreach ($result as $row) {
        echo $row["postcode"] . "  " . $row["type"] . "  " . $row["town"] . "<br>";
    }
} else {
    echo "0 records";
}

Multiple Fields search MySQL database using php

1 Answers1