Managing the database layer with a basic model class - PHP

As I just mentioned in the preceding section, one of the core components that must be added to this MVC-based framework is a model that permits it to handle the corresponding data layer in an independent way. Since this layer will be composed of one or multiple MySQL database tables, the model will perform CRUD operations by using the MySQL driver developed in a previous tutorial.

Although a full-featured model should be abstract enough for handling all sorts of persistent storage mechanisms, and not just database tables, the one that I’m going to build next will use only the MySQL server. This is certainly good enough for demonstration purposes.

Having clarified that point, please look at the initial definition of the new model class, which is as follows:

class Model

{

private $db = NULL;

private $table = 'users';

// constructor

public function __construct(MySQL $db, $table = '')

{

$this->db = $db;

if ($table !== '')

{

$this->table = $table;

}

}

// get all rows from specified table

public function fetchAll()

{

$rows = array();

$this->db->query('SELECT * FROM ' . $this->table);

while ($row = $this->db->fetch())

{

$rows[] = $row;

}

return $rows;

}

// update/insert row

public function save(array $data, $id = NULL)

{

if (!empty($data))

{

foreach ($data as $field => $value)

{

$value = mysql_escape_string($value);

if (!is_numeric($value))

{

$data[$field] = ''' . $value . ''';

}

}

if ($id !== NULL)

{

$set = '';

foreach($data as $field => $value)

{

$set .= $field .'=' . $value . ',';

}

$set = substr($set, 0, -1);

$this->db->query('UPDATE ' . $this->table . ' SET ' . $set . ' WHERE id=' . (int)$id);

}

else

{

$fields = implode(',', array_keys($data));

$values = implode(',', array_values($data));

$this->db->query('INSERT INTO ' . $this->table . ' (' . $fields . ')' . ' VALUES (' . $values . ')');

}

}

}

From the previous code sample, it’s easy to see that the “Model” class implements a bunch of basic methods for selecting, inserting and updating records in a specified MySQL table. Please notice the injection of an instance of the MySQL driver via the class’ constructor, which allows you to expand the model to support additional database drivers.

Also, you should note that the model will work with a default “users” MySQL table, but naturally it’s possible to use a different table in accordance with your specific needs.

So far, so good. At this stage, the “Model” class can perform database selects, inserts and updates as well. Now you're wondering where the method that deletes records is, right? Well, it hasn’t been coded yet, but this will be fixed quickly.

In the final section of this tutorial, I’m going to define the remaining method, thus finishing the construction of the model class.

Go ahead and read the following segment. We’re almost done with the model!