PHP to detect active connections to a database

Detecting Active Database Connections in PHP

Code Solution

// Connect to the database
$mysqli = new mysqli("localhost", "username", "password", "database");

// Execute a query to display the currently active connections
$result = $mysqli->query("SHOW PROCESSLIST");

// Fetch the results into an array
$connections = $result->fetch_all(MYSQLI_ASSOC);

// Display the results
echo "<ul>";
foreach ($connections as $connection) {
    echo "<li>{$connection['Id']} {$connection['Info']}</li>";
}
echo "</ul>";

Explanation

This PHP code demonstrates how to detect the active connections to a MySQL database using the SHOW PROCESSLIST query. Let’s break down how it works:

  1. Database Connection: We establish a connection to the database using the provided host, username, password, and database name.

  2. SHOW PROCESSLIST Query: The SHOW PROCESSLIST query is executed on the connected database. This query displays a list of all the currently active connections and processes on the database server.

  3. Fetch Results: The fetch_all() method is used to retrieve all the rows from the query result and store them in an associative array connections.

  4. Display Results: Finally, we iterate over the connections array and display the details of each active connection, including the connection Id and the Info (query or command being executed).

Effective Implementation

To implement this code effectively, consider the following guidelines:

  • Use Prepared Statements: Prepared statements can help prevent SQL injection vulnerabilities.
  • Safeguard Database Credentials: Always store database credentials securely and avoid hardcoding them in the code.
  • Limit Database Queries: Only execute the necessary database queries to avoid performance bottlenecks.
  • Handle Exceptions: Implement proper error handling to catch and handle any database-related exceptions gracefully.
  • Close Connections: Always close database connections when they are no longer needed to release resources and avoid connection leaks.