Uncompressed data with Internet JOIN using PDO

Question

Hello I’m having problems when I’m returning a select made with some tables Inner Join as shown below:

So I set up a Join Internet with my needs in my method:

public function tableUsuarioUM() {
    $consulta = PDOUtil::getStance()->prepare("SELECT pes.id_pessoa, pes.nome, pes.cpf, end.id_endereco, end.descricao, end.complemento, 
        end.cep,cid.id,cid.nome,cid.id_estado,est.id, est.nome, est.uf, est.id_pais FROM pessoa pes 
        INNER JOIN endereco end ON (pes.id_endereco = end.id_endereco)
        INNER JOIN cidade cid ON (end.id_endereco = cid.id)
        INNER JOIN estado est ON (cid.id = est.id)");
    $consulta->execute();
    while ($linha = $consulta->fetch(PDO::FETCH_OBJ)) { 
            echo '<thead>';
            echo '<tr>';
                echo '<td>'. $linha->nome. '</td>';
                echo '<td>'.$linha->cpf.'</td>';
                echo ' <td><a class="btn btn-primary" href="index.php?pagina=cadastrarUsuarios&id='.$linha->id_pessoa.'&nome='.$linha->nome.'&id_endereco='.$linha->id_endereco.'&cpf='.
                        $linha->cpf.'">Editar</a>';
                echo ' <a class="btn btn-danger" id="btn-apagar" href="index.php?pagina=../controller/controllerUser&id='
                .$linha->id_pessoa.'&nome='.$linha->nome.'&id_endereco='.$linha->id_endereco.'&acao=deletar">Deletar</a></td>';
            echo '</tr>';
            echo '</thead>';
    }
}

in time to bring the results he is bringing the name of the state instead of the name of the person, which may be happening.

Answer 1

In your query you have several fields with the same name in different tables, as php is not strongly typed it takes the last name and defines as the array key or object property

In the query below I left only the fields with the same name to highlight the situation:

SELECT pes.nome(1), cid.nome(2), est.nome(3) FROM pessoa pes 
        INNER JOIN endereco end ON (pes.id_endereco = end.id_endereco)
        INNER JOIN cidade cid ON (end.id_endereco = cid.id)
        INNER JOIN estado est ON (cid.id = est.id)

To get the right values in php you must set an alias for each field with the same name, this can be done with the word AS or simply set a new name after the column.

SELECT pes.nome AS p_nome, cid.nome c_nome, est.nome e_nome FROM pessoa pes 
        INNER JOIN endereco end ON (pes.id_endereco = end.id_endereco)
        INNER JOIN cidade cid ON (end.id_endereco = cid.id)
        INNER JOIN estado est ON (cid.id = est.id)

Answer 2

As @rray demonstrated what it needed would be the aliases nicknamed for some columns of some tables that are equal so the method looked like this:

public function tableUsuario() {
    $consulta = PDOUtil::getStance()->prepare("SELECT pes.id_pessoa, pes.nome as p_nome, pes.cpf, end.id_endereco, end.descricao, end.complemento, 
        end.cep, cid.id, cid.nome as c_nome, cid.id_estado, est.id, est.nome as e_nome, est.uf, est.id_pais FROM pessoa pes 
        INNER JOIN endereco end ON (pes.id_endereco = end.id_endereco)
        INNER JOIN cidade cid ON (end.id_endereco = cid.id)
        INNER JOIN estado est ON (cid.id = est.id)");
    $consulta->execute();
    while ($linha = $consulta->fetch(PDO::FETCH_OBJ)) { 
            echo '<thead>';
            echo '<tr>';
                echo '<td>'. $linha->p_nome. '</td>';

                echo '<td>'.$linha->cpf.'</td>';
                echo ' <td><a class="btn btn-primary" href="index.php?pagina=cadastrarUsuarios&id='.$linha->id_pessoa.'&nome='.$linha->p_nome.'&id_endereco='.$linha->id_endereco.'&cpf='.
                        $linha->cpf.'">Editar</a>';
                echo ' <a class="btn btn-danger" id="btn-apagar" href="index.php?pagina=../controller/controllerUser&id='
                .$linha->id_pessoa.'&nome='.$linha->p_nome.'&id_endereco='.$linha->id_endereco.'&acao=deletar">Deletar</a></td>';
            echo '</tr>';
            echo '</thead>';
    }
}

all right.