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

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';

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