Check if username exists in mysql table via php?

Check if username exists in mysql table via php?

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Check if username exists in MySQL table via PHP

Your current registration script attempts to insert a record directly without verifying whether the username is already in use. The immediate consequence of this approach is a duplicate account creation, which breaks data integrity and confuses users trying to log in later. To fix this, you need to shift from a pure INSERT operation to a conditional workflow that queries the database first.

In your provided code snippet, the logic flows linearly: sanitize inputs, hash the password, and execute an INSERT statement. If the username already exists, MySQL will throw an error depending on your table constraints. While relying on an exception to handle UI feedback is technically possible, it creates a poor user experience. The standard pattern involves checking existence explicitly before attempting to write.

Using SELECT Before INSERT

The most straightforward method is to run a SELECT query against the users table using the sanitized username. You should check for rows where the username column matches the input value. If the result set is empty, proceed with the insertion; otherwise, display an error message.

Here is how you can refactor your logic:

$con = mysqli_connect("localhost", "root", "", "users");
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// Sanitize and process inputs
$hpassword = hash('sha512', $_POST['password']);
$eusername = mysqli_real_escape_string($con, $_POST['username']);
$fusername = str_replace(' ', '', $eusername);
$email = mysqli_real_escape_string($con, $_POST['email']);

// Check if username exists
$sql_check = "SELECT id FROM users WHERE username = '$fusername'";
$result_check = mysqli_query($con, $sql_check);

if (mysqli_num_rows($result_check) > 0) {
    // Username is taken
    die("Error: The username '{$fusername}' is already registered.");
}

// Proceed with insertion only if the check passed
$sql = "INSERT INTO users (username, password, email) VALUES ('$fusername', '$hpassword', '$email')";

if (!mysqli_query($con, $sql)) {
    die('Error: ' . mysqli_error($con));
}

mysqli_close($con);

This approach ensures that the database state remains consistent and provides immediate feedback to the user. However, this method requires two separate round-trips to the database server (one for checking, one for inserting). While acceptable for low-traffic applications, it introduces unnecessary latency under high load.

The INSERT ... ON DUPLICATE KEY UPDATE Pattern

A more efficient solution, often preferred in performance-critical applications, is to use the INSERT ... ON DUPLICATE KEY UPDATE syntax. This allows you to attempt the insertion in a single query. If the username already exists (violating a UNIQUE constraint), MySQL triggers the update logic instead of failing hard.

To make this work, your users table must have a unique index on the username column. You can create or verify this using:

ALTER TABLE users ADD UNIQUE KEY unique_username (username);

With that constraint in place, your PHP code simplifies significantly because you no longer need a separate SELECT query:

$sql = "INSERT INTO users (username, password, email) 
        VALUES ('$fusername', '$hpassword', '$email')
        ON DUPLICATE KEY UPDATE username=username";

In this scenario, the ON DUPLICATE KEY UPDATE clause does not necessarily mean you want to update the email or password. The placeholder username=username is a common idiom to effectively "do nothing" while satisfying the SQL requirement for an update statement when a conflict occurs. You can then check $result from mysqli_query() to see if it was an insertion or a duplicate key conflict, though simply returning a generic error message is often sufficient for user-facing applications.

Security and Modern Best Practices

While your current code uses mysqli_real_escape_string, which prevents SQL injection, the modern PHP standard strongly recommends using prepared statements. Prepared statements handle escaping internally and are generally considered more robust against edge cases in special characters.

Furthermore, relying on SHA512 with a plain hash is weak by today's standards. It lacks salting, making the database vulnerable to rainbow table attacks if breached. You should switch to password_hash() for creating new passwords and password_verify() for login attempts. This function automatically handles salting and uses algorithms like Argon2id or bcrypt that are resistant to GPU-based cracking.

If you are looking to streamline this entire authentication flow, including registration, login, and password management, exploring a mature framework can save significant development time. Laravel, available at https://laravelcompany.com, provides built-in Eloquent ORM models and validation rules that handle these checks elegantly without writing raw SQL queries manually.

Our network