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
– Your Common Sense, Commented Dec 31, 2013 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
– BoltClock, Commented Jan 1, 2014 at 5:38

halfer · Accepted Answer · 2013-12-31 16:55:45Z

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, 2013 at 16:55

answered Dec 31, 2013 at 12:55

halfer

20.2k20 gold badges110 silver badges207 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

skiwi Over a year ago

Both ways (binding and execute() or just execute($data) do exactly the same I believe from (php.net/manual/en/pdostatement.execute.php), also tested it and no change in output.

halfer Over a year ago

Ah, yes - I saw $this->query and assumed this was referring to the PDO method. That's your own method though.

halfer Over a year ago

OK, so try hardwiring the 18 value into the query - it doesn't sound like that would be a problem, but worth checking nevertheless.

skiwi Over a year ago

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

halfer Over a year ago

I don't know about that setting, but this might be useful. I am not aware you need to change any PDO settings to get LIMIT to work.

|

SmootQ · Accepted Answer · 2013-12-31 13:04:21Z

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, 2013 at 13:04

SmootQ

2,1227 gold badges35 silver badges60 bronze badges

1 Comment

skiwi Over a year ago

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.

Collectives™ on Stack Overflow

PHP PDO and query with SELECT COUNT(*)

2 Answers 2

6 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related