This course will be retired on June 1, 2025.

Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

PHP User Authentication

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Book Rating System

6:23 with Alena Holligan and Brian Retterer

The next part of this project is the voting system. We will need to create a way for a user to upvote and downvote a book, but also show the calculated score of a book based on votes. To save time, we will set the database to accept a user id for the vote instead of adding a column to the book table for score.

Course: Integrating PHP with Databases: Using Relational Tables

Course: Querying Relational Databases: Joining Table Data with SQL

Alternate Code Samples

Ternary Operator: 

echo $book['score']; ?: echo '0';

PHP 7: null coalesce operator 

echo $book['score']; :: echo '0';

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    By now, we have a pretty cool system that lets you add books and edit or 0:00
    delete them. 0:04
    If we go back to our main goal for 0:05
    this site, we want to allow a user to vote on a book. 0:07
    Very similar to the way Reddit does their voting, 0:11
    we're going to set up an upvote and a downvote. 0:13
    And also, show the current score of a book. 0:16
    I have already set up the database and the styles for you. 0:19
    So we just have to write a little bit of code to wire everything up. 0:22
    To start this process off, we need to edit our book code from inc book.php. 0:26
    Let's add in some arrows and links for easy voting right inside the first div. 0:33
    Div class = btn group 0:39
    vertical,role = group. 0:45
    Link to 1:00
    procedures/vote.php?vote=up&bookId=echo 1:02
    bookid. 1:15
    Span class=glyphicon, 1:24
    glyphicon triangle-top, 1:30
    and then our score. 1:37
    If isset book, score. 1:46
    Echo, book, score. 1:56
    Else. 2:02
    Echo zero. 2:04
    Let's duplicate these two lines for down. 2:07
    Next, we need to edit our query for the GetAllBooks function to include a score. 2:23
    We're going to be using a votes table. 2:35
    The votes table is a joining table often called a pivot table 2:38
    which relates a book ID to a user ID. 2:42
    We'll get into the user ID part of this later. 2:45
    For more details about joint, please check the notes associated with this video. 2:48
    Select books all, and then we're going 2:51
    to some votes.value, As score. 2:57
    FROM books. 3:13
    LEFT JOIN votes ON books. 3:17
    Id=vote.book_id, 3:24
    GROUP BY books.id. 3:36
    And finally, ORDER BY score. 3:42
    This now adds a new value called score that we can get for the book. 3:50
    While you're in the functions file., we need to add a new function for 3:56
    handling the voting. 3:59
    We'll call this vote. 4:04
    We're going to pass a bookId and a score. 4:07
    We start with the global DB. 4:17
    And then we're going to set a user ID to 0 for now. 4:18
    Then, we add our try catch block. 4:26
    We'll add a simple message. 4:41
    Now, for our query. 4:55
    We're going to INSERT INTO votes book_id, 4:59
    user_id, and value. 5:07
    And our VALUES, Will equal bookId, 5:11
    UserId, and score. 5:19
    Prepare our query. 5:31
    And then bind. 5:34
    And finally execute. 6:17
    Next up is the actual procedure for the voting. 6:20

    You need to sign up for Treehouse in order to download course files.

    Sign up

      You need to sign up for Treehouse in order to set up Workspace

      Sign up