What are some best practices for handling database queries and object manipulation in PHP scripts?

When handling database queries and object manipulation in PHP scripts, it is important to use parameterized queries to prevent SQL injection attacks and to properly handle errors that may occur during database operations. Additionally, it is recommended to separate database logic from presentation logic by using a separate class or function for database operations. 

// Example of handling database queries and object manipulation in PHP scripts

// Establish a database connection
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

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

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

// Example of a parameterized query to prevent SQL injection
$stmt = $conn->prepare("SELECT * FROM users WHERE username = ?");
$stmt->bind_param("s", $username);
$stmt->execute();
$result = $stmt->get_result();

// Example of handling errors during database operations
if (!$result) {
    die("Error executing query: " . $conn->error);
}

// Example of separating database logic from presentation logic
class User {
    private $conn;

    public function __construct($conn) {
        $this->conn = $conn;
    }

    public function getUserByUsername($username) {
        $stmt = $this->conn->prepare("SELECT * FROM users WHERE username = ?");
        $stmt->bind_param("s", $username);
        $stmt->execute();
        $result = $stmt->get_result();

        if (!$result) {
            die("Error executing query: " . $this->conn->error);
        }

        return $result->fetch_assoc();
    }
}

// Usage of the User class
$user = new User($conn);
$userData = $user->getUserByUsername("john_doe");

echo "Username: " . $userData['username'];

Keywords

SQL injection PDO prepared statements ORM data validation

Related Questions