Display unlisted auto-complete search results with

2020-02-15 05:15发布

站内问答 / JavaScript
384 1 6

I am trying to display an unlisted search results which is able to have a click event to a target html page based on the search from the database (mysql).

I am using PhoneGap.

So far I managed to implement a simple PHP and jQuery script that gives me a simple pop down from an auto-complete search field which completes the search. But I want the user to be able to click on it and be directed to a different page with unique information displayed from the database.

PHP:

<?php
    header("Access-Control-Allow-Origin: *");
    $con = mysqli_connect('localhost', 'root', '', 'qc_artwork') or die ("could not connect database");

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

    //get matched data from name table
    $query = $con->query("SELECT * FROM artwork WHERE name LIKE '%".$searchTerm."%' ORDER BY name ASC");
    while ($row = $query->fetch_assoc()) {
        $data[] = $row['name'];
    }

    //return json data
    echo json_encode($data);
?>

HTML:

<!DOCTYPE html>
<html>
    <head>
        <link rel="stylesheet" type="text/css" href="css/ionic.css">
        <link rel="stylesheet" href="css/jquery-ui.css">
        <script type="text/javascript" src="cordova.js"></script>
        <script type="text/javascript" src="js/jquery.js"></script>
        <script src="js/jquery-1.10.2.js"></script>
        <script src="js/jquery-ui.js"></script>
        <script type="text/javascript">
            $(document).ready(function()
            {
                $(function() {
                    $( "#searchField" ).autocomplete({
                        source: 'http://localhost/search.php'
                    });
                });
            });
        </script>
   </head>
   <body>
       <div class="bar bar-header bar-positive" style="margin-bottom:80px;">
           <a href="index.html" class="button button-clear">Home</a>
           <h1 class="title">Search Database (JSON)</h1>
       </div><br/><br/>
       <p>
           <input type="text" id="searchField" placeholder="AF artwork">
       </p>
    </body>
</html>

My SQL table consists of 5 columns:

  1. id
  2. name
  3. path
  4. barcode
  5. type

The end-goal plan is to eventually display those unique images/pdfs into the unique pages from the selected search.

But my main question is How am I able to let the user to be able to click on an unlisted result and be directed to a different page with unique information displayed from the database?

1条回答
家丑人穷心不美
2楼-- · 2020-02-15 05:38

Use the on select callback (its in the docs), include the id, label and value in your json then on click use a window.location.href to navigate to the page. You could also load the content in using an ajax call.

So for example using ajax get the content:

<!DOCTYPE html>
<html>
    <head>
        <link rel="stylesheet" type="text/css" href="css/ionic.css">
        <link rel="stylesheet" href="css/jquery-ui.css">
        <script type="text/javascript" src="cordova.js"></script>
        <script type="text/javascript" src="js/jquery.js"></script>
        <script src="js/jquery-1.10.2.js"></script>
        <script src="js/jquery-ui.js"></script>
   </head>
   <body>
       <div class="bar bar-header bar-positive" style="margin-bottom:80px;">
           <a href="index.html" class="button button-clear">Home</a>
           <h1 class="title">Search Database (JSON)</h1>
       </div><br/><br/>
       <p>
           <input type="text" id="searchField" placeholder="AF artwork">
       </p>

       <div id="result"></div>

        <script type="text/javascript">
            $(document).ready(function() {
                $(function() {
                    $( "#searchField" ).autocomplete({
                        source: './search.php',
                        minLength: 2,
                        select: function( event, ui ) {
                            $.ajax({
                                url: "./search.php",
                                method: "GET",
                                data: { 
                                  id: ui.item.id
                                }
                            }).done(function(data) {
                                $('#result').html('This is the result for: '+data.id+' - '+data.name);
                            }).fail(function(xhr) {
                                $('#result').html('Failed to load result!');
                            });
                        }
                    });
                });
            });
        </script>
    </body>
</html>

And your search.php file, would look like the following which is safe from SQL injection as it uses prepared queries:

<?php
header("Access-Control-Allow-Origin: *");

$response = function ($data = []) {
    header('Content-Type: application/json');
    exit(json_encode($data));
};   

$con = mysqli_connect('localhost', 'root', '', 'qc_artwork');

if (!$con) {
    http_response_code(500);
    $response(['error' => 'could not connect database']);
}

// auto complete search term 
if (isset($_GET['term'])) {
    //get search term
    $searchTerm = isset($_GET['term']) ? '%'.$_GET['term'].'%' : null;

    //get matched data from name table
    $stmt = $con->prepare("SELECT * FROM artwork WHERE name LIKE ? ORDER BY name ASC");
    $stmt->bind_param('s', $searchTerm);

    $result = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

    foreach ($result as $row) {
        $data[] = [
            'id' => $row['id'],    
            'label' => $row['name'],    
            'value' => $row['id'],    
        ];
    }
    $response($data);
}

// result item
if (!empty($_GET['id'])) {
    //get matched data from name table
    $stmt = $con->prepare("SELECT * FROM artwork WHERE id = ? LIMIT 1");
    $stmt->bind_param('i', $searchTerm);
    $response($stmt->get_result()->fetch(MYSQLI_ASSOC));
}
?>

Untested but should work.

查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(6)