Creating sample MySQL tables and defining a model class - PHP

We'll begin building this content management system with Code Igniter by creating a couple of basic MySQL tables. The first one will be used for storing information about a few movies, including the corresponding titles and a brief description, while the second table will be utilized for inserting comments about each of them.

Based on this database schema, the PHP application that I plan to develop will let users enter several comments on a particular movie by means of an HTML form.

Having clarified this point, I’m going to create the first sample MySQL table, which will be called “movies,” with a structure that looks like this:

As you can see, the “movies” table contains data about a few relatively recent movies, and it’s very simple to grasp. However, there is still one more step to take. We need to create an additional table for entering comments on each of the previous movies.

The empty structure of this second table, called “comments,” can be seen below:

Now that the two sample tables have been properly created, everything is ready for us to begin developing this movie-related content management system. Therefore, to interact with these tables, I’m going to define a model class that permits us to achieve this goal in a simple manner.

The signature of this model class is as follows:

class MovieModel extends Model{

function MovieModel(){

// call the Model constructor

parent::Model();

// load database class and connect to MySQL

$this->load->database();

}

// fetch all rows

function fetchAllRows($table){

$query=$this->db->get($table);

if($query->num_rows()>0){

// return result set as an associative array

return $query->result_array();

}

}

// fetch rows based on a certain condition

function fetchRow($param,$field,$table){

$this->db->where($field,$param);

$query=$this->db->get($table);

// return result set as an associative array

return $query->result_array();

}

// insert row

function insertRow($table,$data){

$this->db->insert($table,$data);

}

// get total number of rows

function getNumRows($table){

return $this->db->count_all($table);

}

}

As illustrated above, the logic that drives the previous “MovieModel” class is fairly easy to understand. Basically, all that this class does is implement a group of methods for fetching and inserting rows into a selected MySQL table, as well as for returning the total number of records contained within a result set.

This model class should be saved to the Code Igniter /system/application/models/ folder as “moviemodel.php” for later use.

Okay, assuming that you’ve read the previous articles of this series, then you should be pretty familiar with the structure of the above model class, right? Therefore, it is time to take the next step involved in the development of this content management system, which obviously consists of defining a controller.

As you’ll see in a moment, the controller will be responsible for displaying all of the movies contained in the “movies” MySQL table that you saw before. It will also let users enter different comments on each of them, via a simple HTML form.

To see how this brand new controller class will be created, please visit the following section and keep reading.