When mysql WHERE clause is empty, return all rows

Question

$randomvariable=$_GET['randomvariable'];
$search="SELECT * from objects
          WHERE transactiontype='$randomvariable'
          order by id DESC";

Now if $randomvariable is empty (nothing), I would like it to return all rows. Currently if it's empty it returns nothing, because it basically searches for nothing from all of the rows.

Answer 1

$randomvariable = ESACPE_MYSQL_STRING($_GET['randomvariable']);
$search =
    "SELECT * FROM objects " .
    (empty($randomvariable) ? "" : "WHERE transactiontype='$randomvariable' ") .
    "ORDER BY id DESC";

Where ESCAPE_MYSQL_STRING is the relevant function for escaping strings for whatever MySQL driver you're using.

Another, more modular way:

$search = array(
    "select" => "SELECT * FROM objects",
    "where" => "WHERE transactiontype='$randomvariable'",
    "order" => "ORDER BY id DESC"
);

if (empty($randomvariable)) {
    unset($search["where"]);
}

$search = implode(' ', $search);

The nice thing about this is that you can add, remove or alter the query for any situation easily, having easy access to any part of the query.

---

You could also do this with CASE() in SQL, but it's somewhat cumbersome and you shouldn't expect good performance either:

SELECT * FROM objects
WHERE transactiontype LIKE
    CASE WHEN '$randomvariable' = '' THEN
        '%'
    ELSE
        '$randomvariable'
    END CASE
ORDER BY id DESC