MySQL in PHP - how to deal with databases

How I handle MYSQL database transactions in PHP

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

column key
id primary, auto-increment
name

Table user_email

column key
id primary, auto-increment
user_id
email
confirmation_string
confirmed

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=MySQL in PHP - how to deal with databases', $newUser);
// insert into user_email
Db::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 = EFBFBD1C4DEFBFBD32EFBFBD11EFBFBD', $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 = EFBFBD1C4DEFBFBD32EFBFBD11EFBFBD, $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.

neoan @neoan

333 readers
Keywords
mysql beginner php
On topic

Install PHP 7.4 on Ubuntu

Finally PHP 7.4 is out! You have read about the new features, you have followed externals, you have gathered ideas on how new capabilities will save time. Let's get it running.

Static content pages - still the fastest web-experience

Tutorial: How to utilize blua.blue to generate static content for your website.

Scaffolding REST APIs with JWT authentication

Ever had the need for your own backend while developing your web-app?

Git: globally change GitHub-remotes from git@ to https

Does your IDE or composer set remote repositories to ssh rather than https? Or are repositories you are using set up that way? You are not alone. Let's fix it once and for all!

VueJS & neoan3: a love story.

Setting up neoan3 to play nice with Vue isn't hard. Here is how the two frameworks are combined to support fast, dynamic and rapid development.

SEO strategies for blua.blue

How to get your content listed where you want it to.

MySQL: ERROR1364 fix

The painful realization of why people use containers.

blua.blue PHP SDK

Create your own blog.

How to Build an Express App in Node That Reads From a Headless CMS

A headless CMS lets you design your own front-end, sometimes your own back-end. Let's set up a small application to get us started. Beginner friendly. Approx. 20 min. to reproduce / follow along

Cross publishing to dev.to

How to publish your content to dev.to from blua.blue - hopefully

How to install global npm packages without sudo on Ubuntu

Running npm on a server can be painful. Privileges are there for a reason, and so is sudo. Running npm with sudo is not the solution.

Transformer - falling in love with PHP's magic methods all over again

PHP's magic functions have been around for a long time. But there were always good reasons to avoid them. Transformer is a practical and reliable way to make use of the most common operations we program: CRUD operations.

What is composer?

Composer has become PHP's package & dependency manager. Why you should use it.