In PHP and MySQL, there are different ways to manage database connections. These include short connections, long connections, and connection pooling.

1.Short connections

Short connections refer to connecting to the database only when necessary and disconnecting immediately after the query is executed. This method is suitable for applications with a low volume of database transactions or those that do not require persistent database connections. Short connections are relatively simple to implement and are suitable for small-scale applications.Here’s PDO short connection example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 // Database credentials
$host = "localhost";
$user = "username";
$password = "password";
$database = "database_name";

// Establish a database connection
try {
    $conn = new PDO("mysql:host=$host;dbname=$database", $user, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
}
catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

// Execute a query
$sql = "SELECT * FROM table_name";
$result = $conn->query($sql);

// Process the query results
if ($result->rowCount() > 0) {
    while($row = $result->fetch()) {
        echo "Column 1: " . $row["column1"] . " - Column 2: " . $row["column2"] . "<br>";
    }
} else {
    echo "0 results";
}

// Close the database connection
$conn = null;

2.Long connections

Long connections refer to maintaining a persistent connection to the database throughout the life of the application. This method is suitable for applications that require frequent database access, and it eliminates the overhead of establishing a new connection each time a query is executed. However, it may lead to resource consumption on the database server, especially in applications with a large number of concurrent users.You can establish database long connections creating a PDO

Connection and setting PDO::ATTR_PERSISTENT, here’s an example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
  // Database credentials
$host = "localhost";
$user = "username";
$password = "password";
$database = "database_name";

// Establish a database connection
try {
    $conn = new PDO("mysql:host=$host;dbname=$database", $user, $password, array(PDO::ATTR_PERSISTENT => true));
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
}
catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

// Execute a query
$sql = "SELECT * FROM table_name";
$result = $conn->query($sql);

// Process the query results
if ($result->rowCount() > 0) {
    while($row = $result->fetch()) {
        echo "Column 1: " . $row["column1"] . " - Column 2: " . $row["column2"] . "<br>";
    }
} else {
    echo "0 results";
}

// Close the database connection
// Note: The connection will persist even after the script execution ends
$conn = null;

3.Connection pools

Connection pooling involves maintaining a pool of pre-established database connections that can be reused by multiple clients. This method helps reduce the overhead of establishing new connections and can improve application performance. Connection pooling is suitable for applications that require a large number of database connections and are designed to handle high volumes of traffic.

In MySQL, you can configure the server to support connection pooling using tools such as mysqlnd or third-party libraries such as mysql-proxy. These tools help manage the connection pool and ensure that connections are properly maintained and reused.

Should I Use MySQLi or PDO?
In PHP, you can establish database connections using the mysqli or PDO extensions. Both extensions support short and long connections. Connection pooling can be implemented using third-party libraries or extensions, such as php-pgsql and php-mysqlnd-ms.

Both MySQLi and PDO have their advantages:

PDO will work on 12 different database systems, whereas MySQLi will only work with MySQL databases.

So, if you have to switch your project to use another database, PDO makes the process easy. You only have to change the connection string and a few queries. With MySQLi, you will need to rewrite the entire code - queries included.

Both are object-oriented, but MySQLi also offers a procedural API.
Both support Prepared Statements. Prepared Statements protect from SQL injection, and are very important for web application security.