Check Whether a Record Exists in MySQL

When you only need to know whether a matching row exists, do not fetch an entire record set. In SQL, EXISTS expresses this directly; in application code, a SELECT 1 ... LIMIT 1 query is also efficient and simple.

SQL EXISTS

SELECT EXISTS(
    SELECT 1
    FROM plus_signup
    WHERE userid = 'alex'
) AS user_exists;

user_exists
1

PHP PDO prepared statement

This pattern is suitable for a signup check because the visitor-supplied user ID is bound as a parameter.

<?php
require 'config.php';

$userid = 'alex';
$query = 'SELECT EXISTS(SELECT 1 FROM plus_signup WHERE userid = :userid)';
$stmt = $dbo->prepare($query);
$stmt->execute([':userid' => $userid]);

$exists = (bool) $stmt->fetchColumn();
if ($exists) {
    echo 'User name already exists.';
}
?>

Count matching rows when you need the count

SELECT COUNT(*) AS matches
FROM plus_signup
WHERE userid = 'alex';

Use COUNT() when the actual number of matching rows matters. For a yes/no check, EXISTS avoids doing unnecessary work.

MySQLi prepared statement

<?php
$query = 'SELECT 1 FROM plus_signup WHERE userid = ? LIMIT 1';
$stmt = $connection->prepare($query);
$stmt->bind_param('s', $userid);
$stmt->execute();
$stmt->store_result();

if ($stmt->num_rows > 0) {
    echo 'User name already exists.';
}
?>

Related PHP topics: signup form, PDO, MySQLi, and if/else.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: