search bar is not working when using mysqli

问题:

I have an issue with a search bar I have created, I have no errors and I know my query is correct as that my search bar used to work correctly in old mysql code. But since I have try changing my code to mysqli, it hasn't quite work. No matter what the user enters in the search bar, it always states "Please enter in a phrase within the search bar", no matter if the search bar is empty or not. It doesn't display the results of the search as well. So my question is that why doesn't it show the results of the search if keyword is found or not?

Below is the current code:

        <?php
  $questioncontent = (isset($_POST['questioncontent'])) ? $_POST['questioncontent'] : '';

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


        //Query for search bar      
        $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 ";

        $i = 0;
        foreach ($terms as $each) {
            $i++;


        //If one term entered in search bar then perform a LIKE statement to look up the term entered       
            if ($i == 1) {
                $questionquery .= "q.QuestionContent LIKE ? ";
            } else {
        //If multiple terms then add an OR statement to check for multiple keywords        
                $questionquery .= "OR q.QuestionContent LIKE ? ";
            }
        }
        //Order by terms entered in ascending order they have been entered               
        $questionquery .= "GROUP BY q.QuestionId, q.SessionId ORDER BY ";
        $i = 0;
        foreach ($terms as $each) {
            $i++;

        //if there are multiple terms, then for example there are 2 terms then display content which contains both terms first, then display content which contains only one of those terms                  
            if ($i != 1)
                $questionquery .= "+";
            $questionquery .= "IF(q.QuestionContent LIKE ? ,1,0)";
        }

        $questionquery .= " DESC ";

        //prepare query, bind the terms and execute query    
        $stmt = $mysqli->prepare($questionquery);
        $stmt->bind_param('ss', $each = '%' . $each . '%', $each = '%' . $each . '%');
        $stmt->execute();
        $stmt->bind_result($dbQuestionId, $dbQuestionContent, $dbOptionType, $dbNoofAnswers, $dbAnswer, $dbReplyType, $dbQuestionMarks);
        $questionnum = $stmt->num_rows();

        //If search bar is empty and user submits the search bar, then below is the phrase it should display:



        if (empty($questioncontent)) {
            echo "Please enter in a phrase in the text box in able to search for a question";
        }

        //Below is the code if no results are found from the search:
        else if ($questionnum == 0) {
            echo "<p>Your Search: '$searchquestion'</p>";
            echo "<p>Number of Questions Shown from the Search: <strong>$questionnum</strong></p>";
            echo "<p>Sorry, No Questions were found from this Search</p>";
        }

        //Finally below is the code that displays the results of the search if search is successful:
        else {
            echo "<p>Your Search: '$searchquestion'</p>";
            echo"<p>Number of Questions Shown from the Search: <strong>$questionnum</strong></p>";

            $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>
                  <td class='addtd'><button type='button' class='add' onclick=\"parent.addwindow('{$dbQuestionContent['QuestionContent']}');\">Add</button></td>
                  </tr>";
            }
            $output .= "        </table>";

            echo $output;
        }

    ?>

Here is the link to the application it self for you to test. If you type in "AAA" in search bar, it should display results but it keeps stating that a phrase needs to be entered. IF you enter in something random so that the search should not find a result, it still states please enter in a phrase. Application

回答1:

I rewrote your code a bit, and switched to PDO for easier bindings. Try this

$searchquestion = $_GET['questioncontent'];
        $terms = explode(" ", $searchquestion);


        //Query for search bar      
        $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 ";

  $where = array();
  $bindings = array();
  $orderby=array();

    foreach($terms as $key=>$each)
    {
      $where[] = 'q.QuestionContent LIKE ? ';
      $bindings[] = $each;
      $orderby[] = ' IF(q.QuestionContent LIKE ? , 1, 0)';

    }

    $questionquery .= join(' OR ', $where);
    $questionquery .= "GROUP BY q.QuestionId, q.SessionId ORDER BY " . join('+', $orderby) . ' DESC';




     $stmt = $pdo->prepare($questionquery);
    $sth->execute(array_merge($bindings, $bindings));
    $results = $sth->fetchAll()