Using the MVC framework: setting up the database schema and building a controller class - PHP

To start building the MySQL-driven application that I mentioned in the introduction, I'm going to create a basic database schema. It will be composed of a single table containing records about some users.

With this table situated on top of the data layer, I'll demonstrate how simple it is to perform CRUD operations against it using the framework's components.

So here is the DDL code that creates the table in question. Take a look at it, please:

DROP TABLE IF EXISTS `test`.`users`;

CREATE TABLE `test`.`users` (

`id` INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,

`fname` VARCHAR(45) DEFAULT NULL,

`lname` VARCHAR(45) DEFAULT NULL,

`email` VARCHAR(45) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

So far, there's nothing special about the SQL commands used to built the above "users" table, except for the fact that it'll reside within a database called "test." If you want to place it in a different database, make sure to introduce the proper modifications into the previous code sample. Finally, note that the "users" table will house basic data on users, including their first and last names, and their respective email addresses.

If all has gone well, and the table has been built with no glitches, it's time to populate it with some records, so feel free to do that right now.

Done? Good. Having defined the database schema of this sample application, the next step is to create a controller class. The file containing the controller should be named "UsersController.php" and placed in the same folder in which the other classes of the framework reside, to make sure that everything has been correctly set.

This controller class will use the model defined earlier for selecting, inserting, updating and deleting users from the corresponding table, and the resulting outputs will be injected into different views.

Leaving the theory behind, the initial definition of the controller class will be as follows:

class UsersController

{

private $model = NULL;

// constructor

public function __construct()

{

// store model object as property

$this->model = new Model(MySQL::getInstance(array('host', 'user', 'password', 'test')));

}

}

As you can see, for the moment the user controller only implements its constructor, which creates an instance of the model class and connects it to MySQL via the corresponding driver. Not too difficult to grasp, right?

For obvious reasons, the controller in its current state isn't doing anything really useful. To make it a bit more functional, it'd be convenient to add a method that fetches all of the users stored on the pertinent MySQL table. This brand new method will be coded in the section to come. So click on the link below and keep reading.