PHP和MYSQLi - 使用循环和存储在数组绑定参数? [英] PHP and MYSQLi - Bind parameters using loop and store in array?
问题描述
这将是更容易解释下面的代码(这是错误的,顺便说一下):
$ selectGenre_sql = SELECT genreID FROM genres WHERE dbGenre =?';
if($ stmt-> prepare($ selectGenre_sql)){
//绑定查询参数
$ stmt-> bind_param('s',$ genre) ;
//将结果绑定到变量
$ stmt-> bind_result($ genres);
//执行查询
$ stmt-> execute();
$ genre = array();
while($ stmt-> fetch()){
$ genre [] = $ genres;
}
}
上面的代码从'genreID' 'dbGenre'等于'$ genre'。然后将结果存储在数组中。但我不工作。为什么?我想因为'$ genre'是一个数组,所以我需要循环槽,以获得一个不同的值从'genreID'每次。
$ genre是枚举包含电影类型的数组,例如:
[0] => Action [1] => Adventure [2] => Fantasy p>
我需要比较de value(例如'Action')
'genres'表包含两列: genreID(INT)和 dbGenre(VARCHAR)
我只需要每个genreID一个数字)....让我们说,当dbGenre等于Action,然后将genreID存储在array1中,然后循环$ genre数组获得下一个值的genreID,并再次存储在array1
如何解决?我是新来的编程,所以请尽可能详细,你可以。谢谢!
您不能将数组绑定到SQL参数。您可以在SQL中使用参数代替单个文字值。
要解决您的情况下的任务,您可以使用两种解决方案之一:
/ p>第一个解决方案:循环 $ genre
数组,每次绑定一个值,并执行SQL查询每个值。
if($ 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查询中构建一个变量数量的?
占位符,用逗号分隔。
$ selectGenre_sql ='SELECT genreID FROM genres WHERE dbGenre IN('
。join(',',array_fill(0,count($ genre),'?'))。 )';
if($ stmt-> prepare($ selectGenre_sql)){
$ genre = array();
。 。 。
此外,你还需要调用 bind_param()
使用基于 $ genre
数组中的元素的可变数量的参数:
。 。 。
call_user_func_array(array($ stmt,'bind_param'),
array_unshift($ genre,str_repeat('i',count($ genre)));
$ stmt- > execute();
$ stmt-> bind_result($ genres);
while($ stmt-> fetch()){
$ genre [] = $ genres;
}
}
考虑使用 PDO :: mysql
,因为从数组绑定参数更容易,MySQLi接口对于这种情况非常尴尬。
It will be easier to explain with the next code (It's wrong, by the way):
$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();
$genre = array();
while ($stmt->fetch()) {
$genre[] = $genres;
}
}
The code above gets the value from 'genreID' when 'dbGenre' is equal to '$genre'. And then store the results in an array. But i'ts not working. Why? I think because '$genre' is an array, so I need to loop trough it to get a different value from 'genreID' each time.
$genre is an enumerated array containing movie genres, for example:
[0] => Action [1] => Adventure [2] => Fantasy
I need to compare de value (like 'Action' for example)
The 'genres' table contains two columns: genreID (INT) and dbGenre (VARCHAR)
I just need each genreID (that is a number).... Lets 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
How can I fix it?? I'm new to programming so please be as detailed as you can. Thanks!!
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:
First solution: loop over $genre
array, bind each value one at a time, and execute the SQL query for each value.
if ($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;
}
}
}
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), '?')) . ')';
if ($stmt->prepare($selectGenre_sql)) {
$genre = array();
. . .
Also you need to get tricky calling bind_param()
with a variable number of arguments based on the elements in your $genre
array:
. . .
call_user_func_array( array($stmt, 'bind_param'),
array_unshift($genre, str_repeat('i', count($genre)));
$stmt->execute();
$stmt->bind_result($genres);
while ($stmt->fetch()) {
$genre[] = $genres;
}
}
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.
这篇关于PHP和MYSQLi - 使用循环和存储在数组绑定参数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!