具有多个参数的PDO数据库搜索

Question

I'm trying to search in a database with multiple search parameters. However, I always get the whole table as output. The user is supposed to fill out one or more fields in an HTML form. After the form is submitted, only entries matching the user's parameters should be shown. It worked fine when I only had one parameter.

This is my code:

if (isset($_POST['submit']))
{
    try
    {
        require "../config.php";
        require "../common.php";

        $connection = new PDO($dsn, $username, $password, $options);

        $sql = "SELECT *
                FROM medisGO_patient
                WHERE lastName LIKE '%" . $lastName . "%'
                AND firstName LIKE '%" . $firstName . "%'
                AND birthday LIKE '%" . $birthday . "%'
                AND course LIKE '%" . $course . "%'
                AND id LIKE '%" . $no . "%'";

        $lastName = trim($_POST['lastName']);
        $firstName = trim($_POST['firstName']);
        $course = trim($_POST['course']);
        $birthday = trim($_POST['birthday']);
        $no = trim($_POST['no']);

        $statement = $connection->prepare($sql);
        $statement->bindParam(':lastName', $lastName, PDO::PARAM_STR);
        $statement->bindParam(':firstName', $firstName, PDO::PARAM_STR);
        $statement->bindParam(':birthday', $birthday, PDO::PARAM_STR);
        $statement->bindParam(':course', $course, PDO::PARAM_STR);
        $statement->bindParam(':id', $no, PDO::PARAM_STR);
        $statement->execute();
        $result = $statement->fetchAll();

    }
    catch(PDOException $error)
    {
        echo $sql . "<br>" . $error->getMessage();
    }
}

Answer 1

You're not using parameters in your query, you're using string concatenation. Worse, you're concatenating variables that don't exist yet, so PHP is substituting an empty string. In essence, the query you're building is:

SELECT *
FROM medisGO_patient
WHERE lastName LIKE '%%'
AND firstName LIKE '%%'
AND birthday LIKE '%%'
AND course LIKE '%%'
AND id LIKE '%%'

That's why you're getting the entire table.

You should be using named parameters instead, while you should add the % signs to the values:

$sql = "SELECT *
        FROM medisGO_patient
        WHERE lastName LIKE :lastName
        AND firstName LIKE :firstName
        AND birthday LIKE :birthday
        AND course LIKE :course
        AND id LIKE :id";

$lastName = '%' . trim($_POST['lastName']) . '%';
$firstName = '%' . trim($_POST['firstName']) . '%';
$course = '%' . trim($_POST['course']) . '%';
$birthday = '%' . trim($_POST['birthday']) . '%';
$no = '%' . trim($_POST['no']) . '%';

$statement = $connection->prepare($sql);
$statement->bindParam(':lastName', $lastName, PDO::PARAM_STR);
$statement->bindParam(':firstName', $firstName, PDO::PARAM_STR);
$statement->bindParam(':birthday', $birthday, PDO::PARAM_STR);
$statement->bindParam(':course', $course, PDO::PARAM_STR);
$statement->bindParam(':id', $no, PDO::PARAM_STR);
$statement->execute();