php 违反完整性约束：where 子句中的 1052 列“id”不明确

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

提问by LeonardoFangueiro

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.

我正在做一个简单的查询，但它不起作用，我不知道为什么。我最近开始熟悉与数据库的 PDO 连接。

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))
   
   {
   

1. 连接是：

   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. 然后我创建一个像这样工作的查询：

   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

SQLSTATE[23000]：违反完整性约束：1052 where 子句中的列“id”不明确

回答by AHHP

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

在查询结束时id=numero。id 需要表别名。它应该是 N.id 或 J.id