MySQL in PHP - how to deal with databases

Dealing with relational databases

The most obvious problems beginners have when dealing with relational databases (and MySQL in particular) can be summarized into two main points:

• Database structure
• Transaction handling and

After seeing the same habits over and over again on Reddit or Stackoverflow, it's time to address some basics:

Database structure

Unfortunately, most learners nowadays start with NoSQL solutions like mongoDB. While there is nothing wrong with handling data that way, experience has shown me that an understanding of relational databases translates well into these solutions while learning relational concepts after working with NoSQL concepts seems to be harder. In order to plan a database structure, one should start with defining relations. We will assume a simple user-model throughout this article that can be visualized like this:

{"id":1,"name": "joe1998","emails": [{"id":1,"email": "joe@example.com","confirmation_string": "ABCDEFG","confirmed": true}]}

In general, there are the following relations:

• one-2-one
• one-2-many
• many-2-many (I will not cover this scenario here. If you are interested, please comment)

The first thing you notice is that "emails" is an array. So emails are one-2-many, meaning that one user can have multiple emails. I don't need that!, you might say, but consider the following scenario: A user wants to change the registered email address. However, when submitting the new email, a typo sneaks into the new email. Your app generates a new entry into emails and sends out a confirmation email including a generated "confirmation_string". However, in our scenario, this email never arrives due to the typo. In a worse case, this email could arrive at a different existing mailbox. Whatever happens: we do not want to overwrite the old email. At the very least not until the new one is confirmed. In such a state, the old email must be valid until a new email is confirmed. What does this mean for our database structure? Quite simple: emails need their own table with a relation to a particular user. Without going into foreign keys and types, let's assume the following

Table user

Table user_email

These two tables offer the capability to account for every scenario we have talked through before and transactions can be made accordingly.

NOTE: In a real scenario, you might want to equip your tables with standardized columns like "insert_date", "update_date" and (for soft-deletes) "delete_date" for better data-handling and understanding.

So having these tables set up, how would you go about dealing with data?

Transaction handling

Many use abstracted transaction handling via an ORM (looking at you, Laravelers). Solutions like Doctrine or Propel are clean solutions as well, but let's be honest: such approaches are hard for beginners and do not foster understanding or learning. This is why people learn how to use PDO or mysqli when starting out. However, this leads to sometimes harmless code-repetition and often dangerous injection possibilities. Therefore, let's use a library that abstracts the danger away without being too complex or too far away from native SQL: neoan3-db.

This library comes with a simplified markup for SQL (called easy) which we will ignore here and instead use the identifier ">" to write classical SQL. But let's not get ahead of ourselves.

Installation

In our project folder:

composer require neoan3-apps/db

Creating a connection

Db::setEnvironment(['name' => 'your_db','user' => 'root','password' => 'Som3S3cur3Pa55word'])

NOTE: This class is used statically. There is no need to initiate the connection each time, nor will the function "setEnvironment" create a connection. The first time you execute a query a connection will automatically be established should it not exist already. It is therefore possible to place the above snippet on a relatively high level in your application.

Working with data

Now that our setup is done, let's get the demo-data into the database:

$newUser = ['name' => 'joe1998','email' => 'joe@example.com'];// insert into user *// When auto-incremented ids are used, the function returns the new id$newUser['user_id'] = Db::ask('>INSERT INTO user SET name={{name}}', $newUser);// insert into user_emailDb::ask('>INSERT INTO user_email SET email={{email}}, user_id={{user_id}} confirmation_string = "ABCDE"', $newUser);

NOTE: Please always use the double curly brackets to refer to a value of a given assoc array by key! Under the hood, the class identifies the correct type and prepares a statement. You can then securely handle user-input.

* for clarity:In the documentation you will find magic methods and a simplified markup. As stated before, we want to keep it very understandable, but the user-insert can also be written like this:$id = Db::user(['name' => 'joe1998'])

Retrieving data

Now you are itching to try out the joins you worked so hard on? You could, but let's remember how our user model is supposed to look like. In order to create a format that is usable (as array) in PHP as well as (as JSON) by the front-end, we decide to use two transactions instead:

// first we want to row of the targeted user$wanted = ['id'=>1];$user = Db::ask('>SELECT * FROM user WHERE id = {{id}}', $wanted);if(!empty($user){// The function returns an array of results. Expecting only one result, let's assign the first result to $user$user = $user[0]// and then attach the email(s)$user['emails'] = Db::ask('>SELECT * FROM user_email WHERE user_id = {{id}}, $wanted]);}

And that's it! Our $user will now look like this:

['id' => 1,'name' => 'joe1998''emails' => [['id' => 1,'user_id' => 1,'email' => 'joe@example.com',...]]]

and can easily be passed to the front-end with "json_encode()"

Conclusion

There is a lot to be said when it comes to abstraction, using uuids, migration etc, but I hope this is a good starting point. If you have any questions, please don't hesitate to ask.