PHP MVC With Database (Very Simple Beginner Example)

Welcome to a tutorial and example of an MVC application with PHP and MySQL. You have probably heard of MVC everywhere in different flavors – Framework, design pattern, concept, architecture, and much more. So just what is it? What does it do and what does it mean? How do we build a PHP application based on MVC!?

Yep, it’s that simple actually. But as some tech people love to do, they make simple things look difficult. Let us walk through a simple example in this guide – Read on!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

DUMMY TABLE & DATA

CREATE TABLE `users` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD KEY `name` (`name`);
 
INSERT INTO `users` (`id`, `name`) VALUES
  (1, 'John Doe'),
  (2, 'Jane Doe'),
  (3, 'Rusty Terry'),
  (4, 'Peers Sera'),
  (5, 'Jaslyn Keely');

First, here is the dummy users table that we will be using. Very straightforward with only the user ID and name.

 

 

PART 1) MODEL (DATABASE)

<?php
class DB {
  // (A) CONNECT TO DATABASE
  public $error = "";
  private $pdo = null;
  private $stmt = null;
  function __construct () {
    $this->pdo = new PDO(
      "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET, 
      DB_USER, DB_PASSWORD, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
  }

  // (B) CLOSE CONNECTION
  function __destruct () {
    if ($this->stmt!==null) { $this->stmt = null; }
    if ($this->pdo!==null) { $this->pdo = null; }
  }

  // (C) RUN A SELECT QUERY
  function select ($sql, $data=null) {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
    return $this->stmt->fetchAll();
  }
}

// (D) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");
 
// (E) NEW DATABASE OBJECT
$_DB = new DB();

Model refers to the data structure. So in the case of a PHP-MYSQL application, our “model” will be a database class that deals with all the “database stuff” – Connecting to the database, managing the connection, and doing SQL queries.

 

 

PART 2) CONTROLLER (PROCESS)

<?php
// (A) DATABASE CONNECTION
require "1-model.php";

// (B) SEARCH FOR USERS
$results = $_DB->select(
  "SELECT * FROM `users` WHERE `name` LIKE ?",
  ["%{$_POST["search"]}%"]
);

// (C) OUTPUT RESULTS
echo json_encode(count($results)==0 ? null : $results);

The controller is a “middleman” that bridges the user input and model. In this case, it will be an endpoint that accepts a $_POST["search"] from the user, uses the model (database) to search, and return the results.

 

 

PART 3) VIEW (USER INTERFACE)

<!-- (A) SEARCH JAVASCRIPT -->
<script>
function doSearch () {
  // (A1) GET SEARCH TERM
  var data = new FormData(document.getElementById("mySearch"));

  // (A2) AJAX - USE HTTP:// NOT FILE://
  fetch("2-controller.php", { method:"POST", "body":data })
  .then(res => res.json())
  .then(res => {
    let results = document.getElementById("results");
    results.innerHTML = "";
    if (res !== null) { for (let r of res) {
      results.innerHTML += `<div>${r.id} - ${r.name}</div>`;
    }}
  });
  return false;
}
</script>
 
<!-- (B) SEARCH FORM -->
<form id="mySearch" onsubmit="return doSearch()">
  <input type="text" name="search" required>
  <input type="submit" value="Search">
</form>
 
<!-- (C) SEARCH RESULTS -->
<div id="results"></div>

Finally, the view should be pretty self-explanatory. It is nothing more than HTML, CSS, Javascript to create the user interface.

 

 

 

THE “WRONG” WAY

<!-- (A) SEARCH FORM -->
<form method="post">
  <input type="text" name="search" required>
  <input type="submit" value="Search">
</form>

<!-- (B) SEARCH + SHOW RESULTS -->
<div id="results"><?php
  if (isset($_POST["search"])) {
    // (B1) DATABASE SETTINGS - CHANGE TO YOUR OWN!
    define("DB_HOST", "localhost");
    define("DB_NAME", "test");
    define("DB_CHARSET", "utf8mb4");
    define("DB_USER", "root");
    define("DB_PASSWORD", "");
 
    // (B2) CONNECT TO DATABASE
    $pdo = new PDO(
      "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET, 
      DB_USER, DB_PASSWORD, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
 
    // (B3) SEARCH 
    $stmt = $pdo->prepare("SELECT * FROM `users` WHERE `name` LIKE ?");
    $stmt->execute(["%{$_POST["search"]}%"]);
    $results = $stmt->fetchAll();

    // (B4) OUTPUT
    if (count($results)>0) { foreach ($results as $r) {
      echo "<div>{$r["id"]} - {$r["name"]}</div>";
    }}
  }
?></div>

For the guys who are still confused, maybe a negative example of “not good MVC” will help – Putting all the MVC components into a single script.

 

 

THE CAVEAT – GOOD & BAD?

Actually, there is absolutely nothing wrong with putting everything into a single script. It is much more convenient and easier for small projects to do so. But if you think of it in “actual MVC terms”, the database (model), the retrieval of data (controller), and the display of data (view) are all in one script.

That is not what we want in MVC. The whole idea of MVC is to have a clear-cut separation of concern, and have individual scripts that deal with each component. I.E. Scripts that deal with the data model should be in an individual script, the controller in another, and the HTML view in yet another.

 

NOT THE ONLY WAY

I heard the “expert code trolls” scream – This is not MVC! MVC should be in the format of site.com/MODEL/ site.com/VIEW/ site.com/CONTROLLER/. Well, feel free to stick to your “fixed idea of how MVC should be”. That is not wrong, but please come up with better arguments. True code ninjas know better – MVC is an abstract idea.

How you design a system around MVC is entirely up to you. This tutorial is only a simple example of endless possibilities. Even the “wrong example” above is MVC, it’s just that all 3 components are in a single script; We can create 3 functions and still call it “MVC” – One to connect to the database, one to do the search, and one to draw the HTML.

 

LINKS & REFERENCES

• Model–view–controller – Wikipedia