Problems with using Ajax request

Question

When I try to make a request to return an sql query to the form fields it returns only the first row of the table, how can I change my code to fetch all rows:

select code:

<?php
class Medico 
{
 public $nome;
}

try 
{
require "conexaoMysql.php";

$medico = "";
$especialidade = "";
if (isset($_POST["especialidade"]))
  $especialidade = $_POST["especialidade"];

$SQL = "
 SELECT Nome
 FROM funcionario
 WHERE Especialidade = '$especialidade';
";

if (! $result = $conn->query($SQL))
 throw new Exception('Ocorreu uma falha ao buscar os nomes: ' . $conn-
>error);

if ($result->num_rows > 0)
{
   $row = $result->fetch_assoc();

$medico = new Medico();

$medico->nome = $row["Nome"];

} 

$jsonStr = json_encode($medico);
echo $jsonStr;

}

Code of the function making the request

function buscaNome(especialidade)
{
 $("#nomeEsp").empty();  

$.ajax({

  url: 'php/buscaNomes.php',
  type: 'POST',
  async: true,
  dataType: 'json',
  data: {'especialidade':especialidade},         

  success: function(result) {


    if (result != "")
    {                  
        var campoSelect = document.getElementById("nomeEsp");
        var option = document.createElement("option");
        option.text = result.nome;
        option.value = result.nome;
        campoSelect.add(option);

    }
  },

  error: function(xhr, status, error) {
    alert(status + error + xhr.responseText);
  }

});  

}

Note: The results are placed in a select html field

Answer 1

First the function fetch_assoc returns only one line of the resultset at a time. Then for all rows returned from the database to be accessed it is necessary to use a while loop to iterate over all rows returned by the database. It can be done like this:

//array de medicos
$medicos = [];

if ($result->num_rows > 0)
{
    while($row = $result->fetch_assoc()){
        $medico = new Medico();
        $medico->nome = $row["Nome"];
        $medicos[] = $medico;
    }
} 

$jsonStr = json_encode($medicos);
echo $jsonStr;

As can be seen in the code, it is necessary to return an array of physicians (rather than just an object of the medical class). For every iteration in the while loop is created a new medical object.

The change in the form of the return causes a change in javascript. It is now necessary to add a repetition loop to access the vector indices result. Adjusting gets:

if (result != "")
{                  
    for(var i = 0; i < result.length; i++){
        var campoSelect = document.getElementById("nomeEsp");
        var option = document.createElement("option");
        option.text = result[i].nome;
        option.value = result[i].nome;
        campoSelect.add(option);
    }

}

Serialize private variables with json_encode

If your class Medico have private attributes they will not be serialized with json_encode automatically. For this to happen you must implement the interface JsonSerializable, more about her in the php.net. In doing so it looks something like this:

    <?php
class Medico implements JsonSerializable{
    private $nome;
    public function setNome($nome){
        $this->nome = $nome;
    }

    public function jsonSerialize(){
        return ['nome' => $this->nome]; 
    }
}

$medico1=new Medico();
$medico1->setNome('nome1');
$medico2=new Medico();
$medico2->setNome('nome2');

echo json_encode([$medico1, $medico2]);

Being that the most important is not how the name parameter is sent to the class, but rather the existence of the function jsonSerialize(). When doing this is printed on the console something more or less like this:

[{"nome":"nome1"},{"nome":"nome2"}]

This should solve the Undefined problem presented with previous versions of the code.