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"];

score 0 · Accepted Answer · answered May 26 '16 at 07:12

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 :

AND will be evaluated first, so it will do (1 AND 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

Thank you so much SuperPeanut. This worked and solved my issues. — Kamesan, May 26 '16 at 18:22
@Kamesan mark the post as the answer if it worked, so next with same problem will be able to see how to solve it. — Martin Verjans, May 26 '16 at 18:54

PDO: Get all untested flashcards from database

1 Answers1