PHP: PDO Query returns no results, but the same query returns 4 results in phpmyadmin?

Question

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

The query looks like:

$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);

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

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

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;
}

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

You wrote: with the values 1726 for checkinId and **1527** for userId, but you bind **1517** — venca, Jun 18 '15 at 17:12
Where do you actually prepare and execute this query? Merely assigning to `$data` is not enough. — tadman, Jun 18 '15 at 17:13
I wrote a simple pdo wrapper. I know you will think it's necessary to the question but I have about 20 other queries running off the same wrapper and they return just fine. — Halfpint, Jun 18 '15 at 17:21
Any errors from the 'prepare' query and/or the execute? It may be worthwhile copying and pasting that query into an 'sql ide' to ensure that there ar no invalid characters in there. You could always supply an [SQLFiddle](http://sqlfiddle.com) with some test data and we can try the exact same code on our machines? — Ryan Vincent, Jun 18 '15 at 17:34
I get no errors what so ever. I'll build an Sql fiddle when I get back from work — Halfpint, Jun 18 '15 at 17:57

score -2 · Answer 1 · answered Jun 18 '15 at 17:11

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

I hope this will help.

PDO's placeholders are quoted if necessary. Adding quotes like this could lead to double escaping, and in any case is utterly pointless. Don't do it. — tadman, Jun 18 '15 at 17:13

PHP: PDO Query returns no results, but the same query returns 4 results in phpmyadmin?

1 Answers1

Linked