how to bind parameters correctly in example below in mysqli?
- by user1421767
In old mysql code, I had a query below which worked perfectly which is below:
$questioncontent = (isset($_GET['questioncontent'])) ? $_GET['questioncontent'] : '';
$searchquestion = $questioncontent;
$terms = explode(" ", $searchquestion);
$questionquery = "
SELECT q.QuestionId, q.QuestionContent, o.OptionType, an.Answer, r.ReplyType,
FROM Answer an
INNER JOIN Question q ON q.AnswerId = an.AnswerId
JOIN Reply r ON q.ReplyId = r.ReplyId
JOIN Option_Table o ON q.OptionId = o.OptionId
WHERE ";
foreach ($terms as $each) {
$i++;
if ($i == 1){
$questionquery .= "q.QuestionContent LIKE `%$each%` ";
} else {
$questionquery .= "OR q.QuestionContent LIKE `%$each%` ";
}
}
$questionquery .= "GROUP BY q.QuestionId, q.SessionId ORDER BY "; $i = 0; foreach ($terms as $each) {
$i++;
if ($i != 1)
$questionquery .= "+";
$questionquery .= "IF(q.QuestionContent LIKE `%$each%` ,1,0)";
}
$questionquery .= " DESC ";
But since that old mysql is fading away that people are saying to use PDO or mysqli (Can't use PDO because of version of php I have currently got), I tried changing my code to mysqli, but this is giving me problems. In the code below I have left out the bind_params command, my question is that how do I bind the parameters in the query below? It needs to be able to bind multiple $each because the user is able to type in multiple terms, and each $each is classed as a term.
Below is current mysqli code on the same query:
$questioncontent = (isset($_GET['questioncontent'])) ? $_GET['questioncontent'] : '';
$searchquestion = $questioncontent;
$terms = explode(" ", $searchquestion);
$questionquery = "
SELECT q.QuestionId, q.QuestionContent, o.OptionType, an.Answer, r.ReplyType,
FROM Answer an
INNER JOIN Question q ON q.AnswerId = an.AnswerId
JOIN Reply r ON q.ReplyId = r.ReplyId
JOIN Option_Table o ON q.OptionId = o.OptionId
WHERE ";
foreach ($terms as $each) {
$i++;
if ($i == 1){
$questionquery .= "q.QuestionContent LIKE ? ";
} else {
$questionquery .= "OR q.QuestionContent LIKE ? ";
}
}
$questionquery .= "GROUP BY q.QuestionId, q.SessionId ORDER BY "; $i = 0; foreach ($terms as $each) {
$i++;
if ($i != 1)
$questionquery .= "+";
$questionquery .= "IF(q.QuestionContent LIKE ? ,1,0)";
}
$questionquery .= " DESC ";
$stmt=$mysqli->prepare($questionquery);
$stmt->execute();
$stmt->bind_result($dbQuestionId,$dbQuestionContent,$dbOptionType,$dbAnswer,$dbReplyType);
$questionnum = $stmt->num_rows();
