CODEWITHSUNDEEP
phpmysqlsql
Nipun Tharuksha
Nipun Tharuksha

Reputation: 2567

Filter data from database with multiple user selections

Currently I'm developing a search form so my SQL query needs to change with user input. Please see the below code sample.

$sqlSearch = "SELECT * FROM seafarers WHERE  ";

if ($dateS != "") {
    $sqlSearch .= "add_date = '" . changeDateSlashToHypen($dateS) . "' and ";
}
if ($cdcS != "") {
    $sqlSearch .= "cdc = '" . $cdcS . "'  and ";
}
if ($ppS != "") {
    $sqlSearch .= "passport LIKE  '%$ppS%'  and ";
} 
if ($surnameS != "") {
    $sqlSearch .= "surname LIKE '" . $surnameS . "%'  and ";

In order to execute this statement the user must select all the options; the statement will not work if the user selects one or two options.

Upvotes: 0

Views: 90

Answers (2)

miken32
miken32

Reputation: 42752

Start out with a placeholder like 1=1 which will always be true, and then use AND as a prefix instead of a suffix.

$sqlSearch = "SELECT * FROM seafarers WHERE 1=1 ";

if ($dateS != "") {
    $sqlSearch .= " AND add_date = '" . changeDateSlashToHypen($dateS) . "'";
}
...

But as pointed out in the other answer you need to use prepared statements. So, assuming you're using mysqli, which everyone seems to do for some reason:

$sqlSearch = "SELECT * FROM seafarers WHERE 1=1 ";

$types = "";
$parameters = [];

if ($dateS != "") {
    $sqlSearch .= " AND add_date = ?";
    $types .= "s";
    $parameters[] = changeDateSlashToHypen($dateS);
}
if ($cdcS != "") {
    $sqlSearch .= " AND cdc = ?";
    $types .= "s";
    $parameters[] = $cdcS;
}
if ($ppS != "") {
    $sqlSearch .= " AND passport LIKE  ?";
    $types .= "s";
    $parameters[] = "%$ppS%";
} 
if ($surnameS != "") {
    $sqlSearch .= " AND surname LIKE ?";
    $types .= "s";
    $parameters[] = "$surnameS%";
}

$stmt = $db->prepare($sqlSearch);
if (count($parameters) {
    $stmt->bind_param($types, ...$parameters);
}
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    ...
}

Upvotes: 1

juergen d
juergen d

Reputation: 204934

Don't patch your query together like this. Use Prepared Statements. Example:

SELECT * 
FROM seafarers 
WHERE (:dt is null or add_date = :dt)
and (:cdc is null or cdc = :cdc)

You have to fill the parameters of the query before execution.

Upvotes: 2

Related Questions