CODEWITHSUNDEEP
phpmysqlpdoprepared-statement
KCL
KCL

Reputation: 140

mySQL PDO Prepared Statement Different Results from Non-Prepared Statement

My experience with PDO is somewhat limited and I've been stuck on this for a while. The issue is that when I run the code unprepared (because this has proven to be the only way I can debug PDO) I get the results I want. When I run it as a prepared statement, I get different results. See below:

Non-prepared code:

$interval = array("hourly" => "1 HOUR", "daily" => "1 DAY", "weekly" => "7 DAY", "monthly" => "30 DAY", "yearly" => "1 YEAR");
$intervalString = "INTERVAL " . $interval[$p_sLimitType];

$SQL = "SELECT COUNT(*) as `counted` FROM tbl_transaction" .
       " WHERE type='" . $p_sPostType . "'" . 
       " AND catID=" . $p_nCatID .
       " AND serviceID=" . $p_nServiceID .
       " AND serviceIdentity=" . $p_nServiceUserID .
       " AND timestamp BETWEEN DATE_SUB(NOW(), $intervalString . ") AND NOW()";

$theQuery = $DB->Query($SQL);
echo "\r\n\r\nQuery:";
print_r($theQuery);

echo "\r\nResult:";
$result = $theQuery->fetch(PDO::FETCH_ASSOC);
print_r($result);

Non-prepared results:

Query:PDOStatement Object
(
    [queryString] => SELECT COUNT(*) as `counted` FROM tbl_transaction WHERE type='pudding' AND catID=13 AND serviceID=1 AND serviceIdentity=3324848959 AND timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 1 DAY) AND NOW()
)

Result:Array
(
    [counted] => 15
)

And now the prepared code:

$interval = array("hourly" => "1 HOUR", "daily" => "1 DAY", "weekly" => "7 DAY", "monthly" => "30 DAY", "yearly" => "1 YEAR");
$intervalString = "INTERVAL " . $interval[$p_sLimitType];

$SQL = "SELECT COUNT(*) as `counted` FROM tbl_transaction" .
       " WHERE type=:postType" . 
       " AND catID=:catID" .
       " AND serviceID=:serviceID" .
       " AND serviceIdentity=:serviceIdentity" .
       " AND timestamp BETWEEN DATE_SUB(NOW(), :interval) AND NOW()";

// Execute the statement

try { 
    $stmt = $DB->prepare($SQL);
    $stmt->bindParam(':postType', $p_sPostType, PDO::PARAM_STR, 30);
    $stmt->bindParam(':catID', $p_nCatID, PDO::PARAM_INT);
    $stmt->bindParam(':serviceID', $p_nServiceID, PDO::PARAM_INT);
    $stmt->bindParam(':serviceIdentity', $p_nServiceUserID, PDO::PARAM_INT);
    $stmt->bindParam(':interval', $intervalString, PDO::PARAM_STR, 30);
    $result = $stmt->execute();
} catch(PDOException $e) {
    mm_die($e->getMessage());
}

echo "\r\n\$SQL = $SQL";
//          echo "\r\n\$p_nLimitValue = $p_nLimitValue\r\n";
echo "\r\nRow Count: " .$stmt->rowCount() . "\r\n";

And the prepared results:

$SQL = SELECT COUNT(*) as `counted` FROM tbl_transaction WHERE type=:postType AND siloID=:siloID AND serviceID=:serviceID AND serviceIdentity=:serviceIdentity AND timestamp BETWEEN DATE_SUB(NOW(), :interval) AND NOW()
Row Count: 0

Note "Row Count" being zero in the prepared statement. I've stared at this for more time than I care to admit. Can anyone see why one returns results and the other does not? Thanks!

Upvotes: 0

Views: 376

Answers (3)

Barmar
Barmar

Reputation: 781058

The problem is that the second argument to DATE_SUB() must be an interval, but you're providing a string. The string "INTERVAL 1 HOUR is not automatically converted to the corresponding interval. You can only use a placeholder for the numeric part of the INTERVAL expression, not for the keywords.

Take the time units out of the associative array, and represent everything as hours.

$interval = array("hourly" => 1, "daily" => 24, "weekly" => 7*24, "monthly" => 30*24, "yearly" => 365*24);

Then you can do:

$SQL = "SELECT COUNT(*) as `counted` FROM tbl_transaction" .
       " WHERE type=:postType" . 
       " AND catID=:catID" .
       " AND serviceID=:serviceID" .
       " AND serviceIdentity=:serviceIdentity" .
       " AND timestamp BETWEEN DATE_SUB(NOW(), INTERVAL :interval HOUR) AND NOW()";

and

$stmt->bindParam(':interval', $interval[$p_sLimitType], PDO::PARAM_INT);

Upvotes: 3

Marc B
Marc B

Reputation: 360692

It's not possible for a select count(*) to return 0 rows. It will always return AT LEAST one row, containing the count of the matched/found rows. Getting 0 rows means your query failed outright, and didn't return a result set, period.

Did you enable exceptions in PDO? By default it does "return false" for failure, and won't throw exceptions unless you explicitly enable them. if they're not enabled, then your try/catch is useless.

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157897

Placeholder could not represent an arbitrary part of query, but complete string or numeric literal only.

Thus you cannot bind a part of interval.

As long as you have your intervals whitelisted in the code as shown above, you may and you have to stick with old approach for interval.

Upvotes: 1

Related Questions