在PDO预准备语句中使用if else连接查询
在PDO预准备语句中使用if else连接查询
提问于 2015-01-24 18:47:12
我试图将简单的MySQLi查询转换为PDO预准备语句这里是旧代码
if($mode=='')
{
$query="SELECT * FROM items WHERE 1";
if($CuisineId != '')
{
$query=$query. " AND CuisineId='$CuisineId' ";
}
if($CategoryId != '')
{
$query=$query. " AND CategoryId='$CategoryId' ";
}
if($DietId != '')
{
$query=$query. " AND DietId='$DietId' ";
}
$query=$query. " ORDER BY Name ";
$result= execute_query($con,$query) or die(mysqli_error($con));
echo "<option value=''>Select One</option>";
while ($row=mysqli_fetch_array($result)) {
echo "<option value=". $row['Id'] .">" . $row['Name'] . "</option>";
}
}带有预准备语句的新代码看起来像这样，而且工作正常
if($mode=='')
{
$query="SELECT * FROM items WHERE 1";
if($CuisineId != '')
{
$query=$query. " AND CuisineId=? ";
}
if($CategoryId != '')
{
$query=$query. " AND CategoryId=? ";
}
if($DietId != '')
{
$query=$query. " AND DietId=? ";
}
$query=$query. " ORDER BY Name ";
$stmt = $con->prepare($query);
$stmt->execute(array($CuisineId,$CategoryId,$DietId));
$stmt->setFetchMode(PDO::FETCH_ASSOC);
echo "<option value=''>Select One</option>";
while($row = $stmt->fetch()) {
echo "<option value=". $row['Id'] .">" . $row['Name'] . "</option>";
}
}这段新代码运行得很好，但这是新代码中最糟糕的情况。如果$CuisineId、$CategoryId或$DietId为空怎么办？查询将不会连接，并将在此行上抛出错误
$stmt->execute(array($CuisineId,$CategoryId,$DietId));如果我开始做If ELSE将所有的可能性，它会使我的代码太长。有没有办法解决这个问题？
提前感谢您:)
Stack Overflow用户
发布于 2015-01-24 18:49:06
您不需要与此查询连接
SELECT *
FROM items
WHERE (? is null or CuisineId = ?)
and (? is null or CategoryId = ?)
and (? is null or DietId = ?)
ORDER BY Name页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/28124646
复制相关文章
相似问题
腾讯云开发者
