Autocomplete: JSON + PHP

Question

With the codes below my output coming after typing "Al" is only "Albania", and the same comes duplicated in the autocomplete. The result coming in Chrome console is:

> {"0":"Albania","country_name":"Albania"}

When I run the query in the SQL client the result that comes is:

mysql> select country_name from countries where country_name like '%Al%'
    -> ;
+--------------------------------+
| country_name                   |
+--------------------------------+
| Albania                        |
| Algeria                        |
| Australia                      |
| Brunei Darussalam              |
| Central African Republic       |
| El Salvador                    |
| Equatorial Guinea              |
| Falkland Islands (Malvinas)    |
| Wallis and Futuna Islands      |
+--------------------------------+

HTML code + JSON:

<form action="" method="post">
    <input type="text" placeholder="Name" id="customerAutocomplte" class="ui-autocomplete-input" autocomplete="off" />
</form>
<script src="jquery-autocomplete/external/jquery/jquery.js" />
<script src="jquery-autocomplete/jquery-ui.min.js"</script>
<script src="jquery-autocomplete/jquery-ui.js"</script>

  $(document).ready(function($){
      $('#customerAutocomplte').autocomplete({
    source:'suggest_name.php',
    minLength:2
      });
  });

PHP code:

<?php
$server = 'localhost';
$user = 'abcdefg';
$password = '12345';
$database = 'Luca';

$mysqli = new MySQLi($server,$user,$password,$database);
/* Connect to database and set charset to UTF-8 */
if($mysqli->connect_error) {
  echo 'Database connection failed...' . 'Error: ' . $mysqli->connect_errno . ' ' . $mysqli->connect_error;
  exit;
} else {
  $mysqli->set_charset('utf8');
}
/* retrieve the search term that autocomplete sends */
$term = trim(strip_tags($_GET['term']));
$data = array();
$data = mysqli_fetch_array($mysqli->query("SELECT country_name FROM countries WHERE country_name LIKE '%$term%' ORDER BY country_name"));
echo json_encode($data);
flush();

$mysqli->close();
?>

Can you help me ? =)

Answer 1

This is because you are only getting the first record returned by the query. You have to iterate on the result:

$query = $mysqli->query("SELECT country_name FROM countries WHERE country_name LIKE '%$term%' ORDER BY country_name");

while ($arr = mysqli_fetch_array($query))
{
    $data[] = $arr;
}

echo json_encode($data);