duanhanzi8328
duanhanzi8328
2015-09-30 16:43

php - 如何使用多个WHERE条件(预准备语句)参数化SQL查询?

已采纳

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 ... ";
}

Instead of this I would use

$conditions[] = " ids.uniqueId = ?";

and so on. Now I would also obtain $where, but with ? instead of filter values.

Query should be now prepared

$stmt = $mysqli->prepare("SELECT ... WHERE $where");

and parametrized something like this

$stmt->bind_param('ss', $by_nickname, $by_steamid);

But how do I parametrize query if some filters could be empty? Simply, I don't know the bind_param() method arguments in advance.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • dqdes60666 dqdes60666 6年前

    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>"];
    }
    
    点赞 评论 复制链接分享