使用IN指令搜索准备好的语句 [英] Use the IN directive to search with a prepared statement

问题描述

我的目的是在表中找到与存储在String中的集合匹配的所有项目:

My purpose is to find all the item in the table that match a collection stored in a String:

$array=array("item1","item2","item3","item4");//This is dynamically filled, this is just an example
$in_list = "'".implode("','",$array)."'";//that's why i use implode

$stmt = $this->db->prepare('SELECT libelle,activite,adresse,tel,lat,lng FROM etablissements where type IN ('.$in_list.')');

        $stmt->bind_param("s", $in_list);
        $stmt->execute();
        $stmt->bind_result($libelle,$activite,$adresse,$tel,$lat,$lng);

这似乎并不完美，它总是向我发出以下警告:

That seems not to work perfectly, it always throwing me the following warning:

mysqli_stmt::bind_param() [<a href='mysqli-stmt.bind-param'>mysqli-stmt.bind-param</a>]: Number of elements in type definition string doesn't match number of bind variables in <b>/homepages/25/d399726988/htdocs/

如果我有一个参数，这将是显而易见的:

If i had one parameter, it would be obvious:

    $stmt = $this->db->prepare('SELECT libelle,activite,adresse,tel,lat,lng FROM etablissements where type =?');
$stmt->bind_param("s", $parameter);

但是由于我不得不处理这个问题，这对我来说有点复杂.

But since i have to deal with that, it became a bit complicated to me.

推荐答案

prepared语句没有参数，因为您在准备列表之前已将列表插入到该语句中.

The prepared statement has no parameters because you have interpolated the list into the statement before preparing it.

$array=array("item1","item2","item3","item4");
//This is dynamically filled, this is just an example
$in_list = "'".implode("','",$array)."'";//that's why i use implode

$stmt = $this->db->prepare('SELECT libelle,activite,adresse,tel,lat,lng FROM etablissements where type IN ('.$in_list.')');

此时，您创建的SQL语句为:

At this point, the SQL statement you have created is:

SELECT libelle,activite,adresse,tel,lat,lng 
FROM etablissements where type IN ('item1','Item2','Item3','Item4')

由于该语句没有参数，因此mysqli_stmt::bind_param失败.而不是将项目插值到语句(容易注入)中，而是插值一串参数，然后绑定值(必须保持分开).

Since the statement has no parameters, mysqli_stmt::bind_param fails. Instead of interpolating the items into the statement (which is vulnerable to injection), interpolate a string of parameters, then bind the values (which must be kept separate).

$array=array("item1","item2","item3","item4");

if (count($in_list) > 0) {
    $query = $this->db->prepare('SELECT libelle,activite,adresse,tel,lat,lng FROM etablissements WHERE type IN (' . str_repeat('?, ', count($in_list)-1) . '?)');

    $args = $in_list;
    array_unshift($args, str_repeat('s', count($in_list)));
    call_user_func_array(array($query, 'bind_param'), $args);
    $query->execute();
    $query->bind_result($libelle,$activite,$adresse,$tel,$lat,$lng);
}

PDO的绑定界面更简单.

PDO's interface for binding is more straightforward.

$array=array("item1","item2","item3","item4");

if (count($in_list) > 0) {
    $query = $this->db->prepare('SELECT libelle,activite,adresse,tel,lat,lng FROM etablissements WHERE type IN (' . str_repeat('?, ', count($in_list)-1) . '?)');

    foreach ($in_list as $i => $arg) {
        // query params are 1-based, so add 1 to the index
        // PDO::PARAM_STR is the default type, so no need to pass 3rd arg
        $query->bindValue($i+1, $arg);
    }
    $query->execute();
    // no need to bind the result
}

实际上，使用PDO甚至可以更简单，因为 PDOStatement::execute 可以列出参数值:

In fact, it can be even simpler with PDO, since PDOStatement::execute can take a list of parameter values:

$array=array("item1","item2","item3","item4");

if (count($in_list) > 0) {
    $query = $this->db->prepare('SELECT libelle,activite,adresse,tel,lat,lng FROM etablissements WHERE type IN (' . str_repeat('?, ', count($in_list)-1) . '?)');

    $query->execute($in_list);
}

这篇关于使用IN指令搜索准备好的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！