'SELECT FOUND_ROWS()' returning a minimum of 1 when results empty

Question

I have a problem getting SELECT FOUND_ROWS() to return 0 when the result from MSQL query is empty.

I have the following function that calls 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() builds the required results using LEFT OUTER JOIN and places into an array which is then returned to 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] ) );
}

My HTML then echos the totalRows value stored in $results by viewBasket();

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

It works, but just wont send a 0 if the table is empty !
Thanks, Adam

Answer 1

thanks to the input from @VolkerK and @Phate01 I have solved the issue.

I cleared the BasketID set in the basket table if the row returned from basketProducts is NULL. i.e. if I have removed the last row from products associated to that user, remove the cart session from 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, I had a quick read of that link and its gone over my head. Will read up tonight, but would you mind explaining how this might help me?

Answer 2

I cannot reproduce the problem using php-5.6.3 and mysql-5.6 (default: myisam) under windows.

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

prints

array(2) {
  'results' =>
  array(0) {
  }
  'totalRows' =>
  int(0)
}

Answer 3

Try to cast it:

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

Answer 4

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

an IF condition before echoing would do it.