php-如何参数化具有多个WHERE条件的SQL查询(准备好的语句)?
问题描述:
我在WHERE子句中有多个条件,这些条件由用户输入(称为 filters ).目前,我正在以这种方式处理它们(不用担心它没有被部署):
I have multiple conditions in WHERE clause which are inputted by user (call them filters). Currently I am processing them this way (don't worry it isn't deployed):
//$by_nickname etc. are filters from $_GET
$conditions = array();
if($by_nickname !="")
{
$conditions[] = " players.lastName LIKE ('%" . $by_nickname . "%')";
}
if($by_steamid !="")
{
$conditions[] = " ids.uniqueId = '$by_steamid'";
}
if($by_ip !="")
{
$conditions[] = " players.lastAddress = '$by_ip'";
}
if($by_msg !="")
{
$conditions[] = " chat.message LIKE ('%" . $by_msg . "%')";
}
if (count($conditions) > 0)
{
$where = implode(' AND ', $conditions);
$query = "SELECT ... WHERE " . $where;
}
else
{
$query = "SELECT ... ";
}
我会用
$conditions[] = " ids.uniqueId = ?";
,依此类推.现在,我还将获得$where
,但使用?
而不是过滤器值.
and so on. Now I would also obtain $where
, but with ?
instead of filter values.
现在应该准备查询
$stmt = $mysqli->prepare("SELECT ... WHERE $where");
并参数化了这样的内容
$stmt->bind_param('ss', $by_nickname, $by_steamid);
但是我如何参数化查询某些过滤器是否为空?简单来说,我不预先知道bind_param()
方法参数.
But how do I parametrize query if some filters could be empty? Simply, I don't know the bind_param()
method arguments in advance.
答
我已经使用具有命名参数的PDO解决了我的问题.所以这是我的解决方案,希望对您有所帮助.
I have solved my problem using PDO which has named parameters. So here is my solution, hope it helps somebody.
$by_nickname = $_GET['nickname'];
$by_steamid = $_GET['steamid'];
// integer
$by_serverid = $_GET['serverid'];
$pdo = new PDO("mysql:host=host;port=port;dbname=db;charset=utf8", "user", "password");
$conditions = array();
$parameters = array();
$where = "";
if($by_nickname !="")
{
$conditions[] = " players.nickname LIKE :nickname";
$parameters[":nickname"] = "%$by_nickname%";
}
if($by_steamid !="")
{
$conditions[] = " ids.steamid = :steamid";
$parameters[":steamid"] = $by_steamid;
}
if($by_serverid !="")
{
$conditions[] = " servers.serverid = :serverid";
// Beware of correct parameter type!
$parameters[":serverid"] = intval($by_serverid);
}
if (count($conditions) > 0)
{
$where = implode(' AND ', $conditions);
}
// check if $where is empty string or not
$query = "SELECT ... " . ($where != "" ? " WHERE $where" : "");
try
{
if (empty($parameters))
{
$result = $pdo->query($query);
}
else
{
$statement = $pdo->prepare($query);
$statement->execute($parameters);
if (!$statement) throw new Exception("Query execution error.");
$result = $statement->fetchAll();
}
}
catch(Exception $ex)
{
echo $ex->getMessage();
}
foreach($result as $row)
{
// for example
echo row["<Column Name>"];
}