PDO: Get all untested flashcards from database

Question

I am trying to get all flashcards from a specific deck (deck_id) that are pending.

A flashcard is pending when

• it hasn't been tested,
• next_review is not set (new card) or
• next_review <= $today (means card is due to review).

What I currently get is results from different card decks like deck_id 1 and deck_id 2 but I just want to retrieve pending cards from the same deck (like deck_id = 1).

This is what I tried:

//Get the untested and ready to review cards from DB
public function getCardSR($deckId)
{

    $today = date('Y-m-d');
    $notSet = '0000-00-00';

$query = "SELECT tbl_repetition.repetition_id, 
                 tbl_repetition.card_id, 
                 tbl_repetition.deck_id, 
                 tbl_repetition.user_id, 
                 tbl_repetition.status, 
                 tbl_repetition.last_review, 
                 tbl_repetition.eFactor, 
                 tbl_repetition.inter, 
                 tbl_repetition.repetition, 
                 tbl_repetition.grade, 
                 tbl_cards.front, 
                 tbl_cards.back
    FROM tbl_repetition
    LEFT JOIN tbl_cards
    ON tbl_repetition.card_id = tbl_cards.card_id
    WHERE tbl_repetition.deck_id = :deckId AND 
          tbl_repetition.next_review <= :today OR 
          tbl_repetition.next_review = :notSet";

    $stmt = $this->conn->prepare($query);
    $stmt->bindParam(':deckId', $deckId);
    $stmt->bindParam(':today', $today);
    $stmt->bindParam(':notSet', $notSet);
    $stmt->execute();

    $result = $stmt->fetchAll();

    return $result;
}   

This is how the results are retrieved from the $result array:

$repetition_id = $decks->getCardSR($c)[$repetitionCard]  ["repetition_id"];
$eFactor =       $decks->getCardSR($c)[$repetitionCard]["eFactor"];
$card_id =       $decks->getCardSR($c)[$repetitionCard]["card_id"];
$deck_id =       $decks->getCardSR($c)[$repetitionCard]["deck_id"];
$user_id =       $decks->getCardSR($c)[$repetitionCard]["user_id"];
$status =        $decks->getCardSR($c)[$repetitionCard]["status"];
$last_review =   $decks->getCardSR($c)[$repetitionCard]["last_review"];
$inter =         $decks->getCardSR($c)[$repetitionCard]["inter"];
$repetition =    $decks->getCardSR($c)[$repetitionCard]["repetition"];
$grade =         $decks->getCardSR($c)[$repetitionCard]["grade"];
$front =         $decks->getCardSR($c)[$repetitionCard]["front"];
$back =          $decks->getCardSR($c)[$repetitionCard]["back"];

Answer 1

Your problem comes from Operator Precedence in your WHERE clause.

Now, you are doing this :

WHERE tbl_repetition.deck_id = :deckId AND 
      tbl_repetition.next_review <= :today OR 
      tbl_repetition.next_review = :notSet

Let's call first expression 1, second 2 and third 3.

Which means it will be evaluated like this :

1. AND will be evaluated first, so it will do (1 AND 2)
2. OR will be evaluated second, so you will result in (1 AND 2) OR 3

This means you will retrieve all cards from one deck that need review today AND all cards from all decks that review is not set...

To solve this you must place parenthese to make the OR to be evaluated first :

WHERE tbl_repetition.deck_id = :deckId AND 
      (tbl_repetition.next_review <= :today OR 
      tbl_repetition.next_review = :notSet)

That way you will have what you want