PHP 和 jQuery - 创建两个不同的文本字段，并具有从数据库检索的不同数据列表的自动完成功能

Question

Customer textfield with autocomplete from database

我成功创建了一个具有自动完成功能的客户文本字段，以显示以键入的文本开头的客户。

index.php 用于一个文本字段

              <meta charset="utf-8">
          <link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
          <script src="//code.jquery.com/jquery-1.10.2.js"></script>
          <script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
          <script>
          $(function() {
            $( "#customer" ).autocomplete({
                source: "../phpfiles/search.php",
            });
          });
          </script>



        <div class="ui-widget">
        <!-- action='./../customer_report_request' -->
            <form id="customer_report_request" name="customer_report_request" method="post">
                <table>
                    <tr>
                        <th colspan='2'>Search Customer</th>
                    </tr>
                    <tr>
                        <td>
                            <label>Customer: </label>
                            <input name="customer" id="customer" value='' required>
                        </td>
                        <td>
                            <label>Submit: </label>
                            <input value="Send" name="send_customer_request" type="submit" id="send_customer_request">
                        </td>
                    </tr>
                </table>
            </form>
        </div>

        <?php
            //Display the list of customer details
            if(isset($_POST['send_customer_request']))
            {
                include 'db.php'; //connection

                $query = "SELECT * FROM customer WHERE Company_Name = '".$_POST['customer']."'"; 
                $customer_result = $db->query($query);
                $count_customer = $customer_result->num_rows;
                if($count_customer>0)
                {
                    echo"<div>";
                    echo "<table>";
                    echo"<tr>";
                    echo"<th>Company_Name</th>";
                    echo"<th>VAT_Registration</th>";
                    echo"<th>Contact_First_Name</th>";
                    echo"<th>Contact_Last_Name</th>";
                    echo"<th>Email</th>";
                    echo"</tr>";

                    while ($row = $customer_result->fetch_assoc()) 
                    {
                        echo"<tr>";
                        echo"<td>".$row['Company_Name']."</td>";
                        echo"<td>".$row['VAT_Registration']."</td>";
                        echo"<td>".$row['Contact_First_Name']."</td>";
                        echo"<td>".$row['Contact_Last_Name']."</td>";
                        echo"<td>".$row['Email']."</td>";
                        echo"</tr>";
                    }
                    echo "</table>";
                    echo"</div>";
                }
                $db->close();
            }

        ?>

Search.php 用于一个文本字段

            <?php
        $dbHost = 'localhost';
        $dbUsername = 'bobo';
        $dbPassword = 'rodnik';
        $dbName = 'training';

        //connect with the database
        $db = new mysqli($dbHost,$dbUsername,$dbPassword,$dbName);

        //get search term
        $searchTerm = $_GET['term'];

        //get matched data from customer table

        $query = $db->query("SELECT * FROM customer WHERE Company_Name LIKE '".$searchTerm."%' ORDER BY Company_Name ASC"); //Starts with


        while ($row = $query->fetch_assoc()) {
            $data[] = $row['Company_Name'];
        }
        //return json data
        echo json_encode($data);
        ?>

问题是我想使用单个搜索 php 文件来满足其他查询。例如:

• 如果在联系人文本字段中输入单词，则查询将是“从联系人中选择 *...”
• 如果在“客户”文本字段中输入单词，则查询将是“从客户中选择 *...”

index.php 和 search.php 都经过修改以实现此目的。

修改index.php部分

定义了 jQuery 变量 component_name。 在从 index.php 文件进行更改时，客户文本字段将使用 POST 方法将变量发送到 search.php 文件，以便它可以被识别并用于查询目的。

联系人文本字段可以采用与 index.php 文件中相同的形式，也可以采用另一个 php 文件中的形式。

             <script>
              $(function() {
                $( "#customer" ).autocomplete({
                    var component_name= "customer";

                    source: "../phpfiles/search.php",
                    minLength: 1, 
                    change: function(event, ui) 
                    {
                        $.post("../phpfiles/search.php", data{post_data: component_name});
                    }
                });
              });
          </script>

修改后的search.php

        <?php
    $dbHost = 'localhost';
    $dbUsername = 'bobo';
    $dbPassword = 'rodnik';
    $dbName = 'training';
    //connect with the database
    $db = new mysqli($dbHost,$dbUsername,$dbPassword,$dbName);
    //get search term
    $searchTerm = $_GET['term'];
    //get matched data from skills table

    $query="";
    if($_POST['post_data']=="customer")
    {

        $query = $db->query("SELECT * FROM customer WHERE Company_Name LIKE '".$searchTerm."%' ORDER BY Company_Name ASC"); //Starts with
        while ($row = $query->fetch_assoc()) 
        {
            $data[] = $row['Company_Name'];
        }

        //return json data
        echo json_encode($data);
    }


    ?>

任何人都可以帮助我实现这一目标吗？

我将这些链接用于 jquery-ui 和 jquery api 部分:

• api.jquery.com
• jqueryui.com

Answer 1

