Completing Suggestotron

Complete Suggestotron

Suggestrotron is not really complete unless we can rank suggestions by popular vote.

We're going to use all of the new skills we've learned, to build out this new feature.

Goals

  • Add voting to Suggestotron

Steps

Step 1

Just like with our topics, we will start out by defining our database table, votes:

votes
id
title_id
count

To get started, we run the mysql command in the terminal:

Type this in the terminal:
mysql -u root -p

When prompted, enter the password: root.

Step 2

We then run our SQL code:

USE suggestotron;

CREATE TABLE votes ( 
    id INT unsigned NOT NULL AUTO_INCREMENT, 
    topic_id INT unsigned NOT NULL, 
    count INT NOT NULL DEFAULT 0, 
    PRIMARY KEY(id) 
);

Step 3

Create empty vote records for each of your existing topics:

INSERT INTO votes (
    topic_id,
    count
) SELECT id, 0 FROM topics;

Verify our data:

SELECT * FROM votes;
Approximate expected result:
SELECT * FROM votes;
+----+----------+-------+
| id | topic_id | count |
+----+----------+-------+
|  1 |        1 |     0 |
|  2 |        2 |     0 |
|  3 |        3 |     0 |
+----+----------+-------+
3 rows in set (0.00 sec)
The result you get may differ and is not important.

Step 4

Update our Topics model class, to insert an empty row when creating new topics automatically:

public function add($data)
{
    $query = \Suggestotron\Db::getInstance()->prepare(
        "INSERT INTO topics (
                title,
                description
        ) VALUES (
                :title,
                :description
        )"
    );

    $data = [
        ':title' => $data['title'],
        ':description' => $data['description']
    ];

    $query->execute($data);

    // Grab the newly created topic ID
    $id = \Suggestotron\Db::getInstance()->lastInsertId();

    // Add empty vote row
    $sql = "INSERT INTO votes (
                topic_id,
                count
            ) VALUES (
                :id,
                0
            )";

    $data = [
        ':id' => $id
    ];

    $query = \Suggestotron\Db::getInstance()->prepare($sql);
    $query->execute($data);
}

Step 5

We must also remove this data, when deleting the topic:

public function delete($id) {
       $query = SuggestotronDb::getInstance()->prepare(
           "DELETE FROM topics
               WHERE
                   id = :id"
       );

       $data = [
           ':id' => $id,
       ];

       $result = $query->execute($data);

       if (!$result) {
           return false;
       }

       $sql = "DELETE FROM votes WHERE topic_id = :id";
       $query = \Suggestotron\Db::getInstance()->prepare($sql);

       return $query->execute($data);
   }

Step 6

We now need a model class to manage our votes, \Suggestotron\Model\Votes:

<?php
namespace Suggestotron\Model;

class Votes {
    public function addVote($topic_id)
    {
        $sql = "UPDATE votes 
                   SET 
                       count = count + 1
                   WHERE
                       topic_id = :id";

        $query = \Suggestotron\Db::getInstance()->prepare($sql);

        $data = [
            ':id' => $topic_id,
        ];

        return $query->execute($data);
    }
}

Step 7

Next up, we create our controller, \Suggestotron\Controller\Votes, with an add action.

<?php
namespace Suggestotron\Controller;

class Votes extends \Suggestotron\Controller {
    public function addAction($options) {
        if (!isset($options['id']) || empty($options['id'])) {
            echo "No topic id specified!";
            exit;
        }

        $votes = new \Suggestotron\Model\Votes();
        $votes->addVote($options['id']);

        header("Location: /");
    }
}

Step 8

To access our new controller, we should add a route to config/routes.php:

'/vote(/:action(/:id))' => [
        'controller' => '\Suggestotron\Controller\Votes',
],

This route should be placed above the generic /:controller(/:action) route which will otherwise catch the request.

Step 9

To allow our users to actually vote, we'll add a button to our topic list view, index/list.phtml, before our Edit and Delete buttons:

<a href="/vote/add/<?=$topic['id']; ?>" class="btn btn-success">
    <span class="glyphicon glyphicon-thumbs-up">
        <strong><?=(isset($topic['count'])) ? $topic['count'] : 0;?></strong>
    </span>
</a>

Step 10

Finally, we need to update our \Suggestotron\Model\Topics model, to both retrieve the votes for each topic, and sort by the number of votes:

public function getAllTopics()
{
    $sql = "SELECT 
                topics.*,
                votes.count
            FROM topics INNER JOIN votes ON (
                votes.topic_id = topics.id
            )
            ORDER BY votes.count DESC, topics.title ASC";

    $query = \Suggestotron\Db::getInstance()->prepare($sql);
    $query->execute();
    return $query;
}

Explanation

Guess what? You're done!!! Congratulations, you just "finished" your first web app!

(They're never really ever finished... have fun tweaking it!)

Go take a look at your masterpiece: http://localhost:8080

It should look something like this:

Next Step: