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

Update Voting System

3:53 with Alena Holligan

Let’s make some updates to our voting system to make sure a user can have only a single vote registered per book. We are going to update our book list as well to show the way the user voted by changing the arrow color.

Other samples for score

Ternary Operator: 

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

PHP 7: null coalesce operator

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

Breaking Down the Query

Let's break down this query now that it has become more complex.

First we select all columns from the books table and get the sum total of votes for a book and saving that column as score

SELECT books.*, sum(votes.value) as score, "

The main table used in the select.

. " FROM books "

To get the score used in the select, we do a LEFT JOIN of the votes table where the id of the current book equals the book_id's from the votes table.

. " LEFT JOIN votes ON (books.id = votes.book_id) "

Then, we group everything by the book id so there is only 1 row per book

. " GROUP BY books.id "

Finally we order the books by the score DESC so the highest voted book is at the top and the lowest scored book is at the bottom.

. " ORDER BY score DESC";

For the final step in our book voting system, 0:00
I want to be able to update the way the voting works. 0:03
Only authenticated users should be able to vote. 0:07
And each user only gets one vote per book, but 0:11
that user is always able to change their vote. 0:14
Let's take a look at our functions_vote file. 0:18
The first function gives us the saved vote for a specific book by a specific user. 0:22
The user ID is not required, it will default to 0. 0:29
The next function is the actual vote, 0:33
It accepts a bookId, a score, and an optional userId. 0:39
This function calls the clearVote function. 0:46
That function accepts a bookId and a userId, and 0:52
attempts to delete that user's vote. 0:56
If a vote is actually deleted, 1:00
the function will return a count greater than 0. 1:03
The vote function will count that as the user's vote and return true. 1:08
This keeps the vote to 1 point instead of jumping from positive 1 to negative 1. 1:14
For more information, see the notes associated with this video. 1:20
If no vote was cleared, then we add the user's vote to the database. 1:24
Let's update our procedures/vote.php to pass the ID of the authenticated user. 1:31
First we'll add requireAuth, 1:41
then we can get the user = getAuthenticatedUser. 1:45
And then we can pass user id. 1:58
Next, we want to update our arrows to show which way the current user has voted, 2:03
so let's open book.php. 2:10
The first thing that we'll need to do is get the authenticated user. 2:13
After we check if the user is authenticated, 2:18
we can do user = getAuthenticatedUser. 2:23
Now, inside of the class of our up arrow, we're going to add a php block. 2:29
We can add as much space as we want, so let's add some new lines. 2:40
We're going to add a conditional that checks if, getUserVote, 2:45
And we pass the book, 2:55
id, and the user id. 2:59
And then we check to see if this equals 1. 3:05
If it does, then we're going to echo, space, orange. 3:09
Make sure that we have a space before our new orange class so 3:17
that it doesn't get mixed up with our previous classes. 3:20
Let's copy this code block, And we'll add it to our down arrow. 3:24
Instead of 1, we wanna look for negative 1. 3:33
Now let's go back to the browser and view the Book List page. 3:37
We can now only make a single vote per book, 3:44
either we have no vote, one vote up, or one vote down. 3:48

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

Sign up