I have a query which was correct in terms of the statements as this was tested in MYSQL. But when trying to switch from mysql to use mysqli, I can’t seem the get the results to appear after a successful search.
I am getting 2 errors though which are these:
Warning: call_user_func_array() [function.call-user-func-array]: First argument is expected to be a valid callback, ‘Array’ was given in … on line 78
Fatal error: Call to a member function execute() on a non-object in … on line 79
I don’t understand why I am getting the warning though because I am calling on an array so I don’t know why the warning appears. I am guessing the fatal error would go i the warning goes but I am not sure.
<?php
$username="zzz";
$password="zzz";
$database="mobile_app";
$mysqli = new mysqli("localhost", $username, $password, $database);
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\
", mysqli_connect_error());
die();
}
?>
<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>
<?php
if (isset($_GET['searchQuestion'])) {
$questioncontent = (isset($_GET['questioncontent'])) ? $_GET['questioncontent'] : '';
$searchquestion = $questioncontent;
$terms = explode(" ", $searchquestion);
$questionquery = "
SELECT q.QuestionId, q.QuestionContent, o.OptionType, q.NoofAnswers, GROUP_CONCAT(an.Answer ORDER BY an.Answer SEPARATOR ' ') AS Answer, r.ReplyType, q.QuestionMarks,
MATCH (q.QuestionContent) AGAINST (? IN NATURAL LANGUAGE MODE) AS score
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 MATCH (q.QuestionContent) AGAINST (? IN NATURAL LANGUAGE MODE)
GROUP BY q.QuestionId, q.SessionId
ORDER BY score
";
$paramTypes = '';
$params = array();
$i=0;
//loop through each term
foreach ($terms as $each) {
$i++;
$params[] = "%$each%";
$paramTypes .= "s";
}
$stmt=$mysqli->prepare($questionquery);
call_user_func_array(array($stmt, 'bind_param'), array_merge(array($paramTypes), $params));
$stmt->execute();
$stmt->bind_result($dbQuestionContent);
$questionnum = $stmt->num_rows();
if($questionnum ==0){
echo "<p>Sorry, No Questions were found from this Search</p>";
}
else{
$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;
}
}
?>
what is this returning? If the SQL statement fails here then it will cause the rest to fail. So it is imperative that you test for FALSE at this point before proceeding with the rest of the statements.
The rest of the code looks good and in theory should work.
If it is not an object then there must be an error in the previous line
$stmt=$mysqli->prepare($questionquery);
So there is a chance rcashell is right about checking the success of the sql preparation. You can check for a possible error by rewriting it like this
$stmt = $mysqli->prepare($questionquery) or die($mysqli->error);
I have changed my code back to the original SQL where I know the SQL is correct as it worked in the old mysql code. But since I tried changing the code to mysqli, it does not display a successful search for a correct term entered in the search box, instead it keeps stating it cannot find the term. Like I said my SQL is definitely correct but what am I doing wrong in mysql that is not allowing a successful search to occur? Is it something wrong with the num_rows() or stmt-fetch()?
<?php
//connect to db
$questioncontent = (isset($_GET['questioncontent'])) ? $_GET['questioncontent'] : '';
?>
<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>
<?php
if (isset($_GET['searchQuestion'])) {
$searchquestion = $questioncontent;
$terms = explode(" ", $searchquestion);
$questionquery = "
SELECT q.QuestionId, q.QuestionContent, o.OptionType, q.NoofAnswers, GROUP_CONCAT(an.Answer ORDER BY an.Answer SEPARATOR ' ') AS Answer, r.ReplyType,
q.QuestionMarks
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 ";
$stmt=$mysqli->prepare($questionquery)or die($mysqli->error); ;
call_user_func_array(array($stmt, 'bind_param'), array_merge(array($paramTypes), $params));
$stmt->execute();
$stmt->bind_result($dbQuestionContent);
$questionnum = $stmt->num_rows();
//if search nox is empty
if (empty($questioncontent)){
echo "Please enter in a phrase in the text box in able to search for a question";
}
//if no term was found from search
else if($questionnum ==0){
echo "<p>Sorry, No Questions were found from this Search</p>";
}
else{
//if search is sucessful
$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;
}
}
I understand how you must feel. It needs some patience to make every single part work perfectly.
num_rows will not work if the previous code doesn’t work. Make one more effort to use the alternative code I gave you in my previous post that replaces call_user_func_array()
If it doesn’t work again, take one step back. Use a very simple SQL statement to ensure everything else works.