当mysql WHERE子句为空时，返回所有行 [英] When mysql WHERE clause is empty, return all rows

问题描述

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

现在，如果$randomvariable为空(无)，我希望它返回所有行.当前，如果为空，则不返回任何内容，因为它基本上不从所有行中搜索任何内容.

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.

推荐答案

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

其中ESCAPE_MYSQL_STRING是用于为所使用的任何MySQL驱动程序转义字符串的相关函数.

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.

您也可以使用 CASE() SQL，但这有点麻烦，您也不应该期望获得良好的性能:

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

这篇关于当mysql WHERE子句为空时，返回所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！