Basically all the main programming languages currently have ORM frameworks (Object Relational Mapping), which help a lot the development of applications (under the object orientation paradigm – OOP) that persist in relational databases (like MySQL, Postgre, SQL Server, among others).
The function of an ORM framework is to abstract objects from a database (tables, columns, relationships, and so forth), map them, and make them readable within a project being developed in OOP.
As an example of ORM frameworks, we have Hibernate for Java, NHibernate for the .NET platform and when the programming language in question is PHP, we can mention Doctrine. This framework is quite robust and provides a high list of resources, however its learning curve is relatively steep.
Therefore, the purpose of this article is precisely to present you with a “good and cheap” alternative (laughs) to do the object-relational mapping of your php project. Relational (https://github.com/Respect/Relational).
It is worth mentioning that, in addition to mapping the database (relational), the ORM frameworks also offer classes / libraries that make the basic insertion, editing, deletion and search procedures extremely simple.
P.S.: On my youtube channel I made available a playlist with 5 video lessons where I show how to use Relational ORM. To check it out, visit: https://www.youtube.com/watch?v=E8LDJFKfMdM&list=PLwQkYMetu0OZ6uza5WTSe5RDZya_r2Gfr
For the examples that we will expose here in this post, we will use a database with the tables author (id AUTO INC, name) and post (id AUTO INC, title, content, postdate and author_id). The framework documentation itself recommends that the primary keys (PK) of the tables be defined as “id” and the foreign keys (FK) follow the standard “table_id“(Ex: the foreign key coming from the “author” table will be called “author_id“).
The approach on installing / using the ORM Relational framework will be the following roadmap:
- Installation via Composer
- Connection to the database
- Insert example (INSERT)
- Simple search example (SELECT)
- Editing example (UPDATE)
- Deletion example (DELETE)
- Search example (SELECT) using “JOIN”
Installation via Composer
To install the Relational framework, we will use the Composer package manager (https://getcomposer.org/). Open your command line terminal (or prompt), go to your project’s root folder and enter the following command:
composer require respect/relational |
This command will download all the framework files and add them into a folder called “vendor”, within your project.
Connection to the database
With the framework attached to the project, now we will create a file (it can be the index.php) and connect to the database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | require_once('vendor/autoload.php'); use Respect\Relational\Mapper; $mapper = null; try{ $mapper = new Mapper( new PDO('mysql:host=localhost;port=3306; dbname=DBNAME', 'DBUSER', 'DBPASS') ); } catch(Exception $e){ echo $e->getMessage(); } |
In line 1 I import the file “autoload.php” (created by the composer himself). From this file, I can access all classes available within the Relational framework.
The class that will do the mapping of the database and allow the execution of procedures in the database (insert, edit, delete and search) will be the “Respect\Relational\Mapper“.
Within try/catch I instantiate the connection to the database via PDO (in the example I use MySQL, but you can feel free to use another DBMS, including SQLite). The generated connection instance is assigned to the variable “$mapper” and now from this variable we will be able to work with the database in an extremely simple way.
Insert example (INSERT)
To insert it into the database, simply create a standard php object (stdClass) and then pass it as a parameter to the $mapper variable. The attributes of this object must be the columns of the referring table in the database. For example, for the “author“, we will “set” the attribute “name” (I don’t need to define the “id” at the time of insertion, as it is defined as auto increment). Look:
17 18 19 20 21 22 23 24 25 26 27 28 | //Create a var of type stdClass //"author" -> "id" (autoinc), "name" $author = new stdClass; $author->name = "Joseph"; //"persist" saves information to memory $mapper->author->persist($author); //"flush" save permanently from DB $mapper->flush(); echo 'Author with ID #' . $author->id . " has been created"; |
After defining the values inside the “$author” object, I pass it as a parameter to the “persist” method (line 23), which will save this data, for the time being, only in memory. In line 26, the “flush” method is executed and this saves the data into the database permanently. With this scheme, it is possible for you to do several procedures at the memory level (using “persist”) and then make a single “flush” to save everything at once.
See now an example of insertion for the table “post“, which has the columns “title“, “content“, “postdate” and “author_id” (the author named “Joseph”, except in the previous example was defined with the id = 1. Soon, we will use it to save the “post”).
29 30 31 32 33 34 35 36 37 38 39 | $post = new stdClass; $post->title = "Sixth post"; $post->content = "Content of sixth post"; $post->postdate = date('Y-m-d H:i:s'); //get the date/hour from server $post->author_id = 1; //author created in the previous example $mapper->post->persist($post); //save to memory $mapper->flush(); //save to database echo 'Post #' . $post->id . ' published successfully!'; |
Simple search example (SELECT)
In the same way that we have methods to insert, we also have methods to search for data saved in the database. Let’s see an example of how to search for all authors registered in the database:
1 2 3 4 5 6 | echo '<h2>List of authors</h2>'; $authors = $mapper->author->fetchAll(); //returns a list of all authors foreach ($authors as $author){ echo 'ID: ' . $author->id . '<br />'; echo 'Nome: ' . $author->name . '<br /><br />'; } |
The “fetchAll” method will search for all authors registered with the database. In the example, it returns a list (array) of objects for the variable “$authors“. Each position in this array has an object (stdClass) with the attributes “id” and “name” (which are the columns of the “author” table).
To do a specific search for an “author” by his “id“, we can use the “fetch” method.
1 2 3 4 | echo '<h2>Searching author with ID = 3</h2>'; $author = $mapper->author[3]->fetch(); //select the author with id=3 echo 'ID: ' . $author->id . '<br />'; echo 'Name: ' . $author->name . '<br /><br />'; |
To search for the “id” (PK), we pass the value of this “id” in the attribute index.
To do a search and use another column in the table as a condition, pass an array where the position index is the column name and the value is the parameter to be searched. Example:
1 2 3 4 | echo '<h2>Searching the author with name = Joseph</h2>'; $author = $mapper->author(['name' => 'Joseph'])->fetch(); echo 'ID: ' . $author->id . '<br />'; echo 'Nome: ' . $author->name . '<br /><br />'; |
In this case, it will return the first found author where the “name” = “Joseph”. If you need all authors with that name, replace the “fetch” method with “fetchAll“.
Editing example (UPDATE)
To update a record, we first need to get it from the database. For example, let’s assume that we need to update the information in the post that has “id” = 6.
1 2 3 4 5 6 | $post = $mapper->post[6]->fetch(); //get the "post" with id = 6 //I want to change the "content" and "author_id" $post->content = 'This is the new content of the sixth post'; $post->author_id = 2; $mapper->post->persist($post); //save to memory $mapper->flush(); //save to DB |
In the example above, I search and load all the data from the “post” of “id = 6” into the “$post” variable. I replace the “content” and “author_id” attributes with the new values and then I pass the variable again so that the “$mapper” makes the “update” in the database.
Deletion example (DELETE)
To delete a record, we also need to retrieve it from the database first. In the following example, we will delete the “post” that has “id = 4”.
1 2 3 4 5 6 7 | //remove a record from the "post" table. //first, it searches for the record to be removed $post = $mapper->post[4]->fetch(); echo 'Deleting post #' . $post->id . ' ('. $post->title.')'; $mapper->post->remove($post); //save the changes to memory $mapper->flush(); //execute the DELETE to DB. |
Search example (SELECT) using “JOIN”
As it is very simple to insert, edit, delete records in a relational database using Relational, the same happens when we need to do a search with “joins” in 2 or N tables.
For example, to search our database for all “posts” and their respective “authors“. In this case, we must first inform the table that “receives” the FK (table “post“, which receives the “author_id“), and then the table that provides the FK (table “author“).
1 2 3 4 | $rsPostAuthor = $mapper->post->author->fetchAll(); //JOIN between "post" and "author" foreach ($rsPostAuthor as $reg){ echo $reg->title . '(' . $reg->author_id->name . ') <br /><br />'; } |
Each record returned will be converted to an “stdClass” object. This object will have all the information in the “post” and within the “author_id” attribute of that object, it will have another object that is precisely the information of the author (“id” and “name“).
It is also possible to insert conditions in queries with JOINS (in both tables). Here are some examples:
1 2 3 4 5 6 7 8 9 10 11 | //get the informations of post with id = 1 $rsPost1 = $mapper->post[1]->author->fetch(); //get all posts from author with id=3 $rsAuthor3 = $mapper->post->author[3]->fetchAll(); //get all posts from author with "name" = "Adam" $rsAuthorAdam = $mapper->post->author(["name" => "Adam"])->fetchAll(); //using the LIKE operator $rsLike = $mapper->post(['title LIKE' => '%Sixth%'])->author->fetchAll(); |
To conclude, see how easy but efficient it is to adopt the “Relational” ORM framework for php projects. It provides a lightweight and secure solution for manipulating data in the database and searching for them. Using it, we don’t have to worry about creating insertion, editing, deletion, search queries, and so forth. as the methods for these activities are already available. Therefore, we can only be concerned with the business rules of our project and consequently its development becomes more agile.
I hope you enjoyed this content and any questions leave your comment.
See you later.
Rafael Wendel Pinheiro
Holds a university degree in Information Systems, a postgraduate degree in Database Systems and a master's degree in Education with a focus on Sociocommunity Technologies. He works as a professor of technical and technological education at the Federal Institute of Education, Science and Technology of São Paulo, teaching subjects in the areas of programming, database, project development and software engineering.
