What best practices should be followed when handling database connections and queries in separate PHP files for better code organization and maintenance?

When handling database connections and queries in separate PHP files for better code organization and maintenance, it is recommended to create separate files for database connection and query functions. This separation helps in modularizing the code and makes it easier to maintain and debug. Additionally, using functions for common database operations can help in reusability and readability of the code. 

// db_connect.php
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

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

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

// db_queries.php
<?php
include 'db_connect.php';

function get_users() {
    global $conn;
    
    $sql = "SELECT * FROM users";
    $result = $conn->query($sql);
    
    if ($result->num_rows > 0) {
        return $result->fetch_all(MYSQLI_ASSOC);
    } else {
        return [];
    }
}

function add_user($name, $email) {
    global $conn;
    
    $sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";
    
    if ($conn->query($sql) === TRUE) {
        return true;
    } else {
        return false;
    }
}
?>

Keywords

PDO MySQLi SQL injection prepared statements database abstraction layer

Related Questions