Column 'idusuario' in Where clause is ambiguous

Question

I’m trying to make a select with inner join. Rotating the select in the phpmyadmin the ERROR 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);
?>

Answer 1

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

To solve this is simple just give an alias for one of the columns or in this case specify the table name 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

Answer 2

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 from. In case you did correct in the INNER JOIN but forgot to inform on WHERE.

Column 'idusuario' in Where clause is ambiguous

Your column in Where is ambiguous, that is, sql does not know which table you want to filter from. He needs you to inform: usuario.idusuario or enderecoUsuario.idusuario in the WHERE also.

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

Responding to your comments, it gets a little complicated we debug the error without having access to the base, but I will try to make my answer more useful to you. On the link you sent idusuario=5.

Check if there is user and address for this ID, because 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 INNER JOIN for RIGHT JOIN.

Answer 3

This happens because both in the table addressUsuario and in the table user there is the column idusuario.

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

Example : SELECT COLUNA_A, COLUNA_B FROM ...

Answer 4

The tables usuario and enderecoUsuario has a column with the name idUsuario specify which table is next to the column name in the where.

For example: usuario.idUsuario = idusuario