PHP PDO and query with SELECT COUNT(*)

Question

I have a very strange issue here - it is a small workaround to the fact that PDO cannot return the num_rows in combination with MySQL.

I can feed this query directly to the database via phpmyadmin:

SELECT COUNT(*) AS COUNT
FROM ((
         (SELECT 'Key' AS tradeOrigin,
                 CONCAT(skti.tier, ' ', skty.type) AS trade,
                 CONCAT('Amount: ', t.sourceKeyAmount) AS tradeInfo,
                 'Platinum' AS tradeToOrigin,
                 t.destinationPlatinum AS tradeTo,
                 '' AS tradeToInfo,
                 u.ingame AS seller,
                 DAYSPASSED(added) AS daysPassed,
                 DATEDIFF(NOW(), added) AS sortingSince
          FROM trades t
          JOIN users u ON t.sourceItem = 1
          AND t.destinationItem = 1
          AND t.userId = u.userId
          AND t.sourceModId = 18
          JOIN keytiers skti ON t.sourceKeyTierId = skti.keyTierId
          JOIN keytypes skty ON t.sourceKeyTypeId = skty.keyTypeId)
       UNION ALL
         (SELECT 'Mod' AS tradeOrigin,
                 sm.name AS trade,
                 CONCAT('Level: ', IF(t.sourceModLevel = 0, 'Unranked', t.sourceModLevel)) AS tradeInfo,
                 'Platinum' AS tradeToOrigin,
                 t.destinationPlatinum AS tradeTo,
                 '' AS tradeToInfo,
                 u.ingame AS seller,
                 DAYSPASSED(added) AS daysPassed,
                 DATEDIFF(NOW(), added) AS sortingSince
          FROM trades t
          JOIN users u ON t.sourceItem = 2
          AND t.destinationItem = 1
          AND t.userId = u.userId
          AND t.sourceModId = 18
          JOIN mods sm ON t.sourceModId = sm.modId)) AS derived)

And it will return one row with column count and value 1 as expected.

However it goes wrong when it needs to go through my framework.

The executing code:

if (!empty($sql)) {
    try {
        echo $sql."<br><pre>";
        print_r($dataArray);
        echo "</pre>";
        $numrows = $dbh->num_rows($sql, $dataArray);
    } catch (PDOException $ex) {
//        echo $ex;
        error($ex);
    }
    //...

Output:

  (SELECT 'Key' AS tradeOrigin,
          CONCAT(skti.tier, ' ', skty.type) AS trade,
          CONCAT('Amount: ', t.sourceKeyAmount) AS tradeInfo,
          'Platinum' AS tradeToOrigin,
          t.destinationPlatinum AS tradeTo,
          '' AS tradeToInfo,
          u.ingame AS seller,
          DAYSPASSED(added) AS daysPassed,
          DATEDIFF(NOW(), added) AS sortingSince
   FROM trades t
   JOIN users u ON t.sourceItem = 1
   AND t.destinationItem = 1
   AND t.userId = u.userId
   AND t.sourceModId = :modId
   JOIN keytiers skti ON t.sourceKeyTierId = skti.keyTierId
   JOIN keytypes skty ON t.sourceKeyTypeId = skty.keyTypeId)
UNION ALL
  (SELECT 'Mod' AS tradeOrigin,
          sm.name AS trade,
          CONCAT('Level: ', IF(t.sourceModLevel = 0, 'Unranked', t.sourceModLevel)) AS tradeInfo,
          'Platinum' AS tradeToOrigin,
          t.destinationPlatinum AS tradeTo,
          '' AS tradeToInfo,
          u.ingame AS seller,
          DAYSPASSED(added) AS daysPassed,
          DATEDIFF(NOW(), added) AS sortingSince
   FROM trades t
   JOIN users u ON t.sourceItem = 2
   AND t.destinationItem = 1
   AND t.userId = u.userId
   AND t.sourceModId = :modId
   JOIN mods sm ON t.sourceModId = sm.modId)

Array
(
    [:modId] => 18
)

This goes into (Note: $this->dbh is a PDO instance):

/**
 * Returns the number of rows that this query has.
 * 
 * @param type $query   The input query
 * @param type $values  The values
 * @return type Number of rows
 */
public function num_rows($query, $values) {
    $newquery = "SELECT COUNT(*) AS count FROM (({$query}) AS derived)";
    echo $newquery;
    $statement = $this->query($newquery, $values);
    $i = $statement->fetch();
    echo "<pre>";
    print_r($i);
    echo "</pre>";
    return $i->count;
}

It echoes:

SELECT COUNT(*) AS COUNT
FROM ((
         (SELECT 'Key' AS tradeOrigin,
                 CONCAT(skti.tier, ' ', skty.type) AS trade,
                 CONCAT('Amount: ', t.sourceKeyAmount) AS tradeInfo,
                 'Platinum' AS tradeToOrigin,
                 t.destinationPlatinum AS tradeTo,
                 '' AS tradeToInfo,
                 u.ingame AS seller,
                 DAYSPASSED(added) AS daysPassed,
                 DATEDIFF(NOW(), added) AS sortingSince
          FROM trades t
          JOIN users u ON t.sourceItem = 1
          AND t.destinationItem = 1
          AND t.userId = u.userId
          AND t.sourceModId = :modId
          JOIN keytiers skti ON t.sourceKeyTierId = skti.keyTierId
          JOIN keytypes skty ON t.sourceKeyTypeId = skty.keyTypeId)
       UNION ALL
         (SELECT 'Mod' AS tradeOrigin,
                 sm.name AS trade,
                 CONCAT('Level: ', IF(t.sourceModLevel = 0, 'Unranked', t.sourceModLevel)) AS tradeInfo,
                 'Platinum' AS tradeToOrigin,
                 t.destinationPlatinum AS tradeTo,
                 '' AS tradeToInfo,
                 u.ingame AS seller,
                 DAYSPASSED(added) AS daysPassed,
                 DATEDIFF(NOW(), added) AS sortingSince
          FROM trades t
          JOIN users u ON t.sourceItem = 2
          AND t.destinationItem = 1
          AND t.userId = u.userId
          AND t.sourceModId = :modId
          JOIN mods sm ON t.sourceModId = sm.modId)) AS derived)

stdClass Object
(
    [count] => 0
)

Which calls:

/**
 * Can be called to create a query. Use either unnamed or named placeholders for the prepared statements.
 * 
 * Example: $dbh->query("INSERT INTO table (data1, data2) VALUES(?, ?)", array($data1, $data2));
 * 
 * @param type $query   The input query, including unnamed or named placeholders
 * @param type $values  The input values. If it's not an array, then it will be an one-element array
 * @return type The statement constructed by this query
 */
public function query($query, $values = array()) {
    if (!is_array($values)) {
        $values = array($values);
    }
    $statement = $this->dbh->prepare($query);
    $statement->setFetchMode(PDO::FETCH_OBJ);
    if (is_assoc($values)) {
        foreach ($values as $key => $value) {
            $statement->bindValue($key, $value);
        }
    }
    else {
        $i = 1;
        foreach ($values as $value) {
            $statement->bindValue($i++, $value);
        }
    }
    $statement->execute();
    return $statement;
}

The query method has been proven to work in the past, and also strangely enough the num_rows does work on some other arbitrarily query that returns 6 as count correctly.

I am really stuck here and have no clue what is happening, please help me out.

UPDATE:

Appereantly a setting was introducing me to this issue: $this->dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);.

