The PHP Record Class

Welcome to the Record Class presentation! This class is what actually made me stick my head into the deep PHP OOP jungle. After studying a simply Ruby on Rails solution I thought I could give it a try implementing the same idea but with PHP. The main idea is to extract all the SQL commands necessary to handle inserts, updates and deletes into our Record Class. Except this goal of extracting the SQL commands the class should also handle:

• Retrieving a column value using this syntax: $object->column;
• Updating a column value using this syntax: $object->column = value;
• Updating a collection of column values via $_GET, $_POST and $_FILE;

If we are able to make this work we are able to save large submitted forms using just three lines of code! Our Forms Class will show an excellent example of how this will be used. So before we beginning I just want to point out that the Record Class will be integrated with an existing MySQL table. We will be able to get and set column values found in a specified table. We are going to use our existing Database class. I recommend you reading the Database class presentation before continuing. Okay! The goals and ideas are set and we have the ground to stand on. Let’s begin the dive into the Record Class pool!

We start with looking at the private class variables. There are 5 of those: $_table, $_primary, $_fields, $_db and $_id. And here is what the variables will hold for us:

Record private class variables

• $_table: the name of the current MySQL table we want to fetch our values from.
• $_primary: the name of the primary column in the MySQL table.
• $_fields: an array holding the names of the columns we want to fetch values.
• $_db: the Database instance.
• $_id: the current row id we are working on.

Together with these 5 private class variables we are going to look at 9 public class functions. The functions are presented in much more detail further down but we start with a short introduction:

• __construct: initializes the class. Sets our private class variables to the incoming parameters.
• __get: used to access a field using the syntax we were looking for $object->column;
• __set: used to update a field using the syntax we were looking for $object->column = value;
• find: we will use this when we want to search for a existing row in our table.
• set: makes it possible to update a collection of columns using the $_GET, $_POST and $_FILES vars.
• save: used to save the updated field values into our table in the MySQL database.
• delete: used to delete the current row we are working on.
• getValues: returns all field values in a array.
• view: prints out the current fields => value, used as a debug.

A rather long list of functions but very interesting! We take a look at the __construct() function:

Record __construct() method

The function takes 4 parameters, all required except the last one. The incoming parameters $table, $primary and $db have the same explanation as the private class variables. When we enter the function we are assigning those variables. The fourth and last parameter, $fields, is a bit special. We can either supply an array of names on fields found in the MySQL table or we can leave it be. If we leave it the __construct() function we use our newly created fetch_field_name() function from the Database Class to fetch the fields. This means: if we don’t supply our own array the class itself will fetch all fields available in that provided table. If we enter an array of names we will just be able to work with those columns specified in that array of names. When we have decided which fields we want we save them in a key => value array and sets the value to null by default. We also set the private class variable $id to 0.

Moving on. We wanted to be able to access a field by writing $object->column. This is done by using the __get() function, which is an built-in function provided by the OOP in PHP. It catches all undefined method calls. Since want this class to be dynamic we cannot add a get and set class to every possible value a field/column can have. The __get() function will catch our call when we write $object->column and basically ask us what we want to do. In our case we want to see if the column name exists in our array of fields. If it does we return that value, otherwise false. The function takes one argument, namely the name of the field we want to retrieve.

Record __get() method

Next up is the __set function which works exactly as the __get() function but catches assignments instead. The function takes two parameters, a key and value. The key is the name of a specific field. We make sure the key exists in our array of fields. If it does we assign the key with our new value and returns true, otherwise false.

Record __set() method

Now, a more interesting function, find(). As I described earlier this function is used when we want to find an existing row in our table. The function takes one required parameter, $id. This id must respond to the primary column field we provided in the __construct() class. If we have said that it’s the ‘id’ of a table that is the primary key we need to provide a $id in our find() function listed in the ‘id’ column. If we have chosen ‘name’ as the primary key/field we need to provide a unique ‘name’ to our find() function. Well inside the function we create a SQL string and uses our Database Class to fetch a result first and later we fetch an row as an object. We make sure the object we get isn’t null before we loop through our fields array and updates the values coming from our row object. If we don’t find anything we throw an exception.

Record find() method

This next function will handle our third requirement:

“Updating a collection of columns via $_GET, $_POST and $_FILE”

Even though we have a clean code to update a field in the database it becomes boring and it’s hard to concentrate if we are looking at a table containing over 20 different columns. Printing $object->column = value for each column doesn’t look good to me. What we can do is to take advantage of the $_GET, $_POST and $_FILE variables. The set() function takes two optional parameters $fields and $files. When we have a form, which we assume is already validated and the submit button has been pressed we can use our set() function to save each form field into our Record class and with just one more function call we can save the submitted form to the database. Sounds cool? It is! The set() function is rather self-explaining, the only thing that might be tricky is the last part. If we have a file and we have a value in the database we don’t want to overwrite that if we haven’t provide a new file when we edit a field.

Record set() method

I said we needed to make just one more function call to put the updated record into the database. This is done using the save() function. The function takes no parameters, it’s working with the private class variables declared and assigned in the __construct() function. In the beginning of the function we are creating a SQL string. We need to make sure each field has a value, otherwise we won’t add that value to database. We also need to check if the value is a integer or not, we only need to add ‘ if the value is a string to get a correct SQL string in the end.

Now, here is something cool. Remember we assigned the private class variable $id the value NULL? Every new record we create with this class doesn’t have a primary id, since the row isn’t saved in the database when we create it. We need to see if the current id is NULL, if it is we know that we should insert this record as a new row. Otherwise we do a update on the record corresponding to the supplied id. We use our Database Class to insert and/or update our record in the database. Rather sweet? I think so.

Record save() method

Okay, three more functions and then we are finished! The delete() function is simple. We make sure we don’t have the value of NULL on our private class variable $id and if that’s true we just delete the current row in the table corresponding to the $id value.

Record delete() method

This next function will be used with our Forms Class. What is does is straightforward: We make sure we have fields to work on and adds each field value into an array which we return in the end. This means what when we are done with the function the array will contain all values found in the array of field => value. Oh and it’s named getValues().

Record getValues() method

Okay, now we are almost there. Just one more function and then we will look at an example. The final public function is called view() and is used as a debug to see all field and their values.

Record view() method

Over 1600 words and I’m not finished, this is a long one! Okay, do we have a clue on what we have done? We have created a class working tightly with our Database Class which has extracted all the SQL commands you will normally use when inserting, updating and deleting records in a MySQL table. We are able to access the column values and update the column values in a very handsome way. We also made a find function making it possible to fetch values from the database in an instant. We can update and save incoming $_GET, $_POST and $_FILES in a matter of 2 lines of code! Or three if we haven’t created an instance of our Record class yet. Okay, now we need an example showing how to use this.

First we need to include our Database Class and our Record Class in our default PHP page. We create an instance of our Database class providing the database name, server, user and password. After that we create our Record class instance. We provide a table name, the primary key field name, the database instance and in the first case we don’t provide any fields. We assume there is a field called ‘name’ in our table and assign that field the value of ‘vailo’. We print that value on the screen and finally we do a save. Since we haven’t done a find() call we are inserting a new row in our database.

The second example shows how we can access an already existing row and update and save the updates into the database. We assume we find a row in the table with the primary key 1, we update the ‘name’ column with the value ‘Niklas’. After that we save it.

Pehu. Your thoughts, comments and suggests is as always very welcome. You will be able to download the full source of the Record Class including the updated Database Class below. Enjoy!

• Download the Record Class including the Database Class
  

Related PHP classes:

• The PHP Database Class
• The PHP Paging Class
• The PHP Form Class
• See the PHP class examples