Retrieving user-related data from a MySQL table - PHP

As I anticipated in the beginning, the first step involved in the development of this MySQL-driven application will require defining a model class. In crude terms, this class will act as a simple interface for retrieving user-related data from a sample “users” MySQL table.

To clarify a bit more how this class is going to work, please take a look at its signature, which is listed below:

class Users extends Model{

function Users(){

// call the Model constructor

parent::Model();

// load database class and connect to MySQL

$this->load->database();

}

function getAllUsers(){

$query=$this->db->get('users');

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

// return result set as an associative array

return $query->result_array();

}

}

function getUsersWhere($field,$param){

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

$query=$this->db->get('users');

// return result set as an associative array

return $query->result_array();

}

// get total number of users

function getNumUsers(){

return $this->db->count_all('users');

}

}

As shown above, the previous “Users” class extends the model that comes included with Code Igniter, and has been provided with a set of intuitive methods that come in useful for retrieving some rows from a “users” database table. As you can see, the class in question is capable of fetching one row at a time and multiple records as well, and additionally has the ability to count the number of rows contained within a result set.

However, the most interesting facet of this model class is the way that it implements all of its methods. In this particular case, the constructor first loads the database class bundled with Code Igniter via a loader object, and then connects to MySQL using the set of parameters specified in the “database.php” file that you learned in the previous article.

From this point onward, the model uses some methods provided by the aforementioned database class to fetch user data from the corresponding “users” MySQL table.

Of course, I’m not going to list the complete API corresponding to the Code Igniter database class, since you can visit its web site for a full reference on them. But here’s a brief description of the methods used by the previous “Users” model:

$this->db->get('table_name'): get all the rows from the specified MySQL table

$this->db->where($field,$param): performs a “where” query against the specified MySQL table

$this->db->count_all('table_name'): count all the rows in the specified MySQL table

$query->result_array(): returns a result set as a multidimensional array

$query->num_rows(): counts the number of row returned in a result set

As you can see, the built-in Code Igniter database class includes a group of straightforward methods, which can be used to perform the most common database-related tasks, such as fetching and counting rows, handling data sets, and so forth. As I stated before, however, you should read Code Igniter’s user guide for further information about how these methods work.

Well, at this point I showed you how to create a simple “Users” model class that can be utilized for retrieving and counting rows from sample MySQL table. Before I forget, you should save the model to the /system/application/models/ folder of Code Igniter, so it can load it correctly later on.

Provided that you grasped the logic that drives this class, the next thing that I’m going to teach you will be how to build a controller class. This class will perform a few useful tasks, such as retrieving information from the table by using the model’s API, and embedding this data straight into a view file.

To learn how this controller class will be built, please jump forward and read the following section.