Build a sql query in php from multiple form inputs

I know you're currently using mysqli, but PDO makes building dynamic queries much easier, so I strongly suggest you switch to it, if you're not very far along on this project.

In a mysqli prepared statement, you have to call mysqli_stmt::bind_param(), passing every parameter in the argument list. In contrast, PDO requires no binding, and the parameters are all passed to PDOStatement::execute() in an array. This answer will show you how your code would work with PDO.

<?php

$connection = new \PDO("mysql:host=$db_host;dbname=$db_name", $db_user, $db_pass);

$query = "SELECT * FROM Listings WHERE bedroom = :bed AND type = :type AND (city LIKE :loc OR addressLineOne LIKE :loc OR addressLineTwo LIKE :loc OR county LIKE :loc)";
$parameters = [
    ":bed" => $_POST["bedroom"],
    ":type" => $_POST["type"],
    ":loc" => "%$_POST[location]%",
];

if(!empty($_POST["max"])) {
    $query .= " AND price <= :max";
    $parameters[":max"] = $_POST["max"];
}

if (!empty($_POST["min"])) {
    $query .= " AND price >= :min";
    $parameters[":min"] = $_POST["min"];
}

$query .= " ORDER BY price";

$stmt = $connection->prepare($query);
$stmt->execute($parameters);
$results = $stmt->fetchAll(\PDO::FETCH_ASSOC);

if (empty($results)) { // no results then print the following
    echo "Sorry, we couldn't find any results. Please refine your search and try again.";
}
foreach ($results as $result) {
    //escape for HTML output
    $result = array_map("htmlspecialchars", $result);
    echo <<< HTML
<p>
<h3>$result[addressLineOne] $result[addressLineTwo] $result[location]</h3>
<h4>£$result[price]</h4>
$result[information]
</p>
<br />
<hr />

HTML;
}

I've also simplified your HTML output by using a heredoc string, but you should really have your HTML and PHP separated.

If this is part of a much bigger existing project, you will likely be sticking with mysqli, in which case I urge you to learn how to use prepared statements; the days of building queries with string concatenation are long behind us!

Using PDO and bound parameters as @miken32 suggests is a possibility, but I advise against it because it has several downsides:

• You are currently using mysqli, which also supports bound parameters, so no reason to switch to PDO just to get parameter binding
• @miken32's solution uses a named parameter (something only PDO supports) multiple times in the query. This only works when client-side parameter binding is enabled (i.e. PDO::ATTR_EMULATE_PREPARES set to true) which itself has multiple problems:
  • I could not quickly find out what the default for PDO::ATTR_EMULATE_PREPARES is, apparently "it depends on the driver".
  • With PDO::ATTR_EMULATE_PREPARES switched on, you cannot use bound parameters in your LIMIT clause anymore
  • The errors returned from PDO are completely different depending on whether you use client-side or server-side parameter binding, a missing parameter for example causes an SQLSTATE of HY000 with server-side and HY093 with client-side parameter binding. This complicates proper error handling.
• There is no way to see the actual query, an ability that can be very useful (essential in my opinion) for debugging.

So, I say go ahead and build your query manually, but in a clean and safe way. To do that you need to understand these concepts:

HTML encoding

In general, the only place where you need htmlspecialchars() is when you pass data (for example from the database) to the browser.

So, change every

$location = trim($_POST['location']);
$location = strip_tags($location);
$location = htmlspecialchars($location);

to

$location = trim($_POST['location']);

There is no need for strip_tags() nor htmlspecialchars() at this point. Form data from the browser arrives on the PHP side without any encoding. Of course, if someone would actually enter some<br>city into the location field, he would not find any rooms, but it will not break anything if you get the rest of your application right.

Also change every

echo "<p>".$result['addressLineOne']." ... </p><br /><hr />";

to

echo "<p>".htmlspecialchars($result['addressLineOne'])." ... </p><br /><hr />";

Otherwise the people entering the data into the database could run a "cross site scripting attack" - maliciously or accidentally.

SQL encoding

When sending data to the database as part of a query, you have to encode it in a way that the database knows that it's variable data and not part of the SQL command.

So, instead of for example

$query .= " AND (`city` LIKE '%".$location."%') ";

you have to write

$query .= " AND (`city` LIKE '%".addslashes($location)."%') ";

this makes sure that if there is a quote character (') inside your $location variable it will be escaped, so it does not end the string at that point.

If you work with a character set other than UTF-8, you should use mysql_real_escape_string() instead of addslashes() but you are probably using UTF-8, so it's just fine.

Furthermore in this case you might want to remove or escape % and _ in the value, because they have a special meaning in a LIKE query.

You have two more problems in your code:

$query = "SELECT * FROM Listings WHERE `bedroom` = ".$bedroom." AND `type` = ".$type."";

Here you not only have to add addslashes() but also the quotes around the value:

$query = "SELECT * FROM Listings WHERE `bedroom` = '".addslashes($bedroom)."' AND `type` = '".addslashes($type)."'";

And here:

$query .= " AND (`price` <= '%".$price."%')";

Obviously the percent signs make no sense here, you clearly meant:

$query .= " AND (`price` <= '".addslashes($price)."')";

Libraries and template engines

Later on you would be well advised using a library or framework that does those things for you (because it's very easy to forget an addslashes() somewhere), but it can't hurt to do it manually at first in order to learn how it works under the hood.