PDO Prepared Statement returns 0 rows, but query works fine
I have the following Prepared Statement:
SELECT `e1`.`field_value` AS `county`,
`e2`.`field_value` AS `venue_type`,
`l`.`ID` AS `listing_id`,
`l`.`ID` AS `id`,
`l`.`user_ID` AS `user_id`,
IF(`l`.`user_ID` > 1, 'b', 'a') AS `flag` ,
COUNT(`img`.`ID`) AS `img_num`
FROM ( `listingsDBElements` `e1`,
`listingsDBElements` `e2`,
`listingsDB` `l` )
LEFT JOIN `listingsImages` `img` ON (`l`.`ID` = `img`.`listing_id`
AND `l`.`user_ID` = `img`.`user_id`
AND `img`.`active` = 'yes')
WHERE `e1`.`field_name` = 'county'
AND `e1`.`field_value` = :county
AND `l`.`ID` = `e1`.`listing_id`
AND `l`.`user_ID` = `e1`.`user_id`
AND `e2`.`field_name` = 'venue_type'
AND `e2`.`field_value` = :venueType
AND `l`.`ID` = `e2`.`listing_id`
AND `l`.`user_ID` = `e2`.`user_id`
AND `l`.`ID` = `e2`.`listing_id`
AND `l`.`user_ID` = `e2`.`user_id`
AND `l`.`active` = 'yes'
GROUP BY `l`.`ID`
ORDER BY `flag` DESC,
`img_num` DESC LIMIT :limit,
:offset
With the following parameters bound to it:
:county => 'Bedfordshire'
:venueType => 'Conference Centre'
:limit => 0
:offset => 12
I then execute the Prepared Statement using PDO as follows:
$vens = $db->prepare($sql);
if($paged)
{
$limit = (int) (($page - 1) * $perPage);
$perPage = (int) $perPage;
$vens->bindParam(':offset', $perPage, PDO::PARAM_INT);
$vens->bindParam(':limit', $limit, PDO::PARAM_INT);
}
foreach($bindings as $key => $value) { $vens->bindParam($key, $value, PDO::PARAM_STR); }
$vens->execute();
while($ven = $vens->fetchObject())
{
$_tpl['venues'][] = new Venue($ven->id);
}
The above always returns 0 results, even using $db->rowCount() , but if I manually run the query and replace the placeholders within MySQL, the expected result of 4 records is produced. What is strange, is that I have another (almost identical) query that runs and counts the total rows, and binds the same parameters as follows:
SELECT COUNT(DISTINCT(`l`.`ID`)) AS `total`
FROM ( `listingsDBElements` `e1`,
`listingsDBElements` `e2`,
`listingsDB` `l` )
WHERE `e1`.`field_name` = 'county'
AND `e1`.`field_value` = :county
AND `l`.`ID` = `e1`.`listing_id`
AND `l`.`user_ID` = `e1`.`user_id`
AND `e2`.`field_name` = 'venue_type'
AND `e2`.`field_value` = :venueType
AND `l`.`ID` = `e2`.`listing_id`
AND `l`.`user_ID` = `e2`.`user_id`
AND `l`.`ID` = `e2`.`listing_id`
AND `l`.`user_ID` = `e2`.`user_id`
AND `l`.`active` = 'yes'
And the above is executed using:
$counter = $db->prepare($countSql);
$counter->execute($bindings);
$counts = $counter->fetchObject();
$counts->total correctly returns 4 . Can anyone suggest why the first Prepared Statement returns 0 rows through PDO, but works fine when manually entered into MySQL?
Edit:
As per Joachim's comment, there are other queries that function fine with bound parameters to the limit and offset, and even removing them returns the same result. For example, the following query works fine:
SELECT `l`.`ID` AS `id`,
IF(`l`.`user_ID` > 1, 'b', 'a') AS `flag`
FROM `listingsDB` `l`
INNER JOIN `listingsDBElements` `e` ON `l`.`ID` = `e`.`listing_id`
WHERE `e`.`field_name` = 'city'
AND `e`.`field_value` = :town
AND `l`.`active` = 'yes'
ORDER BY `flag` DESC LIMIT :limit,
:offset
Basically, you can only bind certain types of things in a Prepared Statement.
From the MySQL manual:
The markers are legal only in certain places in SQL statements. For example, they are permitted in the VALUES() list of an INSERT statement (to specify column values for a row), or in a comparison with a column in a WHERE clause to specify a comparison value. However, they are not permitted for identifiers (such as table or column names), or to specify both operands of a binary operator such as the = equal sign. The latter restriction is necessary because it would be impossible to determine the parameter type. In general, parameters are legal only in Data Manipulation Language (DML) statements, and not in Data Definition Language (DDL) statements.
Note that LIMIT and OFFSET are not in the list of allowed locations.
链接地址: http://www.djcxy.com/p/66710.html