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 Stack*overflow*, 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": "[e-mail]",
            "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' => '[e-mail]'
];
// 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);
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' => '[e-mail]',
            ...
        ]
    ]
]

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.

Comments

[Ted Ngeene]

Hey nice post. So assuming the user has more than one email and only wants to be logging in with the latest email, how would you handle that?

[neoan]

So in your scenario you would want the user to have multiple verified emails but authentication only works with the latest?
As mentioned in the post, I would normally have a column 'insert_date' in every table set up to insert the current timestamp on creation as well as a 'delete_date' defaulting to NULL.

In such a case I would modify my authentication logic accordingly ( I currently handle it like this: neoan3 user-model ).

Assuming the user passes in an email and a password, I would

1. retrieve the row in user_email by the provided email

2. use the retrieved info (if existing) to query again by user_id to ensure the passed in email address was the latest one

3. in success case, proceed with regular password verification

The code could look like this:

$assumedUserInput = ['email' => '[e-mail]', 'password' => '123456'];

// 1. find user-id (using easy here instead of SQL)
$existingUser = Db::ask('user_email.*',['email' => $assumedUserInput['email'], 'delete_date' => '']);

if(empty($existingUser){
    // throw error, return false, however you handle it
}
// 2. get latest email of user
$latestEmail = Db::easy('user_email.email', ['user_id' => $existingUser[0]['user_id'], 'delete_date' => ''],['orderBy'=>['insert_date','DESC'], 'limit'=>[0,1]]);

/**
* SQL equivalent:
* SELECT email FROM user_email WHERE user_id = ? ORDER BY insert_date DESC LIMIT 1
*/

if($assumedUserInput['email'] != $latestEmail[0]['email']){
    // throw error, return false, however you handle it
}

// then, authenticate (in our example we did not talk about passwords. Please have a look at the provided link if you have questions here)

DISCLAIMER
I am on the go and have not tested the above code. Also, sorry for missing line breaks here and there.

[Ted Ngeene]

Aah it's okay. I'll try out the code and your package to see if it does work