I write on, and

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:

    "name": "joe1998",
    "emails": [
            "email": "",
            "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.


In our project folder:

composer require neoan3-apps/db

Creating a connection

    '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' => ''
// 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_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 = {{id}}', $wanted);
    // 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' => '',

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


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.

Install PHP 8 on Ubuntu

PHP8 has finally been released and it is FAAAAST! Time to update.


6 Technologies to look at in 2021

2020 wasn't the best year humans can look back to. but what does 2021 bring for coders?


CSS Grid or CSS framework - are they really exclusive?

Grid-based or framework - there are many articles about this choice. But why is that even a thing?


Scaffolding REST APIs with JWT authentication

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


What is composer?

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


MySQL: ERROR1364 fix

The painful realization of why people use containers.


Cyber wars: Defending your server

Maintaining your own server can be a thrill. High security standards can protect you from data leakage, injection attacks and DDoS attempts. But what about adaptive brute force?

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.


MySQL in PHP - how to deal with databases

How I handle MYSQL database transactions in PHP

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.

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.


Static content pages - still the fastest web-experience

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

image PHP SDK

Create your own blog.

image plugin for headless CMS (part 2)

A solution to supplying plugins to


Cross publishing to

How to publish your content to from - hopefully

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.

The Uselessness of Pre-Assessment

After almost two decades in the industry, new jobs will still ask you for "assessment tests". A little rant...


SEO strategies for

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


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


Help us document neoan3

Over 4000 brave developers are exploring the framework on their own.


When politics kill innovation

How misunderstood diversity killed the PHP Central Europe Conference for good.