Error returning database from php + mysqli

Navigation
0
I'm filling in the select with data from the bank it gets to the select part in the neighborhoods table and returns the ids of the city however it does not enter the loop of the clients table where it fetches the ID_UF and if I put an existing id directly in the where it returns the record but three repeats, does anyone know 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>
    
asked by anonymous 13.11.2017 / 18:23

2 answers

0

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

  • Is the ID_CITY a primary key? If it is being repeated it can be the cause of several lines with the same number in the key;
  • Assuming that the city has only one UF and its search must return only one line because the id must be unique, it is not necessary to iterate this value (foreach);

  • Have you ever considered using an INNER JOIN in your search? seems to me more functional than the many 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;
    • 13.11.2017 / 20:06
    0

    If you compare your tables in order to get everything inside select so I'll show you a code in MYSQL using JOIN clause that basically unites / concatenates tables

    Try to use this select within a While and return a feedback in my answer to see what the result was. 

    <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 just select you should only have while and query . You see ? And whenever I make a <option> dentro de um bloco de PHP você deve colocar um value também assim como fez nesta linha UF Pois assim que for selecionado UF irá ser inserido no banco de dados o valor 0. E no código que fiz acima eu indiquei que o value do é o próprio id_uf você pode alterar para o que quiser. Caso o código acima não funcione, utilize o debaixo com INNER JOIN 'and give me a feedback of what your result will be. 

    <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>
        
    13.11.2017 / 23:38
    DataGridView loads data but displays blank How to update listbox of another Form?