Are there any best practices for structuring PHP functions to handle database queries efficiently and securely?

When structuring PHP functions to handle database queries efficiently and securely, it is important to use prepared statements to prevent SQL injection attacks. Additionally, it is recommended to establish a separate database connection function to avoid code duplication and improve code maintainability. Lastly, consider implementing error handling to gracefully handle any database connection or query errors. 

// Function to establish a database connection
function connectToDatabase() {
    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "database";

    $conn = new mysqli($servername, $username, $password, $dbname);

    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    return $conn;
}

// Function to execute a prepared statement
function executePreparedStatement($conn, $sql, $params) {
    $stmt = $conn->prepare($sql);
    
    if ($stmt === false) {
        die("Error preparing statement: " . $conn->error);
    }
    
    if (!empty($params)) {
        $stmt->bind_param(str_repeat('s', count($params)), ...$params);
    }
    
    $stmt->execute();
    
    $result = $stmt->get_result();
    
    if ($result === false) {
        die("Error executing statement: " . $stmt->error);
    }
    
    return $result;
}

// Example usage
$conn = connectToDatabase();
$sql = "SELECT * FROM users WHERE id = ?";
$params = [1];
$result = executePreparedStatement($conn, $sql, $params);

while ($row = $result->fetch_assoc()) {
    echo "Name: " . $row['name'] . "<br>";
}

$conn->close();

Keywords

PHP functions database queries efficiency security

Related Questions