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."
";
        print_r($dataArray);
        echo "";
        $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 "";
    print_r($i);
    echo "";
    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 · Accepted Answer

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!

PHP PDO and query with SELECT COUNT(*)

Answers (2)

Related Questions