PHP:PDO查询不返回任何结果，但是同一查询在phpmyadmin中返回4个结果?

问题描述

我写了一个查询以返回该帖子的所有评论，但不包括该帖子中被阻止的用户.我已经在phpmyadmin中测试了查询，对于给定的帖子(其中1个用户被阻止)，我得到了4/5条可能的评论.

I have written a query to return all comments for a post, excluding blocked users from that post. I have tested the query in phpmyadmin and I get 4/5 possible comments back for the given post (where 1 user is blocked).

查询如下:

$query = "SELECT ent.Entity_Id, ent.Profile_Pic_Url, ent.First_Name, ent.Last_Name, ent.Last_CheckIn_Place, comments.Content
          FROM   checkin_comments AS comments
          JOIN   entity AS ent
          ON     comments.Entity_Id = ent.Entity_Id
          LEFT JOIN friends AS f
          ON     ent.Entity_Id = :entityId
          WHERE  comments.Chk_Id = :checkInId
          AND    f.Category != 4
          GROUP BY comments.Comment_Id
          ";

// Bind the parameters to the query
$data = Array(":checkInId" => (int)$checkInId, ":entityId" => (int)$userId);

如果我在phpmyadmin上使用checkinId的值1726和userId的值1517来运行查询，则会得到预期的结果，但是在PHP中，我会得到0个结果.我用var_dump来打印数据的内容，它显示为:

If I run the query on phpmyadmin with the values 1726 for checkinId and 1517 for userId I get the expected outcome, however in PHP I get 0 results. I used var_dump to print the contents of data and it shows as:

array(2) {
[":checkInId"]=>
int(1726)
[":entityId"]=>
int(1517)
}

为什么我在PHP中遇到不同的结果?我所有其他查询都运行正常

Why am I experiencing different results in PHP? All my other queries run fine

编辑如果我将绑定变量替换为数字值，则查询工作正常，这使我认为这是PDO将值绑定到查询的问题.当执行绑定时，我使用我的PDO包装器类，该类执行以下方法:

EDIT If I swap the bind variables for number values the query works fine, which leads me to believe this is a problem with PDO binding the values to the query. When I perform the bind I use my PDO wrapper class which executes the following methods:

public function fetchAll($query, $data = null)
{
    $stmt = $this->prepareQuery($query, $data);
    return  $stmt->fetchAll();
}

private function prepareQuery($query, $data = null)
{
    $stmt = $this->connection->prepare($query);
    $stmt->execute($data);
    return $stmt;
}

这是否给PDO的更多有经验的用户带来了答案?

Does this scream the answer to any more experienced users of PDO?

推荐答案

我认为您没有在单引号中包含这些参数.试试这个代码

I think you were not including the params in single quotes. Try this code

$query = "SELECT `ent`.`Entity_Id`, `ent`.`Profile_Pic_Url`, `ent`.`First_Name`, `ent`.`Last_Name`, `ent`.`Last_CheckIn_Place`, `comments`.`Content`
      FROM   `checkin_comments` AS `comments`
      JOIN   `entity` AS `ent`
      ON     `comments`.`Entity_Id` = `ent`.`Entity_Id`
      LEFT JOIN `friends` AS `f`
      ON     `ent`.`Entity_Id` = ':entityId'
      WHERE  `comments`.`Chk_Id` = ':checkInId'
      AND    `f`.`Category` != 4
      GROUP BY `comments`.`Comment_Id`
      ";

// Bind the parameters to the query
$data = Array(":checkInId" => (int)$checkInId, ":entityId" => (int)$userId);

我希望这会有所帮助.

这篇关于PHP:PDO查询不返回任何结果，但是同一查询在phpmyadmin中返回4个结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！