phpjqueryhtmlmysqlautocompletebox

Autocomplete Textbox results based from SQL database


I'm trying to create an auto-complete function into a textbox but the result should come from my SQL database.

Here's the code that i'm trying to configure:
index.php:

<html lang="en">
    <head>
        <meta charset="utf-8">
        <title>jQuery UI Autocomplete - Default functionality</title>
        <link rel="stylesheet" href="http://code.jquery.com/ui/1.10.4/themes/smoothness/jquery-ui.css">
        <script src="http://code.jquery.com/jquery-1.9.1.js"></script>
        <script src="http://code.jquery.com/ui/1.10.4/jquery-ui.js"></script>
        <link rel="stylesheet" href="/resources/demos/style.css">
        <script>
            $(function() {
                var availableTags = [
                    "autocomplete.php"; ];
                $( "#tags" ).autocomplete({
                    source: availableTags
                });
            });
        </script>
    </head>
    <body>
        <div class="ui-widget">
            <label for="tags">Tags: </label>
            <input id="tags">
        </div>
    </body>
</html>

EDIT: I changed the content of variable availableTags and made it into var availableTags = <?php include('autocomplete.php') ?>;

Variable availableTags is the source of words, so I try to change it and instead put a file name where fetching of words from my database is happening.

Here's my autocomplete.php file:

 <?php

 include('conn.php');
 $sql="SELECT * FROM oldemp";
 $result = mysqli_query($mysqli,$sql) or die(mysqli_error());

 while($row=mysqli_fetch_array($result))
 {
 echo "'".$row['name']."', ";
 }
 ?>

EDIT: Also changed the content of the while loop and made it into

$name=mysqli_real_escape_string($con,$row['name']);
$json[]=$name;

How can I insert the fetched words from autocomplete.php into availableTags variable?

EDIT/UPDATE: There's a list showing up whenever I type something on the textbox, but it has no text in it. I know it's fetching, but the word itself is not showing on the list.


Solution

  • Solved my problem.

    Have the script like this:

    <!-- WITHOUT THESE THREE BELOW, THE AUTOCOMPLETE WILL LOOK UGLY OR WILL NOT WORK AT ALL -->
    <link rel="stylesheet" href="http://code.jquery.com/ui/1.10.4/themes/smoothness/jquery-ui.css">
    <script src="http://code.jquery.com/jquery-1.9.1.js"></script>
    <script src="http://code.jquery.com/ui/1.10.4/jquery-ui.js"></script>
    
    <script>
    
      $(function() {
        $( "#tags" ).autocomplete({
          source: "autocomplete.php"
        });
      });
    
    </script>
    

    And autocomplete.php (where we will get the data to fill the autocomplete input field):

    <?php
    
      include("conn.php"); /* ESTABLISH CONNECTION IN THIS FILE; MAKE SURE THAT IT IS mysqli_* */
    
      $stmt = $con->prepare("SELECT description FROM table"); /* START PREPARED STATEMENT */
      $stmt->execute(); /* EXECUTE THE QUERY */
      $stmt->bind_result($description); /* BIND THE RESULT TO THIS VARIABLE */
      while($stmt->fetch()){ /* FETCH ALL RESULTS */
        $description_arr[] = $description; /* STORE EACH RESULT TO THIS VARIABLE IN ARRAY */
      } /* END OF WHILE LOOP */
    
      echo json_encode($description_arr); /* ECHO ALL THE RESULTS */
    
    ?>