'SELECT FOUND_ROWS（）'当结果为空时返回最小值1

Question

当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通过viewBasket();

<p>You have <?php echo $results['totalRows']?> item<?php echo ($results['totalRows'] != 1) ? 's' : '' ?> in your Basket</p> 

它的工作原理回声存储在$results的totalRows值，但只是不会发送一个0，如果表是空的！
谢谢，亚当

Answer 1

得益于@VolkerK输入和@ Phate01我已经解决了这个问题。

如果从basketProducts返回的row为NULL，我清除了设置在basket表中的BasketID。即如果我已从与该用户关联的产品中删除最后的row，则从Basket中删除购物车会话。

$conn = new PDO(DB_DSN, DB_USERNAME, DB_PASSWORD); 
    $st = $conn->prepare ("select BasketID FROM basketProducts WHERE BasketID = :basketID"); 
    $st->bindValue(":basketID", $basketID, PDO::PARAM_INT); 
    $st->execute(); 

    $row = $st->fetch(); 
    $conn = null; 


    /** 
    * If the row returned from BasketProducts DOES NOT include the customers $basketID 
    * remove the row. 
    **/ 
    if($row == 0){ 
     $conn = new PDO(DB_DSN, DB_USERNAME, DB_PASSWORD); 
     $st = $conn->prepare ("DELETE FROM Basket WHERE BasketID = :basketID LIMIT 1"); 
     $st->bindValue(":basketID", $basketID, PDO::PARAM_INT); 
     $st->execute(); 

     $row = $st->fetch(); 
     $conn = null; 
    }else{ 
    //do nothing 
    } 

@VolkerK，我有联系的快速阅读和它的消失在我的头上。今晚会读，但你介意解释这可能对我有帮助吗？

Answer 2

if($results['totalRows'] > 0){ 
echo $results['TotalRows']; 
else{ 
echo '0'; 
} 

的IF条件之前echo ING会做到这一点。

Answer 3

尝试投它：在Windows下：（MyISAM数据默认）

return (array ("results" => $list, "totalRows" => (int)$totalRows[0])); 

Answer 4

使用PHP-5.6.3和mysql-5.6，我不能重现该问题。

<?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) 
}