多字段搜索表单显示整个数据库[重复]

I am trying to create a database with multiple fields for searching but it is displaying the entire database if there is an empty field. i suspect it is because of the OR's in the query and i am not sure how to fix it.

<?php
if (isset($_POST['Submit']))
{ 
$con = mysqli_connect();
if (mysqli_connect_errno())
{
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$surname = $_POST['surname'];
$firstname = $_POST['firstname'];
$maiden = $_POST['maiden'];
$birth = $_POST['birth'];
$death = $_POST['death'];
$keyword = $_POST['keyword'];

$sql = "SELECT * FROM obits WHERE  surname LIKE '%" . $surname . "%' OR firstname LIKE '%" . $firstname  . "%' OR maiden LIKE '%" . $maiden . "%'  OR birth LIKE '%" . $birth . "%' OR death LIKE '%" . $death . "%' OR obittext LIKE '%" . $keyword . "%'";

$result = mysqli_query($con, $sql);

further down i have this:

if (isset($result) && $result->num_rows > 0);

then follows the table etc. i think i have all the pertinent info here. any suggestions? please use english rather than programmer, i am quite new at this. thanks in advance!

</div>

Let's look at one of the conditions:

surname LIKE '%" . $surname . "%'

Assuming, $surname is Miller here, you select all rows that have a surname like %Miller%. The % signs are wildcards, which can basically stand for anything. This means you are selecting all rows where the surname contains Miller with any string before or after it, including empty ones.

Now, if Miller would be empty in this case, you are looking for %%, so an empty string with anything before or after it -- so really any possible string. As a result, every row will be selected.

Since this is true not only for the surname, but for any of the columns, leaving any of the criteria blank will result in all rows being selected.

Find more info on SQL Wildcards.

To skip empty values in your where clause, you can build it dynamically:

$condition = "WHERE";

if(!empty($surname)){
    $condition .= "surname LIKE '$surname' OR";
}

if(!empty($firstname)){
    $condition .= "firstname LIKE '$firstname' OR";
}

// ...

$sql = "SELECT * FROM obits " . $condition;

Note:

• There will be a trailing OR in the condition that you will have to remove.
• If all conditions are blank, this will also lead to an error.

But it should give you an inpiration! :-)

Side Note: You should look into prepared statements. Passing POST variables directly into an SQL statement is highly dangerous.