当来自MSQL查询的结果为空时,让SELECT FOUND_ROWS()
返回0
时出现问题。
我有以下调用getBasket();
的函数
function viewBasket(){
include('classes/Orders.php');
$BasketID = 10;
$numRows=100;
$data = Orders::getBasket( $numRows, $BasketID);
$results['basket'] = $data['results'];
$results['totalRows'] = $data['totalRows'];
require( "templates/Basket.php" );
};
getBasket()
使用LEFT OUTER JOIN
构建所需的结果,并将其放入数组中,然后将其返回到viewBasket()
。public static function getBasket( $numRows, $BasketID ) {
$order="Name ASC";
$conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
$sql = "
SELECT SQL_CALC_FOUND_ROWS B.BasketID
, BP.ProductID
, BP.Quantity
, P.Name
, P.Price
, PT.NameType
FROM Basket B
LEFT
JOIN BasketProducts BP
ON B.BasketID = BP.BasketID
LEFT
JOIN Products P
ON BP.ProductID = P.ProductID
LEFT
JOIN ProductTypes PT
ON P.ProductTypeID = PT.ProductTypeID
WHERE B.BasketID = :BasketID
ORDER
BY $order
LIMIT :numRows;
";
$st = $conn->prepare( $sql );
$st->bindValue( ":numRows", $numRows, PDO::PARAM_INT );
$st->bindValue( ":BasketID", $BasketID, PDO::PARAM_INT );
$st->execute();
$list = array();
while ( $row = $st->fetch() ) {
$basket = new Orders( $row );
$list[] = $basket;
}
// Now get the total number of articles that matched the criteria
$sql = "SELECT FOUND_ROWS() AS totalRows";
$totalRows = $conn->query( $sql )->fetch();
$conn = null;
return ( array ( "results" => $list, "totalRows" => $totalRows[0] ) );
}
然后,我的HTML回显
totalRows
存储在$results
中的viewBasket();
值<p>You have <?php echo $results['totalRows']?> item<?php echo ( $results['totalRows'] != 1 ) ? 's' : '' ?> in your Basket</p>
它可以工作,但是如果表为空,则不会发送0!
谢谢亚当
最佳答案
我无法在Windows下使用php-5.6.3和mysql-5.6(默认值:myisam)重现该问题。
<?php
define('DB_DSN', 'mysql:host=localhost;dbname=test;charset=utf8');
define('DB_USERNAME', 'localonly');
define('DB_PASSWORD', 'localonly');
var_dump( getBasket(10, 1) );
function getBasket( $numRows, $BasketID ) {
$order="Name ASC";
$conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
setup($conn); // boilerplate: creating empty, temp tables
$sql = "
SELECT
SQL_CALC_FOUND_ROWS
Basket.BasketID, BasketProducts.ProductID, BasketProducts.Quantity,
Products.Name, Products.Price, ProductTypes.NameType
FROM
soBasket as Basket
LEFT OUTER JOIN
soBasketProducts as BasketProducts
ON
Basket.BasketID = BasketProducts.BasketID
LEFT OUTER JOIN
soProducts as Products
ON
BasketProducts.ProductID = Products.ProductID
LEFT OUTER JOIN
soProductTypes as ProductTypes
ON
Products.ProductTypeID = ProductTypes.ProductTypeID
WHERE
Basket.BasketID = :BasketID
ORDER BY
" . $order . " LIMIT :numRows
";
$st = $conn->prepare( $sql );
$st->bindValue( ":numRows", $numRows, PDO::PARAM_INT );
$st->bindValue( ":BasketID", $BasketID, PDO::PARAM_INT );
$st->execute();
$list = array();
while ( $row = $st->fetch() ) {
$basket = new Orders( $row );
$list[] = $basket;
}
// Now get the total number of articles that matched the criteria
$sql = "SELECT FOUND_ROWS() AS totalRows";
$totalRows = $conn->query( $sql )->fetch();
$conn = null;
return ( array ( "results" => $list, "totalRows" => $totalRows[0] ) );
}
class Orders {
public $_data;
public function __Construct(array $data) {
$this->_data = $data;
}
}
function setup($pdo) {
$queries = array(
"
CREATE TEMPORARY TABLE soBasket (
BasketID int
)
",
"
CREATE TEMPORARY TABLE soBasketProducts (
ProductID int,
BasketID int,
Quantity int
)
",
"
CREATE TEMPORARY TABLE soProducts (
ProductID int,
ProductTypeID int,
Price DECIMAL(10,2),
Name varchar(64)
)
",
"
CREATE TEMPORARY TABLE soProductTypes (
ProductTypeID int ,
NameType varchar(64)
)
"
);
foreach( $queries as $q ) {
$pdo->exec($q);
}
}
版画
array(2) {
'results' =>
array(0) {
}
'totalRows' =>
int(0)
}