Read Records

Adopt MVC for the read records feature.

Our project has adopted the MVC architecture thus far. Now it’s time for us to refactor the read records feature so it becomes MVC friendly.

Let’s first consider our previous code to get a list of projects.

The non-MVC code

    <?php
    try {
        require "./config.php";

        $connection = new PDO($dsn, $username, $password, $options);

        $sql =  'SELECT * FROM projects ORDER BY title';

        $statement = $connection->query($sql);

        $projectCount = $statement->rowCount();
    } catch (PDOException $err) {
        echo $sql . "<br>" . $err->getMessage();
    }
    ?>
    <div>
        <h1>Project list (<?php echo $projectCount ?>)</h1>
        <!-- If there's not yet data -->
        <?php
        if ($projectCount <= 0) {
            echo "<p>You have not yet added any project </p>";
            echo "<p><a href='#'>Add project</a></p>";
        }

        ?>

        <?php foreach ($statement as $row) : ?>
        <ul>

            <li>
                <?php echo $row["title"] ?>
            </li>
        </ul>
        <?php endforeach; ?>
    </div>

    <p><a href="./">Home</a></p>

This is a good start, but an overall bad practice because it will be hard to maintain, debug, or unit-test. For that reason, we need some sort of clean separation. To do this, we’ll break the code into three different files or components:

  • model.php to put all staff related to the database.
  • views/project_list.php to for the presentation.
  • controllers/project_list.php to link the model with the view.

Let’s get started.

The controller component

For the controller component, we’ll create the controllers/project_list.php file —if it is not already there— and put the following code inside it:

<?php
// controllers/project_list.php
require_once "../model/model.php";

$projects = get_all_projects();
$projectCount = get_all_projects_count();

require "../views/project_list.php";

There shouldn’t be anything too complicated here, we’re just linking the model to the view.

The model

For the model, we’ll create the model/model.php file —if it is not already there— and put these functions into it:

<?php
// model/model.php
require "connection.php";

$connection = db_connect();

function get_all_projects()
{
    try {
        global $connection;

        $sql =  'SELECT * FROM projects ORDER BY title';
        $projects = $connection->query($sql);

        return $projects;
    } catch (PDOException $err) {
        echo $sql . "<br>" . $err->getMessage();
        exit;
    }
}

function get_all_projects_count()
{
    try {
        global $connection;

        $sql =  'SELECT COUNT(id) AS nb FROM projects';
        $statement = $connection->query($sql)->fetch();

        $projectCount = $statement['nb'];

        return $projectCount;
    } catch (PDOException $err) {
        echo $sql . "<br>" . $err->getMessage();
        exit;
    }
}

Note: We define $connection as a global variable. Every time we want to use it locally, we’ll use the global keyword before the variable.

Our model relies on the connection string. Let’s create a connection.php file inside our model directory:

<?php
// model/connection.php
function db_connect()
{
    try {
        require config/dev.php;

        $connection = new PDO($dsn, $username, $password, $options);
    } catch (PDOException $err) {
        echo "Database connection error. <br>" . $err->getMessage();
    exit;
    }
    return $connection;
}

Now we can display the content.

The view component

The basic front-end will look like this:

<!-- views/project_list.php -->
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="../public/css/style.css"> <!-- STYLE -->
    <title>Projects list</title>
</head>

<body>
    <div class="container">
        <h1>Project list (<?php echo $projectCount ?>)</h1>
        <!-- If there's not yet data -->
        <?php if ($projectCount == 0) { ?>
        <div>
            <p>You have not yet added any project </p>
            <p><a href='#'>Add project</a></p>
        </div>
        <?php } ?>

        <ul>
            <?php foreach ($projects as $project) : ?>
            <li>
                <?php echo $project["title"] ?>
            </li>
            <?php endforeach; ?>
        </ul>
    </div>

    <p><a href="../">Home</a></p>
</body>

So, what did we do with the file shown above? We refactored it in such a way that it would only contain the code needed to present data to the user. We also added a bonus point to alert the user if there’s no project in the database.

We can also refactor the veiw to accomodate for the excess of views that we’re likely to have. Let’s create a basic layout in the views directory: views/layout.php.

Get hands-on with 1000+ tech skills courses.