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.
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
SELECT books.*, sum(votes.value) as score, "
Then, we have a nested select statement to get our vote for the book. This is a basic select from votes where the book_id on the votes table is the same as the current book from the query, and we are getting only the vote where the user_id is our id. We then save that as myVote.
. " (SELECT value FROM votes " . " WHERE votes.book_id=books.id " . " AND votes.user_id=:userId) as myVote"
The main table used in the select.
. " FROM books "
To get the score used in the first line, 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, I want to group everything by the book id so I only have 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 update the way our voting works. 0:02 Only authenticated users should be able to vote, and 0:05 any user can only vote once per book. 0:08 But that user is always able to change their vote. 0:11 Open the book.php file in the inc folder. 0:14 Around the arrows, let's check if the user is authenticated. 0:17 Next, we want to update our arrows to show the way the current user has voted. 0:39 We're going to change the color of the arrows if our user's vote corresponds 0:44 to one of the vote directions. 0:48 If ($book['myVote'] = 1, 0:58 Then we're going to set the style="color:orange". 1:08 We'll do the same thing to the down arrow, only we'll change it to -1. 1:24 To get my vote, we need to update the get all books function. 1:31 First we need the userId. 1:37 Let's set the default to 0, then, if (isAuthenticated, 1:42 Then we can set the userId equal to the userId in our jwt, 1:55 we'll decodeJwt and pass sub. 2:02 Now we need to update our query statement by adding an inner select statement. 2:07 Make sure you add a comma. 2:14 And then in parentheses, we're going to add SELECT value FROM votes. 2:18 WHERE votes.book_id=books.id. 2:29 AND votes.user_id=userId. 2:40 And this will be as myVote. 2:48 Now we need to bind our variable. 2:52 Next we want to clear the votes for the current user and book combination. 3:08 So, we don't have duplicate votes for our user. 3:12 Let's go down to our vote. 3:17 Below the vote function, let's add a new function. 3:19 We'll call this clearVote. 3:23 And we'll pass the bookId. 3:28 We'll need our global $db. 3:35 And once again, we'll pull the userId from our Jwt. 3:40 For our query, we'll DELETE FROM 4:13 votes WHERE book_id = :bookId AND 4:19 user_id = :userId. 4:25 We'll prepare our query, And then bind our variables. 4:36 Execute and then we'll return the row count. 5:11 This will tell us if we actually removed a vote or not. 5:22 Finally, we need to update our vote procedure. 5:26 We're going to wrap our switch statement in the clearVote. 5:32 if (!clearVote and pass the bookId. 5:36 Then we'll make a vote. 5:48 We either clear the vote or make a vote. 5:53 Finally we need to update our vote function to use the current user to score 5:56 the vote. 6:01 Instead of the user ID = 0, we'll use our decodeJwt and pass sub. 6:06 Great, we're now setting our votes table to record our user, and 6:15 we're allowing the user to vote only once. 6:19 Let's go back to the browser and view the book list page. 6:22 Great, we can now make only a single vote for each book, 6:35 either we have no vote, one vote up, Or one vote down. 6:39
You need to sign up for Treehouse in order to download course files.Sign up