PDO MYSQL nested COUNT breaks my query

Question

I have query like this:

$query = $con->prepare("SELECT `Id`, 
                                (SELECT count(`Id`) 
                                 FROM `images` 
                                 WHERE `parentId` = :recId) as `numImgs` 
                       FROM `records`
                       WHERE `id`= :recId LIMIT 1");

$query->execute(array('recId' => $recId));

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

When I do this without the nested (SELECT Count(Id)) the query works. If I take out the SELECT(COUNT(Id)) and do it on it's own, it also works.

For some reason the above query doesn't work. I don't get any errors, just no results. However if I run the query inside phpMyAdmin, it works without any problem and returns two columns, Id and numImgs, eg:

----------------
| id | numImgs |
----------------
| 50 |   10    |
----------------

I've tested the value I'm passing, it is being correctly populated from $recId so there's no issue there. Can anyone point me in the right direction as to what's going wrong with this?

Thanks!

NOTE: this works perfectly, but I don't understand why I can't do it with one query:

try{
  $query = $con->prepare("SELECT `Id` 
                                  FROM `records`
                                  WHERE `id`= :recId
                                  AND `ownerId` = :userId
                                  LIMIT 1");
  $query->execute(array('recId' => $recId, 'userId' => $userId));
  $rec = $query->fetch(PDO::FETCH_ASSOC);
}catch(PDOException $e) {
  dump_exception('Exception selecting record.', $e);
}
if($rec){
  try{
    $picQuery = $con->prepare("SELECT COUNT(`Id`)
                                      FROM `images` 
                                      WHERE `parentId`= :recId");
    $picQuery->execute(array('recId' => $recId));
    $numPics = $picQuery->fetchColumn();
  }catch(PDOException $e) {
    dump_exception('Exception counting pictures.', $e);
  }

sorry, corrected. That's what happens when you copy and paste — ministe, Nov 12 '14 at 10:30
var_dump($query->rowCount()) gives int(0), var_dump($recId) gives string(3) "172", which is correct as the id of a record I've just inserted, var_dump $rec gives bool(false) — ministe, Nov 12 '14 at 11:21
since the query is returning false but not giving an exception, I tried dumping PDO::errorInfo but I just get "array(1) { [0]=> string(5) "00000" } " which doesn't seem particularly useful! This is the case for prepare, execute and fetch. — ministe, Nov 12 '14 at 11:26
Are you allowed to use the same parameter name (recId) more than once but only pass it once? If you used '?' as placeholders then you would need to pass two parameters in the 'execute' statement. — Ryan Vincent, Nov 12 '14 at 12:24
A good question @RyanVincent, not one I can find a definitive answer to although it looks like it should be OK. Doesn't matter either way, if I add a third parameter, I get the same results. — ministe, Nov 12 '14 at 13:33
@RyanVincent, your answer has pushed me towards the solution, find my comment in the original question. If you want to put the comment as an answer, I'll mark it as correct — ministe, Nov 12 '14 at 14:34
Thanks for updating your question with the results of your investigation. Glad to have assisted. — Ryan Vincent, Nov 13 '14 at 16:45

score 0 · Answer 1 · answered Nov 12 '14 at 12:04

0

Can't you use JOINs and GROUP BY? It would look like this if I got your question right.

SELECT `id`, COUNT(*) AS `numImgs`
FROM `records` r
INNER JOIN `images` i ON i.parentId = r.id
WHERE r.id = :recId
AND r.ownerId = :userId
GROUP BY r.id
LIMIT 1

answered Nov 12 '14 at 12:04

Paul

8,974
3
28
48

Looks like it should work, and it does in myPhpAdmin, but I'm getting the same results in the actual code :( – ministe Nov 12 '14 at 13:27
Is your MySQL log turned on? Take a look at the final query after it's sent to the db server. There must be some error in your PDO. – Paul Nov 12 '14 at 13:52
I'm using a free web host and it doesn't look like I have any access to the log :( I have enabled logging in the .htaccess file though, but that's not showing any errors – ministe Nov 12 '14 at 13:58

score 0 · Accepted Answer · answered Nov 14 '14 at 08:06

Looks like I've found the problem thanks to @RyanVincent's comment below. Whilst I have other working queries which use the same parameter more than once without it having to be passed into the "execute" array more than once, in this case, it seems that only listing the parameter once is causing the issue. I suspect that because it's a nested query, it treats it as two independent queries and as such, one has no access to the parameters of another. That's just a guess, I may be wrong, but it seems to make sense based on my results. Not only does the parameter have to be listed twice, but it must also be uniquely named otherwise you get exactly the same problem. So this code fixed the problem:

$query = $con->prepare("SELECT `Id`, 
                                (SELECT count(`Id`) 
                                 FROM `images` 
                                 WHERE `parentId` = :recIdOne) as `numImgs` 
                       FROM `records`
                       WHERE `id`= :recIdTwo LIMIT 1");

$query->execute(array('recIdOne' => $recId, 'recIdTwo' => $recId));

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

PDO MYSQL nested COUNT breaks my query

2 Answers2