However I would still like to know why it is causing the issue, as I cannot just disable it, as I am using it for the LIMIT clause which else fails (plenty of SO posts over that matter). An example of a post is the following: How to apply bindValue method in LIMIT clause?

@halfer what about *closing* an offtopic question, that plainly asks to fix the code, instead of comforting the op? — Your Common Sense, Dec 31 '13 at 13:25
If it describes what's happening in the code in English, it demonstrates a minimal understanding of the problem being solved, and therefore is not just "fix my code" or for that matter off topic. — BoltClock, Jan 01 '14 at 05:38

halfer · Accepted Answer · 2013-12-31T16:55:45.450

1

Following the discussion in the comments, it seems that you now have enough information to be getting on with: your PDO setting is preventing your binding from working.

You say that you are using that setting to get LIMIT working - I am guessing specifically binding to LIMIT. You cannot normally do this, since binding is just for parameter values (i.e. in WHERE clauses) whereas LIMIT clauses are not treated as parameters. Can you rewrite the bound LIMIT queries another way?

Lastly, as per the comments, ensure that any aliases you use are in lower case, and are not reserved words. You can probably use these case-insensitively, but code conventions are good to have anyway!

edited Dec 31 '13 at 16:55

answered Dec 31 '13 at 12:55

halfer

19,824
17
99
186

Both ways (binding and `execute()` or just `execute($data)` do exactly the same I believe from (http://www.php.net/manual/en/pdostatement.execute.php), also tested it and no change in output. – skiwi Dec 31 '13 at 12:58
Ah, yes - I saw `$this->query` and assumed this was referring to the PDO method. That's your own method though. – halfer Dec 31 '13 at 12:59
OK, so try hardwiring the 18 value into the query - it doesn't sound like that would be a problem, but worth checking nevertheless. – halfer Dec 31 '13 at 13:00
`$this->dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);` seems to be the cause of the issue, without it the counting works, however that was the work around to make limiting work with PDO. – skiwi Dec 31 '13 at 13:03
I don't know about that setting, but [this might be useful](http://stackoverflow.com/questions/10113562/pdo-mysql-use-pdoattr-emulate-prepares-or-not). I am not aware you need to change any PDO settings to get `LIMIT` to work. – halfer Dec 31 '13 at 13:05
I ended up doing it this way with `LIMIT` in the end. SQL injection is not possible on this part anyway (no user input), still I do not see my original issue, but it surely had something to do with emulate prepares. – skiwi Dec 31 '13 at 13:35

score 1 · Answer 2 · answered Dec 31 '13 at 13:04

1

$statement->bindValue($key, $value);

Did you try using $statement->bindParam instead??

because results differ, there's no problem with the Sql itself, but the problem is either in the values, or the bind.

answered Dec 31 '13 at 13:04

SmootQ

2,096
7
33
58

That caused even more problems in the past and is not what I want, I want to bind the value, this statement itself should not cause any issues. – skiwi Dec 31 '13 at 13:05

PHP PDO and query with SELECT COUNT(*)

2 Answers2