Error returning data from php + mysqli database

Question

I am Filling the select with database data it reaches up to the part of the select in the neighborhood table and returns the city ids but it does not enter the loop of the clients table where it searches the ID_UF and if I put an existing id directly in Where it returns the record but three repeated,someone knows what it can be?

<select id="uf" name="uf" onchange="carrega(this.value)"; class="selectoso">
    <option value="0" >UF</option>
    <?php

    $querya = mysqli_query($con,"SELECT ID_ENDERECO FROM pessoas WHERE TIPO='Anfitriao'");
    while($resultadoa = mysqli_fetch_array($querya))
    {

        $retornaidEndereco = $resultadoa['ID_ENDERECO'];
        $queryb = mysqli_query($con, "SELECT  ID_BAIRRO  FROM enderecos WHERE ID_ENDERECO='.$retornaidEndereco.'");
        while($resultadob = mysqli_fetch_array($queryb))
        {
            $retornaidBai = $resultadob['ID_BAIRRO'];


            $queryc = mysqli_query($con, "SELECT ID_CIDADE FROM bairros WHERE ID_BAIRRO='.$retornaidBai.'");
            while($resultadoc = mysqli_fetch_array($queryc))
            {

                $retornaidCid = $resultadoc['ID_CIDADE'];


                $queryd = mysqli_query($con, "SELECT ID_UF FROM cidades WHERE ID_CIDADE='.$retornaidCid.'");


                while($resultadod = mysqli_fetch_array($queryd))
                {


                    $retornaidUF = $resultadod['ID_UF'];

                    echo '<option >'.$retornaidUF.'</option>';

                }
            }

        }
    }
    ?>
</select>

Answer 1

I have some suggestions that Voce will have to analyze based on what Voce is doing and analyzing your bank:

1. ID_CIDADE is a primary key? If it is repeating itself it may be the cause of several lines with the same number in the key;
2. assuming that the city has only one UF and its search should return only one line because the id must be unique, it is not necessary to iterate this value (foreach);

3. Have you considered the possibility of using an INNER JOIN in your search? it seems more functional than the numerous requests to the database to bring a single element, try this query:

   SELECT cid.ID_UF from pessoas pes 
   INNER JOIN enderecos ende 
   INNER JOIN bairros bai 
   INNER JOIN cidades cid 
   where pes.TIPO = 'anfitriao' 
   and ende.ID_ENDERECO = pes.ID_ENDERECO  
   and bai.ID_BAIRRO = ende.ID_BAIRRO 
   and cid.ID_CIDADE = bai.ID_CIDADE;
   

Answer 2

If you want to compare your tables in order to bring everything in select so I’ll show you a code on MYSQL using clause JOIN that basically unites / concatenates tables

Try using this select inside a While and return me a feedback in my reply to know what was the result.

<select id="uf" name="uf" onchange="carrega(this.value)"; class="selectoso">
       <option></option>
       <option value="0">UF</option>

    <?php

       $query = "SELECT * FROM A.id_endereco, A.tipo, B.id_bairro, C.id_cidade, D.id_uf FROM pessoa A"
           . " LEFT OUTER JOIN endereco B ON (A.id_endereco = B.id_endereco)"
           . " LEFT OUTER JOIN bairro C ON (B.id_endereco = C.id_endereco)"
           . " LEFT OUTER JOIN cidade D ON (C.id_endereco = D.id_cidade) WHERE A.tipo = 'Anfitrião' ;

       $resultado = mysqli_query($con, $query);

       while($row = mysqli_fetch_assoc($resultado)) {
           echo '<option value="'.$row['id_uf'].'">'.$row['id_uf'].'</option>';
       }

    ?>
</select>

Note that your select has 4 while and 4 selects with querys so for only one select you must have only one while and a query. Do you understand ? And whenever you make one <option> dentro de um bloco dePHPvocê deve colocar umvaluetambém assim como fez nesta linhaUFPois assim que for selecionadoUFirá ser inserido no banco de dados o valor 0. E no código que fiz acima eu indiquei que ovaluedoé o próprioid_ufvocê pode alterar para o que quiser. Caso o código acima não funcione, utilize o debaixo comINNER JOIN` and give me feedback on what will be your result.

<select id="uf" name="uf" onchange="carrega(this.value)"; class="selectoso">
       <option></option>
       <option value="0">UF</option>

    <?php

       $query = "SELECT * FROM pessoas A
             . " INNER JOIN enderecos B"
             . " INNER JOIN bairros C" 
             . " INNER JOIN cidades D WHERE A.tipo = 'Anfitriao'" 
             . " AND B.id_endereco = A.id_endereco" 
             . " AND C.id_bairro = B.id_bairro" 
             . " AND D.id_cidade = C.id_cidade";

       $resultado = mysqli_query($con, $query);

       while($row = mysqli_fetch_assoc($resultado)) {
           echo '<option value="'.$row['id_uf'].'">'.$row['id_uf'].'</option>';
       }

    ?>
</select>