Skip to content
PHP Logo

In PHP, what are some effective ways to handle database queries and transactions to ensure data integrity and security?

To ensure data integrity and security when handling database queries and transactions in PHP, it is important to use prepared statements to prevent SQL injection attacks, validate user input before executing queries, and handle errors gracefully to prevent data corruption.

php
// Establish a database connection
$dsn = 'mysql:host=localhost;dbname=mydatabase';
$username = 'username';
$password = 'password';
$options = array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false
);
try {
    $pdo = new PDO($dsn, $username, $password, $options);
} catch (PDOException $e) {
    die('Database connection failed: ' . $e->getMessage());
}

// Prepare a SQL statement
$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username');

// Bind parameters and execute the statement
$username = 'john_doe';
$stmt->bindParam(':username', $username, PDO::PARAM_STR);
$stmt->execute();

// Fetch results
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($results as $row) {
    echo $row['username'] . '<br>';
}