CODEWITHSUNDEEP
phpmysql
rns
rns

Reputation: 92

Selecting rows where out of two columns one is null

Out of two search selections if a visitor select one only there is no search result. Following is my sql query:

$sql = "SELECT * FROM table WHERE  column1='$column1' AND column2 ='$column2' ORDER BY id DESC

If I use 'OR' or otherwise I got wrong result in pagination. What should be right coding if a visitor opted only one criteria to search he will get result in first and subsequent pages?

Upvotes: 0

Views: 38

Answers (2)

capcj
capcj

Reputation: 1535

A fast solution is that you can put the filters into a variable checking if the values of $column1 or $column2 it's filled and add after that in the SELECT clause:

$where_column = 'WHERE ';
if ($column1 != false)
    $where_column .= "column1='$column1'";

if ($column2 != false) {
    if ($where_column != 'WHERE') {
        $where_column .= "AND column2='$column2'";
    else
        $where_column = "column2='$column2'";
    }
}

$sql = "SELECT * FROM table $where_column ORDER BY id DESC

Upvotes: 0

Justinas
Justinas

Reputation: 43441

In PHP construct your query:

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

if (!empty($column1)) {
    $where[] = 'column1 = :column1';
    $params[':column1'] = $column1;
} else {
    $where[] = 'column1 IS NULL';
}

if (!empty($column2)) {
    $where[] = 'column2 = :column2';
    $params[':column2'] = $column2;
} else {
    $where[] = 'column2 IS NULL';
}

if (!empty($where)) {
    $pdo
       ->prepare("SELECT * FROM table WHERE ".implode(' AND ', $where))
       ->execute($params);
}

If you allow selection only by one column, remove else parts

Upvotes: 2

Related Questions