What are the best practices for handling database queries and results in PHP to avoid errors and improve efficiency?

When handling database queries and results in PHP, it's important to properly sanitize input to prevent SQL injection attacks, use prepared statements to improve efficiency and security, and handle errors gracefully to avoid unexpected behavior. One way to achieve this is by using PDO (PHP Data Objects) to interact with the database. 

// Establish a database connection using PDO
$dsn = 'mysql:host=localhost;dbname=mydatabase';
$username = 'username';
$password = 'password';

try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die('Connection failed: ' . $e->getMessage());
}

// Prepare and execute a query using PDO prepared statements
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$id = 1;
$stmt->execute();

// Fetch results using PDO fetch methods
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // Handle each row of results
}

// Close the database connection
$pdo = null;

Keywords

SQL injection PDO prepared statements error handling query optimization

Related Questions