Column 'idusuario' in where clause is ambiguous

Navigation
3

I'm trying to make a select with inner join . Running the select in phpmyadmin the ERR that returns is this:

  

Column 'idusuario' in where clause is ambiguous 

<?php
$idusuario = $_GET['idusuario'];

    $getCli=$pdo->prepare("SELECT * FROM enderecoUsuario INNER JOIN usuario 
                            ON enderecoUsuario.idusuario = usuario.idusuario 
                            WHERE usuario.idusuario=:idusuario");
    $getCli->bindValue(":idusuario", $idusuario);
    $getCli->execute();

    while ($linha=$getCli->fetch(PDO::FETCH_ASSOC)) {

        $idusuario = $linha['idusuario'];
        $nome = $linha['nome'];
        $sobrenome = $linha['sobrenome'];
        $logradouro = $linha['logradouro'];
        $bairro = $linha['bairro'];
        $cidade = $linha['cidade'];
        $uf = $linha['uf'];
        $email = $linha['email'];

        $return = array(
            'idusuario' => $idusuario,
            'nome'  => $nome,
            'sobrenome' => $sobrenome,
            'logradouro'    => $logradouro,
            'bairro'    => $bairro,
            'cidade'    => $cidade,
            'uf'    => $uf,
            'email' => $email
        );

    }

    echo json_encode($return);
?>

    
asked by anonymous 12.09.2017 / 16:43

4 answers

11

This error means that the database does not know which column you are using / referencing because that name exists in two or more tables.

To solve this is simple just give an alias to any of the columns or in this case specify the name of the table before the field.

Change: 

SELECT * FROM enderecoUsuario
INNER JOIN usuario ON enderecoUsuario.idusuario = usuario.idusuario
WHERE idusuario=:idusuario

For something like (option with alias): 

SELECT enderecoUsuario.*, usuario.idusuario as novo_nome_id FROM enderecoUsuario
INNER JOIN usuario ON enderecoUsuario.idusuario = usuario.novo_nome_id
WHERE usuario.novo_nome_id = :idusuario

Option with full column name: 

SELECT * FROM enderecoUsuario
INNER JOIN usuario ON enderecoUsuario.idusuario = usuario.idusuario
WHERE usuario.idusuario = :idusuario
    
12.09.2017 / 16:47
5

When you search for data in more than one table and they have repeated column names you need to tell which table the column is. In case you did correct in INNER JOIN but forgot to report in WHERE .

  

Column 'idusuario' in where clause is ambiguous

Column idusuario no where is ambiguous, that is, sql does not know which table you want to filter. It needs you to report: usuario.idusuario or enderecoUsuario.idusuario in WHERE too. 

SELECT * FROM enderecoUsuario 
INNER JOIN usuario 
ON enderecoUsuario.idusuario = usuario.idusuario 
WHERE usuario.idusuario=:idusuario

Responding to your comments, it gets a bit complicated so we debug the error without having access to the database, but I'll try to make my answer more useful for you. In the link you sent idusuario=5 .

Verify that there is a user and address for this ID, since your query depends on the existence of both to return something. 

SELECT * FROM usuario WHERE idusuario= 5 -- Veja se retorna algo.
SELECT * FROM enderecoUsuario WHERE idusuario = 5 -- Veja se retorna algo.

If you want to bring all users even if there is no address change from INNER JOIN to RIGHT JOIN .

    
12.09.2017 / 16:49
2

This happens because both the 'user_letter' table and the table 'user' have the idusuario column.

You could "select" which columns you need in the query return to avoid this conflict.

Example: SELECT COLUNA_A, COLUNA_B FROM ...

    
12.09.2017 / 16:47
2

The tables usuario and enderecoUsuario have a column named idUsuario indicate which table together with the name of the column in where .

For example: usuario.idUsuario = idusuario

    
12.09.2017 / 16:47
How to make a function where every time a variable is called is added plus 1? [closed] What are files with .cpp and .h extension?