Why successful results are not appearing from search bar in mysqli

Question

Mysqli code I have is not displaying the results of a successful search. The query is correct in terms of the statements as this was testing in MYSQL. But when trying to use mysqli, I can't seem the get the results to appear after a successful search. What am i doing wrong? I am getting no errors when doing error reporting and there is no misspelling in the query. I don't know if it's because query needs to outside the loop, or I am binding the parameters incorrectly or something like that but the code used to work in old mysql code but I can't get it working in mysqli (can't use PDO due to version of my PHP)

URL to application is here. If you type in "AAD", it says it can't find any results when in reality it should do because there are 5 rows in the db which contains the term 'AAD'

Below is the form code:

<form action="previousquestions.php" method="get">
      <p>Search: <input type="text" name="questioncontent" value="<?php echo $questioncontent; ?>" onchange="return trim(this)" /></p>
      <p><input id="searchquestion" name="searchQuestion" type="submit" value="Search" /></p>
      </form>

Below is the whole mysqli code and its comments

//Get terms entered in the textbox    
$questioncontent = (isset($_GET['questioncontent'])) ? $_GET['questioncontent'] : '';

$searchquestion = $questioncontent;
$terms = explode(" ", $searchquestion);

//Start of query      
$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 ";

$i=0;
//loop through each term
foreach ($terms as &$each) { 
$each = '%'.$each.'%';    
$i++;         

//if only 1 term entered then perform this LIKE statement       
if ($i == 1){         
$questionquery .= "q.QuestionContent LIKE ? ";     
} else {     
//If more than 1 term then add an OR statement    
$questionquery .= "OR q.QuestionContent LIKE ? ";    
} 
}  
//group terms by terms entered in chronilogical order                
$questionquery .= "GROUP BY q.QuestionId, q.SessionId ORDER BY "; $i = 0; foreach ($terms as $each) {     
$i++;      

//if multiple terms, then display results that contains all terms first, then start displaying other results by knocking off 1 term at a time                    
if ($i != 1)         
$questionquery .= "+";     
$questionquery .= "IF(q.QuestionContent LIKE ? ,1,0)"; 
} 

$questionquery .= " DESC "; 

//bind the parameters by the amount of terms there are      
$types = "";
for($i = 0; $i<sizeof($terms); $i++) {
$types .= "s";
}

//merge the parameters and the terms    
$eachterms = array_merge( array($types), $terms);

//prepare quert    
$stmt=$mysqli->prepare($questionquery);
//in array call all the parameters and its terms      
call_user_func_array(array($stmt, 'bind_param'), $eachterms);
//execute terms 
$stmt->execute();
//bind the result
$stmt->bind_result($dbQuestionContent); 


//display the results of the successful search
$output = "";
$output .= "
<table border='1' id='resulttbl'>
<tr>
<th class='questionth'>Question</th>
</tr>
";
while ($stmt->fetch()) {
$output .= "
<tr>
<td class='questiontd'>{$dbQuestionContent['QuestionContent']}</td>
</tr>";
}
$output .= "        </table>";

echo $output;

}

Answer 1

The reason for this error message is that $stmt is not a MySQLi_Stmt object. The reason for that is a syntax error in your statement you pass to $mysqli->prepare.

"… q.QuestionContent LIKE '%'.$each.'%' "

This results in something like:

… q.QuestionContent LIKE '%'.<value of each>.'%'

The issues here: . is not the string concatenation operator in MySQL (you would need to use +) and $each is probably not a properly quoted MySQL string declaration, so that it would result in a valid expression.

Apart from that, inserting data directly into your statement, you undermine one of the main reason to use prepared statements: to separate command and data from each other so that data cannot misinterpreted as command (i. e. SQL Injection).

So instead of inserting the string values directly, place a ? instead. Then prepare the statement and bind the parameters to it for execution:

$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 ";

$paramTypes = '';
$params = array();
$i=0;
//loop through each term
foreach ($terms as $each) {
    $i++;
    //if only 1 term entered then perform this LIKE statement
    if ($i == 1){
        $questionquery .= "q.QuestionContent LIKE ? ";
    } else {
        //If more than 1 term then add an OR statement
        $questionquery .= "OR q.QuestionContent LIKE ? ";
    }
    $params[] = "%$each%";
    $paramTypes .= "s";
}  
//group terms by terms entered in chronilogical order                
$questionquery .= "GROUP BY q.QuestionId, q.SessionId ORDER BY ";
$i = 0;
foreach ($terms as $each) {
    $i++;
    //if multiple terms, then display results that contains all terms first, then start displaying other results by knocking off 1 term at a time
    if ($i != 1) $questionquery .= "+";
    $questionquery .= "IF(q.QuestionContent LIKE ? ,1,0)";
    $params[] = "%$each%";
    $paramTypes .= "s"
}
$questionquery .= " DESC ";

//prepare query
$stmt=$mysqli->prepare($questionquery);
//bind parameters
call_user_func_array(array($stmt, 'bind_params'), array_merge(array($paramTypes), $params));
//execute query
$stmt->execute();

Oh, and again: You should really have a look at fulltext search. It can make this whole manual query building obsolete:

… WHERE MATCH (q.QuestionContent) AGAINST ("search terms" IN NATURAL LANGUAGE MODE)

Answer 2

Change the statements:

$questionquery .= "IF(q.QuestionContent LIKE '%'.$each.'%' ,1,0)";
$questionquery .= "q.QuestionContent LIKE '%'.$each.'%' ";
$questionquery .= "OR q.QuestionContent LIKE '%'.$each.'%' ";

to this (respectively):

$questionquery .= "IF(q.QuestionContent LIKE '%" . $each . "%' ,1,0)";
$questionquery .= "q.QuestionContent LIKE '%" . $each . "%' ";
$questionquery .= "OR q.QuestionContent LIKE '%" . $each . "%' ";

Also, I think you need to do this for your $stmt variable:

$stmt = $mysqli->stmt_init();
$stmt->prepare($questionquery);
//execute query
$stmt->execute();