当来自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)
}

08-05 06:21
查看更多