PHP MVC With Database – Really Simple Example For Beginners

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? What does it mean? How do we build a PHP application based on MVC!?

MVC stands for “model-view-controller”, and in the context of a PHP MySQL application:

  • Model refers to the data structure, the MySQL tables.
  • View refers to the user interface, the HTML, CSS, and Javascript.
  • Controller refers to the “middle man” that does the processing between the view and model, the PHP scripts and libraries.

So long as an application has clear structural separation between the MVC components – We can call that an “MVC application”.

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!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

REAL QUICK TUTORIAL

 

TABLE OF CONTENTS

Download & Notes PHP MVC Example Useful Bits & Links
The End

 

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

EXAMPLE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

QUICK NOTES

  • Create a test database and import 0-users.sql.
  • Change the database settings to your own in 1-model.php.
  • That’s all, launch 3-view.html in your browser.

If you spot a bug, please feel free to comment below. I try to answer questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

PHP MVC EXAMPLE WITH MYSQL

All right, let us now get started with the example of a simple MVC application with PHP and MySQL – Doing a search for users.

 

0) DUMMY TABLE & DATA

0-users.sql
CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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.

 

 

1) MODEL (DATABASE)

1-model.php
<?php
class DB {
  // (A) CONNECT TO DATABASE
  public $error = "";
  private $pdo = null;
  private $stmt = null;
  function __construct () {
    try {
      $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
        ]
      );
    } catch (Exception $ex) { die($ex->getMessage()); }
  }

  // (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, $cond=null){
    $result = false;
    try {
      $this->stmt = $this->pdo->prepare($sql);
      $this->stmt->execute($cond);
      $result = $this->stmt->fetchAll();
      return $result;
    } catch (Exception $ex) { 
      $this->error = $ex->getMessage(); 
      return false;
    }
  }
}

// (D) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define('DB_HOST', 'localhost');
define('DB_NAME', 'test');
define('DB_CHARSET', 'utf8');
define('DB_USER', 'root');
define('DB_PASSWORD', '');

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.

 

 

2) CONTROLLER (PROCESS)

2-controller.php
<?php
// (A) DATABASE CONNECTION
require "1-model.php";
$DB = new DB();

// (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.

 

3) VIEW (USER INTERFACE)

3-view.html
<!-- (A) SEARCH JAVASCRIPT -->
<script>
function doSearch () {
  // (A1) GET SEARCH TERM
  var data = new FormData();
  data.append("search", document.getElementById("search").value);

  // (A2) AJAX - USE HTTP:// NOT FILE://
  var xhr = new XMLHttpRequest();
  xhr.open("POST", "2-controller.php");
  xhr.onload = function(){
    let results = document.getElementById("results"),
    search = JSON.parse(this.response);
    results.innerHTML = "";
    if (search !== null) { for (let s of search) {
      results.innerHTML += `<div>${s.id} - ${s.name}</div>`;
    }}
  };
  xhr.send(data);
  return false;
}
</script>
 
<!-- (B) SEARCH FORM -->
<form onsubmit="return doSearch()">
  <input type="text" id="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.

 

 

USEFUL BITS & LINKS

That’s it for the example, and here are a couple more bits that may be useful to you.

 

THE “WRONG” WAY

4-not-good.php
<!-- (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', 'utf8');
    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 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, have individual scripts that deal with each component. I.E. Scripts that deal with the data model should be in an individual script, controller in another, and the HTML view in yet another.

 

NOT THE ONLY WAY

I can hear the “expert code troll ninjas” go – “This is not MVC! This is just normal programming! MVC should be in the format of site.com/MODEL/ site.com/VIEW/ site.com/CONTROLLER/“. Well, feel free to stick with your “fixed idea of an MVC framework”. That is not wrong, but please come up with better arguments.

Smart code ninjas know the truth – MVC is an abstract idea, it is a framework. How you interpret and design your system structure around it is entirely up to you. This tutorial is but a simple example of the endless possibilities; Even the “wrong example” above is MVC, it’s just that all 3 components co-exist in one single script.

 

INFOGRAPHIC CHEAT SHEET

PHP MySQL MVC Example (Click To Enlarge)

 

LINKS & REFERENCES

 

THE END

Thank you for reading, and we have come to the end of this guide. I hope that it has helped you to better understand what MVC is, and if you have anything to add to this guide, please feel free to comment below. Good luck and happy coding!

18 thoughts on “PHP MVC With Database – Really Simple Example For Beginners”

  1. LEANDRO DE SOUZA TAVARES

    This is not MVC.
    Mvc follows the pattern: URL -> controller/action/optional_parameters

    You are calling the controller from the view. This is 100% nonsense.
    You are even importing the User model inside the view.

    1. I usually don’t approve the negative comments. But this is a perfect example of exactly what I meant in the tutorial – Another angry poison spewing troll bites the dust. Stuck to the very idea of “MVC must be done this way”, because “a certain programming framework does it this way”. A PHP framework must be in the format of URL -> controller/action/optional_parameters, anything else that is not following this way is nonsense and therefore not MVC. For your information:

      1) The controller can be used to help build and even manipulate the view (what’s wrong with calling the controller within the view?)
      2) The model is the backbone and what shapes the view (what’s wrong with including it in the view again?)

      So going with your argument of “the controller and model should not be in the view”, let us make things “MVC compliant” in your way:
      1) Create another controller function called “createHTMLPage()”.
      2) Literally just cut paste the above view example into the new function.
      3) Calling the createHTMLPage() function.

      There, we now have the controller generating the view instead, and this matches your “strict” criteria. But what’s your point here really? That we need to abandon intelligence and not think flexibly?

  2. class Users extends DB { … } // ?!

    Your “user” object shouldn’t inherit the “database” object. That’s a completely works logic. For instance, a “dog” object could inherit a lot from object “pet”; object “man” could inherit a lot from object “human” but in a nutshell “user” has nothing to do with “database”. But since you need “database” to be related with “user, then you should make that relation something in a different way. Let’s say, you could use dependency injection.

    class User {
    private $db = null;
    private $data = null;
    public function __construct(Database $db, $data = []){
    this->db = $db;
    this->data = $data;
    }
    }

  3. You helped a lot, thank you! As someone already mentioned, it is hard to find a tutorial which explains MVC in simple words.

  4. Nice Dude!! I’ve been looking for a simplified example forever, this was the most concise thing I’ve ever come across. Really appreciate the article.

Leave a Comment

Your email address will not be published. Required fields are marked *