Data mismatch with inner JOIN using PDO

3

Hello I'm having problems when I'm returning a select done with inner join from some tables as below:

SoIsetupaninnerjoinwithmyneedsinmymethod:

publicfunctiontableUsuarioUM(){$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>';
    }
}

When it comes to bringing the results, it is bringing the name of the state instead of the person's name, which may be happening.

    
asked by anonymous 23.05.2015 / 20:52

2 answers

3

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 it 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 correct values in php you must define an alias for each field with the same name, this can be done with the word AS or simply define 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)
    
23.05.2015 / 21:41
1

As the @rray demonstrated what it needed would be the Alias nicknames for some columns of some tables that are the same then the method looks 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.

    
23.05.2015 / 22:50