MySQL & PHP On Backend Best Practices

When building a backend in PHP using MySQL (and not only), it is important to follow some simple tips in order for the application to work correctly and efficiently.

Few small tips

Don’t mix DB queries and View

Because mixing the code for interacting with the database (business logic) and the views on the backend side subsequently leads to the fact that it will be very difficult for you to accompany and maintain the application (technical debt).

Modern frameworks, such as Laravel, Yii, CakePHP or Symfony components (etc.), suggest using the Model-View-Controller (MVC) pattern to separate the logic of working with database and view, and the connection between these abstractions is carried out in the intermediate controller.

Don’t trust data sent by users

Don’t use data entered by users directly in the database queries without validation and sanitisation, I recommend reading what SQL injections are. In short, if you don’t clean up user-entered data, at some point the user can submit a specially formed query and gain access to your database or currupt it.

Embedded PHP functions like filter_var() and filter_input() may help you sanitize and validate input data.

Don’t Repeat Yourself (DRY)

This is a programming pattern that encourages you not to repeat yourself when developing software, but instead try to reuse existing code. For example, in your project of several similar requests, you can create a function/method that will generate a request to the database from a template, and pass the value that can be changed through a variable.

Instead of:

$user1 = "SELECT id, name FROM users WHERE id = 1 LIMIT 1";
$user2 = "SELECT id, name FROM users WHERE id = 2 LIMIT 1";

Use something like that:

function getUserById(int $id) {
  if ($id <= 0) {
		throw new \OutOfRangeException("ID must be greater than zero");
  }
  return "SELECT id, name FROM users WHERE id = $id LIMIT 1";
}

$user1 = getUserById(1);
$user2 = getUserById(2);

Use prepared statement

Instead of forming a query using the mixing of variables and SQL directives, it is most practical to use prepared statements, such a feature will complicate the code a little, but allow you to worry a little less about the data submitted by users, because escaping in this case is done automatically.

Using ext-mysqli:

// Prepate the statement
$stmt = $mysqli->prepare('INSERT INTO products(label, order, price) VALUES (?, ?, ?)');

// Bind values to statemnt
$stmt->bind_param('sid', 'new product', 9, 123.45);
// "sid" mean
//  - "s" is type string (first value)
//  - "i" is type integer (second)
//  - "d" is type float/non-integer (third)

// Execute query
$stmt->execute();

Using PDO:

// Prepare the statement
$stmt = $pdo->prepare("INSERT INTO products(label, order, price) VALUES (:label, :order, :price)");

// Bind values to statement (PDO will detect type automatically)
$stmt->bindValue(':label', 'new product');
$stmt->bindValue(':order', 9);
$stmt->bindValue(':price', 123.45);

// Execute query
$stmt->execute();

Don’t keep unnecessary database connections open

In order to work with a database from PHP, we first need to connect to DB, then create a state, in which we can describe what query needs to be executed, and then return the data. Developers sometimes forget to close the connection after the work is completed, of course PHP and libraries can perform such tasks, but it’s safer to close everything manually just in case.

For example:

$stmt = $mysqli->prepare(...);
// Other code here
$stmt->execute();
// Processing response from DB

// Closing connection
$mysqli->close();

Almost the same is for PDO extension.

Try to avoid SELECT * queries

Such queries cause a large load on the DBMS, especially if you need to return a large set of data and often you don’t need data from all the columns of the table for subsequent work.

Instead of:

SELECT * FROM users;

Better to use something like that:

SELECT id, name FROM users;

Use LIMIT/OFFSET pair for pagination

Instead of displaying all the data from the table at once, it is more convenient to use pagination. You may use it on the database side with help of LIMIT and OFFSET directives.

LIMIT – allows you to limit the number of results to the specified number of rows.

OFFSET – indents the top by the specified number of rows.

For example:

SELECT id, price, created_at FROM orders LIMIT 10 OFFSET 10;

This ^ mean: return 10 rows from orders table with offset 10 rows from begin.

What to do if pagination is not applicable

But let’s say LIMIT/OFFSET doesn’t suit your case and you need to get all the rows from the table (or a very large amount of them).

In such cases, it is better to use an iterator, it allows you to process the data received from the database on the server side gradually, at one point in time the backend receives a small piece of information and processes it. This is much more efficient in terms of resources consumed than receiving a large amount of data for processing.

For example using ext-mysql:

