How can PHP developers ensure data consistency when checking for the existence of a username and inserting a new record in a database simultaneously?

When checking for the existence of a username and inserting a new record in a database simultaneously, PHP developers can ensure data consistency by using transactions. By wrapping the select and insert queries within a transaction, developers can guarantee that the database will remain in a consistent state even if multiple queries are executed concurrently. 

<?php

// Establish database connection
$pdo = new PDO('mysql:host=localhost;dbname=mydatabase', 'username', 'password');

// Begin transaction
$pdo->beginTransaction();

try {
    // Check if username already exists
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE username = :username");
    $stmt->execute(['username' => $username]);
    $count = $stmt->fetchColumn();

    if ($count == 0) {
        // Insert new record if username doesn't exist
        $stmt = $pdo->prepare("INSERT INTO users (username) VALUES (:username)");
        $stmt->execute(['username' => $username]);
        $pdo->commit();
        echo "User record inserted successfully.";
    } else {
        echo "Username already exists.";
    }
} catch (Exception $e) {
    // Rollback transaction in case of an error
    $pdo->rollBack();
    echo "Error: " . $e->getMessage();
}

Keywords

PHP data consistency database username record insertion

Related Questions