使用mysqli时搜索栏不起作用 [英] search bar is not working when using mysqli

问题描述

我创建的搜索栏有问题，没有错误，我知道我的查询是正确的，因为我的搜索栏以前在旧的mysql代码中可以正常工作.但是由于我已经尝试将代码更改为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?

下面是当前代码:

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

    ?>

这是您自己测试的应用程序的链接.如果您在搜索栏中输入"AAA"，它会显示结果，但会一直提示需要输入短语.如果您随机输入某些内容，以使搜索找不到结果，则仍请输入短语. 应用程序

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

推荐答案

我稍微重写了您的代码，并切换到PDO以简化绑定. 试试这个

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()

这篇关于使用mysqli时搜索栏不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！