How to fix mysqli warning and error

[carlbrooks]

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: &#37;s\n", 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;
            
              }
              
            }
            
            ?>

[NogDog]

I would start by validating that your call to prepare() did not fail.

[carlbrooks]

When I have done a var_dump($stmt), it does not output anything on the screen, so does this mean the prepare of the query has failed?

[NogDog]

Probably. I always do a check, just in case. At the very least:

$stmt=$mysqli->prepare($questionquery);
if($stmt == false) {
  throw new Exception($mysqli->error."\n".$questionquery);
} 

[carlbrooks]

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;
    
      }
      
    }