Search code examples
phpjquerymysqljquery-ui-autocomplete

Jquery autocomplete function wont autocomplete with PHP, Mysql data


I'm trying to make an autocomplete search function with JQuery but the results are not being displayed. In the browser console I can see the requests are working:

<code>enter image description here</code>

This is my Search function

public function getAllartikelSearch()
{
    $statement = $this->connection->prepare("SELECT * FROM lievers_serienummers.artikel where artikelNummer like :keyword");
    $statement->bindValue('keyword', '%' . $_GET['term'] . '%');
    $statement->execute();
    $result = array();
    while($product = $statement->fetch(PDO::FETCH_OBJ)) {
        array_push($result, $product->artikelNummer);
    }
    echo json_encode($result);
}

This gets called by search.php

<?php
require_once 'database.php';
require_once 'autoloader.php';

$overzichtmanager = new overzichtmanager();
$overzichtmanager->getAllartikelSearch();
?>

And here is the autocomplete code:

<script>
    $(document).ready(function () {
        $('#tags').autocomplete({
            source: 'search.php'
        });
    });
</script>

<body>

<header>
    <h1>Toevoegen</h1>
</header>
<div class="ui-widget">
    <label for="tags">Tags: </label>
    <input id="tags" name="term"/>
    <div id="countryList"></div>
</div>

Solution

  • You should not be using echo from a method, only return. You should not output anything, except from your controller logic. Also avoid using global variables inside the method, instead pass the value as a parameter. As well, no need for a loop when you can use PDO's fetchAll() function.

    public function getAllartikelSearch($term)
    {
        $sql = "SELECT artikelNummer FROM artikel where artikelNummer like :keyword";
        $statement = $this->connection->prepare($sql);
        $statement->execute(["keyword" => "%$term%"]);
        $result = $statement->fetchAll(PDO::FETCH_COLUMN, 0);
        return json_encode($result);
    }
    

    Now search.php can look like this, setting the proper headers and outputting the JSON to the script. Don't close the PHP code unless you have HTML after it in the same file.

    <?php
    require_once 'database.php';
    require_once 'autoloader.php';
    
    $overzichtmanager = new overzichtmanager();
    $json = $overzichtmanager->getAllartikelSearch($_GET["term"]);
    header("Content-Type: application/json");
    echo $json;
    

    Give this a try and it should work well. I suspect the lack of header was causing problems, or possibly other data being output before or after the JSON. If problems continue, check the JS console in your browser for error messages and edit your question to include them.