LeonardoFangueiro LeonardoFangueiro -4 years ago 280
MySQL Question

Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous

I am making a simple query but it is not working and I don't know why. I recently started becoming acquainted with PDO connections to databases.

Here is the code :


  1. The Connections is :

    define("HOST","localhost");
    define("USER","root");
    define("PASS","password");
    define("BASE","portugalforcedb");


    try{
    $conexao = 'mysql:host='.HOST.';dbname='.BASE;
    $connect = new PDO($conexao, USER, PASS);
    $connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }catch(PDOException $erro){
    echo $erro->getMessage();
    }

  2. then I create a a query that works like this :

    try{
    $query = $connect->query("SELECT N.id,N.titulo,N.texto,N.autor,N.data,J.imagem_noticia FROM noticias N JOIN jogo J ON N.jogo_id = J.id WHERE N.publicada =1 ORDER BY N.data DESC LIMIT 4");
    }catch(PDOException $erro){
    echo $erro->getMessage();
    }

    while($dados = $query->fetch(PDO::FETCH_ASSOC))

    {



but then I create another query in another page like this that doesn't work :

$id = $_GET['id'];
try{
$query = $connect->prepare("SELECT N.id,N.titulo,N.texto,N.autor,N.data,J.imagem_noticia FROM noticias N JOIN jogo J ON N.jogo_id = J.id WHERE N.publicada =1 AND id=numero");
$query->bindParam('numero',$id,PDO::PARAM_INT);
$query->execute();
}catch(PDOException $erro){
echo $erro->getMessage();
}

$dados = $query->fetch(PDO::FETCH_ASSOC);


and I tried:

$id = $_GET['id'];
try{
$query = $connect->query("SELECT N.id,N.titulo,N.texto,N.autor,N.data,J.imagem_noticia FROM noticias N JOIN jogo J ON N.jogo_id = J.id WHERE N.publicada =1 AND id=numero");
}catch(PDOException $erro){
echo $erro->getMessage();
}

while($dados = $query->fetch(PDO::FETCH_ASSOC))


but then this error appears :


SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous

Answer Source

At the end of query id=numero. id needs table alias. It should be N.id or J.id

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download