Optimization of SQL Queries Usage in PHP

Executing SQL queries to the database is a very slow operation. Therefore, it is necessary to try to minimize the number of queries to the database.

The most common mistake is executing many SQL queries in a loop, when there are alternative queries that allow achieving the desired result with a single command.

Let's look at an example.

Suppose we are given an array with id of records in the database and we need to delete records with such id.

Without thinking twice, one might write such a "wonderful" construct:

<?php
	$ids = [1, 2, 3, 4, 5]; // array for deletion
	
	foreach ($ids as $id) {
		mysqli_query($link, "DELETE FROM users WHERE id=$id");
	}
?>

The problem with this code is that it executes many queries in a loop. Imagine that the array contains 100 elements - 100 queries to the database will be executed!

The particular trap of the described task is that during website development the database is small and queries execute very quickly. However, as the website runs on the hosting, the database will grow, queries will execute slower and slower, and it may come to the point where due to the execution of a hundred queries in a loop the website will load for more than a minute!

With very little effort, it is possible to make it so that only one query is executed, like this:

<?php
	$ids = [1, 2, 3, 4, 5];
	
	$str = implode(',', $ids); // get the string '1,2,3,4,5'
	mysqli_query($link, "DELETE FROM users WHERE id IN ($str)");
?>

Optimize the code below:

<?php
	$arr = [1, 2, 2, 4, 5];
	$res = [];
	
	for ($i = 1; $i < count($arr); $i++) {
		$note = mysqli_query($link, "SELECT * FROM users WHERE id=$id");
		for ($data = []; $row = mysqli_fetch_assoc($note); $data[] = $row);
		
		$res[] = $data;
	}
	
	var_dump($res);
?>