如何动态地将参数绑定到SQL IN（）子句？

Question

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();

Answer 1

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();