$result = $mysqli->query($query);
while ($row = $result->fetch_array(MYSQLI_ASSOC)){
    // your code here
}

Using PDO:

$stmt = $pdo->query($query);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // your code here
}

Sliding window – another alternative for pagination

Sometimes there are situations when the possibilities of LIMIT/OFFSET is not enough, but it is still necessary to select data in blocks. Suppose we have a Cron script (which executed every hour) to fetch all data from the database, but ignore the data that was selected during previous runs of the script.

Here is small example of script

// Get last ID
$lastIdFile = '/tmp/lastId';
$lastId = 0;
if (file_exists($lastIdFile)) {
	$lastId = file_get_content($lastIdFile);
}

// Open connection to database
$pdo = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

// Select all rows with ID larger than $lastId
$stmt = $pdo->prepare("SELECT id, name, price FROM products WHERE id > :id ORDER BY id");
$stmt->bindValue(':id', $lastId);
$stmt->execute();

// Fetch all data
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
// ... some work with response ...

// Close connection
$pdo->close();

// Detect last ID
$lastItem = end($results);
$lastId = $lastItem['id'];

// Save last ID
file_put_content($lastIdFile, $lastId);

In this example, at the first start, we request all rows whose ID is greater than 0, after which we read the value of the last ID (let’s say it is 100) and save it, for example, on the file system.

After one hour 100 more records have been added during this time. Our script reads the value of the last ID from the file and selects all records with a value greater than 100. And so on.

To solve such problems, the Sliding Window principle is used.

Always keep passwords encrypted

If an attacker somehow gained access to your database, you must prevent the possibility of reading the passwords used by users, with this you may help embedded password encryption and password validation functions embedded to PHP.

Instead of:

$nickname = 'user1';
$password = 'secret_password';
$query = "INSERT INTO users (nickname, password) VALUES (:nickname, :password);"

Use something like this:

$nickname = 'user1';
$password = password_hash('secret_password');
$query = "INSERT INTO users (nickname, password) VALUES (:nickname, :password);"

Then for verification:

$password = 'secret_password';
$hash = '$2y$10$9trAUhBsQr.rTZuRz9T...7piFrj4weTgckSWvtwKqAVXnlRqq26y'; // secret_password
$isValidPassword = password_verify($password, $hash); // true
// or
$isValidPassword = password_verify('wrong_password', $hash); // false

Read more about password_hash() and password_verify().

Try to avoid subqueries, use JOIN instead

There are few ways to get intertwined data from multiple tables in a single query, this is usually done either with a subqueries or with a JOIN.

Subqueries are much more readable (human friendly) but usually require more server resources, while those implemented with the JOIN directive are less readable but have better performance (computer friendly).

For example you have two tables: accounts and users, and you need to select all users connected with an account, balance of which is larger than 100 USD.

Using subqueries:

SELECT id, name
FROM users
WHERE account_id IN (
  SELECT id
	FROM accounts
	WHERE balance >= 100
);

Using JOINs:

SELECT u.id, u.name
FROM users AS u
LEFT JOIN accounts AS a ON (u.account_id = a.id)
WHERE a.balance >= 100;

Use Transactions for safe from Race Conditions

Let’s analyze the situation when you have an example table and two scripts (A and B) that work with this table. Each of these scripts performs small updates of individual rows in the same column, each script first makes a request to the database, after which some analysis of the received data, after which it makes a change.

Script A

SELECT * FROM example WHERE id=1; // id:1, name:demo
UPDATE example SET name='test';

Script B

SELECT * FROM example WHERE id=1; // id:1, name:demo
UPDATE example SET name='zzz';

In some case, it may turn out that both scripts took data on the same row and are trying to update, usually only the data that was updated last (let’s say it was script B) is saved, because the data entered earlier (script A) is overwritten.

To prevent such situations transactions will help us. They are performed in three stages: the beginning of the transaction, the logic of working with data, the commit (that is, the completion).

START TRANSACTION;
SELECT * FROM example WHERE id=1; // id:1, name:demo
UPDATE example SET name='zzz';
COMMIT;

If an error occurred during the execution of this block before commit (for example, because another script changed the data), we can rollback and the database will not save the changes described inside the transaction.

ROLLBACK;

Using this technique will help you avoid a lot of unpleasant situations associated with the Race Conditions problem.

Conclusion

Of course, these are not all possible recommendations, only a basic set, it is easy to follow these recommendations, and if they are used, the quality and readability of the code will be much higher, and the code itself will be safer.