SQL Prepared statements select all result with =

January 29, 2022

So I’m using prepared statements in PHP to send a SQL command where there 5 different matching columns. The goal is that there should be an option where if only one column is selected the command should be able to run. And the options should also work if you choose all of the 5 different matching columns.

$sqlPrepared = $conn->prepare("SELECT * FROM crashes_history WHERE region = ? AND county = ? and crash_id = ?");
$sqlPrepared->bind_param("sssii", $region,  $county, $crash_id, $limit, $offset);

So what I did is that if the $region doesn’t exist we set the $region parameter to ‘region’ to select all results. But unfortunately, this doesn’t work with prepared statements.

An example where no columns are selected (fetch all data)

SELECT * FROM crashes_history WHERE region = region AND county = county and crash_id = crash_id

>Solution :

You may use a flexible prepared statement here which will ignore a given column should it be NULL:

$sql = "SELECT *
        FROM crashes_history
        WHERE region = ? OR ? IS NULL AND
              county = ? OR ? IS NULL AND
              crash_id = ? OR ? IS NULL";
$sqlPrepared = $conn->prepare($sql);
$sqlPrepared->bind_param("ssssssii", $region, $region, $county, $county, $crash_id, $crash_id, $limit, $offset);