Building the Back-End: Part 2 - Realtime Web Apps: With HTML5 WebSocket, PHP, and jQuery - page 203

* @author Phil Leggetter <[e-mail]>
*/
class Question_Model extends Model
{
}
Loading All Questions for a Room
To load all the questions for a room, the room ID is passed to the get_room_questions() method. The results are
loaded as an object, which is then passed back to the controller for processing.
In order to retrieve the questions in a logical order (i.e., the highest-voted, unanswered questions showing at the
top of the list), a LEFT JOIN is used to leverage the vote count from question_votes for ordering.
Add the following bold code to Question_Model :
class Question_Model extends Model
{
/**
* Loads all questions for a given room
*
* @param $room_id int The ID of the room
* @return array The questions attached to the room
*/
public function get_room_questions( $room_id )
{
$sql = "SELECT
id AS question_id,
room_id,
question,
is_answered,
vote_count
FROM questions
LEFT JOIN question_votes
ON( questions.id = question_votes.question_id )
WHERE room_id = :room_id
ORDER BY is_answered, vote_count DESC";
$stmt = self::$db->prepare($sql);
$stmt->bindParam(':room_id', $room_id, PDO::PARAM_INT);
$stmt->execute();
$questions = $stmt->fetchAll(PDO::FETCH_OBJ);
$stmt->closeCursor();
return $questions;
}
}