Relationship Models for PHP and MySQL
Want to be in a relationship with a model? If that has always been a dream of yours, then I might be able to help. I firmly believe if you read this blog post, you’ll be able to attract any person you desire. Intelligence is an aphrodisiac.
This post is really about how I developed my own model capable of handling relationships in a database. That said, it is probably going to get more technical and longer than my other posts.
I wanted to create my own model instead of using a pre-existing solution because I needed a very simple solution. You may use the argument, “don’t reinvent the wheel,” and I will usually agree with you, but not this time. My simple solution did get a little more complicated when I wanted to add another feature. I kept adding features until it could do nearly any type of query that I may find myself using. Reinventing is a method of teaching myself new concepts that I may not normally have discovered.
How I Started
I first created an abstract class that all other models would extend. I chose an abstract class so I could write the logic in the methods and so no one should be able to instantiate it. A model only needs to extend the abstract class to be able to do any CRUD query.
Looking up synonyms for “model” on Google, I found the word “Illustrative” and thought it sounded cool. The name of my base model is called Illustrative. Next, I created a Person class that extends Illustrative. I then started adding some simple methods to the Illustrative class and using them on the Person class. A basic query is a “SELECT” statement so I started with that. I had my database credentials hardcoded in my Illustrative class and created a method to retrieve all the rows from the table. Instead of hardcoding the table name, I decided to make it the plural form of the class name, so “SELECT * FROM persons” was the query that was getting run.
At this point, I hadn’t done anything that excited me so I had to keep going.
When using a static method, you don’t need to instantiate the class, which is the way I like to do it. It really comes down to preference, as do most things, so do what you like.
A newly instantiated class is returned when you call one of the static methods that the model inherits from Illustrative. This gets things started and allows you to use method chaining to build your entire query. Below are two examples of how it can be used for instantiating and method chaining.
As long as I thoroughly document all the methods and their parameters, this will help someone who isn’t familiar with SQL retrieve rows from the database. I think it is verbose enough to English vernacular to make it seem familiar.
While your program should probably read from the database more than it writes, it still needs to be able to create new rows. Assuming the Baller class extends the Illustrative class, you could do something like below.
This allows you to dynamically assign new properties to the class and then run the save method to add the new row. Some people aren’t a fan of dynamically adding properties, so I made another way to do it. The other way is shown below.
In case you are wondering, yes, Alex and I are ballers.
Finding Certain Rows
At first, I retrieved all the rows from the database and filtered the results based on the parameters. I knew this wasn’t a good way to do it, but I did it anyway. I later went back and corrected it. It now uses SQL to limit the results from the database.
The example above is using the User model and running the static method fetch. By that, I hope you can deduce we are fetching a user. Instead of getting all of the users, we specify the where the method and pass an array to it. In that example, we are looking in the name field that is equal to “levi.”
While most of your queries may be simple, sometimes you have normalized your database and need to get information from multiple tables. I was a bit hesitant to move onto defining relationships, but I did, and I believe that is what makes this package useful.
Writing a query that includes a JOIN isn’t very difficult, but writing a method in PHP to accomplish this was a little challenging. I’ve used frameworks that can handle this, but I’ve never tried to figure out how they do it. I dug into some code written by others and I turned up with nothing.
I eventually got the logic worked out, but my query results weren’t matching what I expected. I was getting a little frustrated, so I decided to sleep on it and let my diffused mode thinking take over. I woke up the next day with a fresh outlook on the project and found my mistake. I also redid some of the logic of the other methods.
I am not claiming to be intelligent. My opening paragraphs are usually my attempt at humor, and as poorly done as they may be, I don’t think I’m going to stop.
You can’t force yourself to see what is wrong. Take a break. Go for a walk, take a nap, talk to a friend, or do something other than work on that project. You could also switch to another project. Doing these things allows your brain to go into the diffused mode and work on the problem. Even though you aren’t actively thinking about it, your brain is looking at the problem from a wider perspective. That doesn’t mean your brain stopped.
I still need to add an update method, but you can check out the Illustrative repo.