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