searchValue 来自 ajax 搜索栏。示例值:鸡很胖
//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);
我不明白为什么这不起作用。
我有 SQL 查询所需的正确数量的"?"。
我有bind_param函数所需的正确数量的"s"。
我有正确的搜索值格式:"鸡"、"是"、"胖"用于bind_param功能。
我的错误消息:警告:mysqli_stmt::bind_param():类型定义字符串中的元素数与 ...行号
以下是完整代码:
//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();
试试这个,它应该可以正常工作:
$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();
尝试这段代码,不要做不必要的循环
$seachValue = isset($_GET['searchValue']) ? $_GET['searchValue'] : '';
if (!$seachValue) {
exit('Empty search value');//Your error text
}
$values = explode(' ', $seachValues);
$size = count($values);
$markers = $params = array();
$markers = array_pad($markers, $size, '?');
$params = array_pad($params, $size, 's');
$mysqli = new mysqli($host, $user, $password, $database);
if ($mysqli->connect_errno) {
exit('Our apologies, having technical difficulties');
}
$query = 'SELECT results.Title, results.tags
FROM results
INNER JOIN tags
ON results.ID = tags.book_id
WHERE tags.tag
IN (' . implode(',', $markers) . ')
GROUP BY results.ID';
$stmt = $mysqli->prepare($query);
if (!$smtp) {
exit('Our apologies, having technical difficulties');//Your error text
}
$tags = array(implode($params));
for ($i = 0; $i < $size; ++$i) {
$tags[] = &$values[$i];
}
call_user_func_array(array($stmt, 'bind_param'), $tags);
$stmt->execute();
$results = $stmt->get_result();
if ($results->num_rows > 0) {
foreach ($row = $results->fetch_assoc()) {
echo sprintf('Title: %s<br>Tags: %s<br><br>', $row["Title"], $row["tags"]);
}
}
else {
echo 'Nothing found';
}
$stmt->close();
$mysqli->close();