如何使用循环绑定mysqli参数并将结果存储在数组中? [英] How to bind mysqli parameters using loop and store results in array?
问题描述
$genre = array(
'Action',
'Adventure',
'Fantasy'
);
$selectGenre_sql = 'SELECT genreID FROM genres WHERE dbGenre = ?';
if ($stmt->prepare($selectGenre_sql)) {
// bind the query parameters
$stmt->bind_param('s', $genre);
// bind the results to variables
$stmt->bind_result($genres);
// execute the query
$stmt->execute();
$array1 = array();
while ($stmt->fetch()) {
$array1[] = $genres;
}
}
当dbGenre
等于$genre
时,上面的代码从genreID
获取值.然后将结果存储在数组中.但这是行不通的,因为$genre
是一个数组,所以我需要遍历它,以便每次都从genreID
获得一个不同的值.
The code above gets the value from genreID
when dbGenre
is equal to $genre
. And then store the results in an array. But it's not working because $genre
is an array, so I need to loop through it to get a different value from genreID
each time.
类型"表包含两列: genreID(INT)和 dbGenre(VARCHAR)
The 'genres' table contains two columns: genreID (INT) and dbGenre (VARCHAR)
我只需要每个genreID(即一个数字)...假设dbGenre等于Action,然后将genreID存储在array1中,然后循环$ genre数组以获取下一个值的genreID,然后再次将其存储在array1中.
I just need each genreID (that is a number)... Let's say when dbGenre is equal to Action, then store the genreID in an array1, and then loop the $genre array to get the genreID for the next value and store it again in array1.
我该如何解决?
推荐答案
您不能将数组绑定到SQL参数.您可以在SQL中使用参数代替单个文字值.不是值列表,表达式,列名或表名.
You can't bind an array to an SQL parameter. You can use a parameter in SQL in place of a single literal value. Not a list of values, or an expression, or a column name or table name.
要解决您的情况下的任务,可以使用以下两种解决方案之一:
To solve the task in your case, you can use either of two solutions:
第一个解决方案:遍历$genre
数组,一次绑定每个值,并对每个值执行SQL查询.
First solution: loop over $genre
array, bind each value one at a time and execute the SQL query for each value.
$stmt->prepare($selectGenre_sql);
$genre = array();
foreach ($gengre as $genreID) {
$stmt->bind_param('s', $genreID);
$stmt->execute();
$stmt->bind_result($genres);
while ($stmt->fetch()) {
$genre[] = $genres;
}
}
第二个解决方案:使用多个参数一次执行查询,每个参数对应数组中的每个值.这需要一些棘手的代码才能在SQL查询中构建可变数量的?
占位符,并用逗号分隔.
Second solution: execute the query once, with multiple parameters, one for each value in the array. This requires some tricky code to build a variable number of ?
placeholders in the SQL query, separated by commas.
$selectGenre_sql = 'SELECT genreID FROM genres WHERE dbGenre IN ('
. join(',', array_fill(0, count($genre), '?')) . ')';
此外,您还需要根据$genre
数组中的元素使用可变数量的参数来巧妙地调用bind_param()
:
Also you need to get tricky calling bind_param()
with a variable number of arguments based on the elements in your $genre
array:
$stmt->prepare($selectGenre_sql);
$temp = array();
foreach ($genre as $key => $value) {
$temp[] = &$genre[$key];
}
array_unshift($genre, str_repeat('i', count($genre)));
call_user_func_array(array($stmt, 'bind_param'), $genre);
$stmt->execute();
$stmt->bind_result($genres);
$array1 = array();
while ($stmt->fetch()) {
$array1[] = $genres;
}
您可能要考虑使用PDO_MYSQL
,因为从数组绑定参数更容易.在这种情况下,MySQLi界面非常尴尬.
You might want to consider using PDO_MYSQL
because it's easier to bind parameters from an array. The MySQLi interface is pretty awkward for this case.
这篇关于如何使用循环绑定mysqli参数并将结果存储在数组中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!