这可能是一个有点复杂的广告，我希望它有所帮助。您的示例没有向您的数据库提供任何示例数据或模式，因此我不得不做出一些猜测。你需要调整。

考虑一下如果您有不同的输入字段，您可以:

HTML

<div class="ui-widget">
  <form id="customer_report_request" name="customer_report_request" method="post">
    <table>
      <tr>
        <th colspan='2'>Search Customer</th>
      </tr>
      <tr>
        <td>
          <label>Customer: </label>
          <input class="entry-field" name="customer" id="customer" value='' required>
        </td>
        <td>
          <label>Submit: </label>
          <input value="Send" name="send_customer_request" type="submit" id="send_customer_request">
        </td>
      </tr>
      <tr>
        <td>
          <label>Contact: </label>
          <input class="entry-field" name="contact" id="contact" value='' required>
        </td>
        <td>
          <label>Submit: </label>
          <input value="Send" name="send_customer_request" type="submit" id="send_ccontact_request">
        </td>
      </tr>
    </table>
  </form>
</div>

JavaScript

$(function() {
  $(".entry-field").autocomplete({
    source: function(req, resp) {
      // determine which field we're working with
      var type = $("this").attr("id");
      // collect the entry in the field
      var term = req.term;
      // Prepare our response array
      var responses = [];
      // PErform POST Request to our Search and accept JSON results
      $.ajax({
            url: "../phpfiles/search.php",
            data: {
              t: type,
              q: term
            },
            type: "POST",
            dataType: "JSON",
            success: function(results) {
              $.each(results, function(key, val) {
                responses.push(val);
              });
            }); resp(responses);
        },
        minLength: 1
    }
  });
  
  $("#customer_report_request").submit(function(e) {
    e.preventDefault();
    if ($("#customer").val().length) {
      // perform POST to a PHP search for that specific customer details
    } else {
      // performn a post to a PHP search for that specific contact details
    }
    // populate result DIV on page with resulting data from POST
  });
});

PHP:search.php

<?php
$dbHost = 'localhost';
$dbUsername = 'bobo';
$dbPassword = 'rodnik';
$dbName = 'training';
//connect with the database
$db = new mysqli($dbHost,$dbUsername,$dbPassword,$dbName);
// get search query
$searchTerm = $_POST['q'];
// get search type
$searchType = $_POST['t'];
//get matched data from customer table
if($searchType == "customer"){
    /* create a prepared statement */
    $stmt = $mysqli->prepare("SELECT * FROM customer WHERE Company_Name LIKE '?%'");
    
} else {
    /* create a prepared statement */
    $stmt = $mysqli->prepare("SELECT * FROM customer WHERE Contact_Name LIKE '?%'");
}
/* bind parameters for markers */
$stmt->bind_param("s", $searchTerm);
/* execute query */
$stmt->execute();
/* instead of bind_result: */
$result = $stmt->get_result();
while ($row = $results->fetch_assoc()) {
    if($searchType == "company"){
        $data[] = $row['Company_Name'];
    } else {
        $data[] = $row['Contact_Name']
    }
}
//return json data
header('Content-Type: application/json');
echo json_encode($data);
?>

所以发生了很多事情。将从您的 PHP 开始。它很容易受到 SQL 注入(inject)攻击，因此我使用 MySQLiPrepare 来保护东西。我们期望将数据发布到此脚本，并且期望条件:query 和 type。如果我们没有得到类型，我们可以设置默认值。可能想要添加对 query 的检查，但它应该始终有 1 个字符。

我们使用 source 选项的函数方法将此数据获取到我们的搜索脚本。查看更多:http://api.jqueryui.com/autocomplete/#option-source

Function: The third variation, a callback, provides the most flexibility and can be used to connect any data source to Autocomplete. The callback gets two arguments:

• A request object, with a single term property, which refers to the value currently in the text input. For example, if the user enters "new yo" in a city field, the Autocomplete term will equal "new yo".
• A response callback, which expects a single argument: the data to suggest to the user. This data should be filtered based on the provided term, and can be in any of the formats described above for simple local data. It's important when providing a custom source callback to handle errors during the request. You must always call the response callback even if you encounter an error. This ensures that the widget always has the correct state.

因此，我们可以添加到 $.ajax() 调用中并使用 error 回调。基本上，我们最终将一个空数组发送回响应。

因此，我们向 PHP 发送搜索词，返回 JSON 数组数据，将其通过管道传输到我们自己的数组中以发送回 response，用户将获得结果列表.

它仍然有点笨重，如果您的用户习惯的话，那也没关系。您可以精简它并对结果进行分类。这样您就可以拥有一个搜索字段。此外，一旦选择了某些内容或更改了字段，您就可以再次使用 AJAX 从另一个从数据库收集所有数据的 PHP 中提取这些详细信息。这将导致不必等待页面再次加载等。

我希望这能回答您的问题，并且我怀疑它还会引起更多问题。继续搜索，会有很多答案。有时，将大问题分解为较小的单个问题比解决整个问题更容易。