Mysql query with optional where condition

Question

I have a form which is used to search the database. The form consists of four fields, including two date fields: (Col1, Col2, Datefm, DateTo). Users have the option to fill the values in these fields. They may fill the value in one field or two field or three field or all fields. Based on these values my query has to search the database.

I made a query which fetches the data, but it works only when all the values in the fields are available. I want it to exclude the where conditions which are not filled by the user. How can i achieve that?

I tried the following code.

SELECT * 
FROM Table1 
WHERE tcol1=$col1
    AND tcol2=$col2
    AND tDateto=$Dateto
    AND tdatefm=$Datefm

Answer 1

You can try using this OR function. like the one below

`SELECT *
 FROM Table1
 WHERE ($col1 IS NULL OR tcol1 = $col1)
 AND ($col2 IS NULL OR tcol2 = $col2)
 AND ($Dateto IS NULL OR tDateto = $Dateto)
 AND ($Datefm IS NULL OR tdatefm = $Datefm);`

Answer 2

As ysth has pointed out in the comments above, you should build your WHERE clause dynamically based on which form fields are populated.

Here's a very simple example which will work for the presented use case (mysqli based as your question was originally tagged with mysqli):

$where = [];
$params = [];

if (isset($_GET['Col1']) && !empty($_GET['Col1'])) {
    $where[] = 'tcol1 = ?';
    $params[] = $_GET['Col1'];
}

if (isset($_GET['Col2']) && !empty($_GET['Col2'])) {
    $where[] = 'tcol2 = ?';
    $params[] = $_GET['Col2'];
}

if (isset($_GET['Datefm']) && !empty($_GET['Datefm'])) {
    $where[] = 'tdatefm = ?';
    $params[] = $_GET['Datefm'];
}

if (isset($_GET['Dateto']) && !empty($_GET['Dateto'])) {
    $where[] = 'tDateto = ?';
    $params[] = $_GET['Dateto'];
}

$whereClause = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';

$stmt = $mysqli->prepare('SELECT * FROM Table1' . $whereClause);
$stmt->execute($params);