如何动态地将参数绑定到SQL IN()子句? [英] How to bind paramenters to SQL IN() clause dynamically?
问题描述
searchValue 来自ajax搜索栏。
示例值:鸡很胖
//将搜索值转换为数组并计数元素。或者字符串中的单词
$ explode = explode('',$ searchValue); // value = Array(chickens,are,fat)
$ tags_cnt = count($ explode); // value = 3
$ cnt_q = NULL;
$ s_cnt = NULL;
($ i = 0; $ i< $ tags_cnt; $ i ++){
// $ cnt_q查找SQL
$ b $ cnt_q = $所需的'? cnt_q。 '?'
if($ i< $ tags_cnt - 1)$ cnt_q = $ cnt_q。','; // value =?,?,?
// $ s_cnt found参数绑定的'S'号
$ s_cnt = $ s_cnt。 '' // value = sss
}
//将数组转换为Bindparam的逗号分隔字符串。
$ tags = NULL;
foreach($ explode as $ tag){
$ tags = $ tags。'。$ tag。',;
}
$ tags = trim($ tags,,); // value ='Chickens','are','fat'
/ / IN子句= IN(?,?,?)
IN(。$ cnt_q。)
//准备绑定statments = $ stmt-> bind_param('sss' ,'鸡','是','胖');
$ stmt = $ conn-> prepare($ query);
$ stmt-> bind_param($ s_cnt,$ tags);
我不明白为什么这不工作。
我有SQL查询所需的正确数量的'?'。
我有正确的金额对于bind_param函数需要的's'。
我有正确的搜索值格式:'chickens','are','fat'for bind_param function
我的错误信息:
警告:mysqli_stmt :: bind_param():类型定义字符串中的元素数不匹配
$ b
以下是完整的代码:
//从ajax
$ searchValue = $ _ GET [searchValue]中检索搜索值;
// connect
$ conn = new mysqli($ host,$ user,$ password,$ database);
if($ conn-> connect_errno){
echo我们的道歉,有技术困难;
}
//分配标签,将searchValue转换为每个单词的数组
$ explode = explode('',$ searchValue);
$ tags_cnt = count($ explode);
$ cnt_q =空;
$ s_cnt = NULL;
($ i = 0; $ i <$ tags_cnt; $ i ++){
$ cnt_q = $ cnt_q。 '?'
if($ i< $ tags_cnt - 1)$ cnt_q = $ cnt_q。',';
$ s_cnt = $ s_cnt。 ''
}
$ tags = NULL;
foreach($ explode as $ tag){
$ tags = $ tags。'。$ tag。',;
}
$ tags = trim($ tags,,);
//按标签搜索
$ query =SELECT results.Title,results.tags
FROM results
INNER JOIN标签
ON results.ID = tags.book_id
WHERE tags.tag
IN(。$ cnt_q。)
GROUP BY results.ID;
$ stmt = $ conn-> prepare($ query);
$ stmt-> bind_param($ s_cnt,$ tags);
$ stmt-> execute();
$ results = $ stmt-> get_result();
while($ row = $ results-> fetch_array(MYSQLI_ASSOC)){
echoTitle:;
echo $ row [Title];
echo< / br>;
echo标签:;
echo $ row [tags];
echo< / br>< / br>;
}
$ stmt-> close();
$ conn-> close();
尝试这个,它应该可以正常工作: / p>
$ searchValue = $ _ GET [searchValue];
//连接
$ conn = new mysqli($ host,$ user,$ password,$ database);
if($ conn-> connect_errno){
echo我们的道歉,有技术困难; / / code>
//分配标签,每个单词将searchValue转换成一个数组
/ p>
$ explode = explode('',$ searchValue);
$ tags_cnt = count($ explode);
$ cnt_q = NULL;
$ s_cnt = NULL;
($ i = 0; $ i< $ tags_cnt; $ i ++){
$ cnt_q = $ cnt_q。 '?'
if($ i< $ tags_cnt - 1)$ cnt_q = $ cnt_q。',';
$ s_cnt = $ s_cnt。 '';
}
$ tags [] =& $ s_cnt;
for($ a = 0; $ a< $ tags_cnt; $ a ++){
$ tags [] =& $ explode [$ a];
}
//按标签搜索
$ query =SELECT results.Title,results.tags
FROM results
INNER JOIN标签
ON results.ID = tag.book_id
WHERE tags.tag
IN(。$ cnt_q。)
GROUP BY results.ID;
$ stmt = $ conn-> prepare($ query);
call_user_func_array(array($ stmt,'bind_param'),$ tags);
$ stmt-> execute();
$ results = $ stmt-> get_result ()
while($ row = $ results-> fetch_array(MYSQLI_ASSOC)){
echoTitle:;
echo $ row [Title];
echo< / br>;
echo标签:;
echo $ row [tags];
echo< / br>< / br>;
}
$ stmt-> close();
$ conn-> close();
searchValue comes from ajax search bar. Example value: Chickens are fat
//Turns search value into array and counts elements. Or words in the string
$explode = explode(' ', $searchValue); //value = Array (chickens, are, fat)
$tags_cnt = count($explode); //value = 3
$cnt_q = NULL;
$s_cnt = NULL;
for ($i=0; $i<$tags_cnt; $i++) {
//$cnt_q finds number of '?' needed for SQL
$cnt_q = $cnt_q. '?';
if ($i<$tags_cnt - 1) $cnt_q = $cnt_q.','; //value = ?,?,?
//$s_cnt finds Number of 'S' for paramater binding
$s_cnt = $s_cnt. 's'; //value = sss
}
//Turns the Array into comma separated string for Bindparam.
$tags=NULL;
foreach ($explode as $tag) {
$tags = $tags."'".$tag."',";
}
$tags = trim($tags, ",");// value = 'Chickens', 'are', 'fat'
//The IN clause = IN(?,?,?)
IN (".$cnt_q.")
//prepared binding statments = $stmt->bind_param('sss','chickens','are','fat');
$stmt = $conn->prepare($query);
$stmt->bind_param($s_cnt, $tags);
I can't see why this is not working.
I have the correct amount of '?' needed for the SQL query.
I have the correct amount of 's' needed for the bind_param function.
I have the correct format of the search value: 'chickens', 'are', 'fat' for the bind_param function.
My error message: Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables in ... line number
Below is full code:
//retrive search value from ajax
$searchValue=$_GET["searchValue"];
//connect
$conn = new mysqli($host, $user, $password, $database);
if ($conn->connect_errno) {
echo "Our apologies, having technical difficulties";
}
//assign tags, turn searchValue into a array by each word
$explode = explode(' ', $searchValue);
$tags_cnt = count($explode);
$cnt_q = NULL;
$s_cnt = NULL;
for ($i=0; $i<$tags_cnt; $i++) {
$cnt_q = $cnt_q. '?';
if ($i<$tags_cnt - 1) $cnt_q = $cnt_q.',';
$s_cnt = $s_cnt. 's';
}
$tags=NULL;
foreach ($explode as $tag) {
$tags = $tags."'".$tag."',";
}
$tags = trim($tags, ",");
//search by Tags
$query = " SELECT results.Title, results.tags
FROM results
INNER JOIN tags
ON results.ID = tags.book_id
WHERE tags.tag
IN (".$cnt_q.")
GROUP BY results.ID";
$stmt = $conn->prepare($query);
$stmt->bind_param($s_cnt, $tags);
$stmt->execute();
$results = $stmt->get_result();
while($row = $results->fetch_array(MYSQLI_ASSOC)) {
echo "Title: ";
echo $row["Title"];
echo "</br>";
echo "Tags: ";
echo $row["tags"];
echo "</br></br> ";
}
$stmt->close();
$conn->close();
Try this one, it should work just fine:
$searchValue=$_GET["searchValue"];
//connect
$conn = new mysqli($host, $user, $password, $database);
if ($conn->connect_errno) {
echo "Our apologies, having technical difficulties"; }
//assign tags, turn searchValue into a array by each word
$explode = explode(' ', $searchValue);
$tags_cnt = count($explode);
$cnt_q = NULL;
$s_cnt = NULL;
for ($i=0; $i<$tags_cnt; $i++) {
$cnt_q = $cnt_q. '?';
if ($i<$tags_cnt - 1) $cnt_q = $cnt_q.',';
$s_cnt = $s_cnt. 's';
}
$tags[] = & $s_cnt;
for ($a=0; $a<$tags_cnt; $a++) {
$tags[] = &$explode[$a];
}
//search by Tags
$query = " SELECT results.Title, results.tags
FROM results
INNER JOIN tags
ON results.ID = tags.book_id
WHERE tags.tag
IN (".$cnt_q.")
GROUP BY results.ID";
$stmt = $conn->prepare($query);
call_user_func_array(array($stmt, 'bind_param'), $tags);
$stmt->execute();
$results = $stmt->get_result();
while($row = $results->fetch_array(MYSQLI_ASSOC)) {
echo "Title: ";
echo $row["Title"];
echo "</br>";
echo "Tags: ";
echo $row["tags"];
echo "</br></br> ";
}
$stmt->close();
$conn->close();
这篇关于如何动态地将参数绑定到SQL IN()子句?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!