How to Check if a Record Exists in a MySQL Database

Developers often need to check whether a record exists in a MySQL database.

Below is the recommended approach, followed by a common mistake beginners often make.

The Correct Command

If you want to check whether a record already exists in your database, use the following code:

Using MySQLi

Example of checking whether a record already exists in your database in PHP

<?php

// connect to database
$mysqli = new mysqli("localhost","db_user","db_password","db_name");

$code = 'EXAMPLE_CODE'; // Define the value to search for

// Use prepared statements to prevent SQL injection
$stmt = $mysqli->prepare("SELECT * FROM products WHERE code = ?");
$stmt->bind_param("s", $code);
$stmt->execute();
$result = $stmt->get_result();

// Note: For simple existence checks, using LIMIT 1 or fetching a single row is often more efficient than counting all rows.
if ($result) {
    if ($result->num_rows > 0) {
        echo 'found!';
    } else {
        echo 'not found';
    }
} else {
    echo 'Error: ' . $mysqli->error;
}

$stmt->close();
$mysqli->close();

?>

Try it Yourself »

Using mysql (Deprecated & Removed)

<?php

// WARNING: The mysql_* extension was removed in PHP 7.0. Do not use this in modern applications.
// mysql_connect() is required to establish a connection.
mysql_connect("localhost", "db_user", "db_password");

$code = 'EXAMPLE_CODE'; // Define the value to search for

// Use mysql_real_escape_string to prevent SQL injection
$safe_code = mysql_real_escape_string($code);
$query = "SELECT * FROM products WHERE code = '$safe_code'";
$result = mysql_query($query);
if ($result) {
  if (mysql_num_rows($result) > 0) {
    echo 'found!';
  } else {
    echo 'not found';
  }
} else {
  echo 'Error: ' . mysql_error();
}

mysql_close();

?>

A Common Mistake

Here is a common mistake to avoid:

<?php

// Assumes connection is established
$code = 'EXAMPLE_CODE'; // Define the value to search for

$query = "SELECT * FROM products WHERE code = '$code'";
$result = mysql_query($query);
if ($result) {
  echo 'found';
} else {
  echo 'not found';
}

?>

Why this fails: mysql_query() returns a result resource on success, even when zero rows match the query. Checking if ($result) will always evaluate to true for valid queries. You must use mysql_num_rows($result) > 0 to verify that data was actually returned.

What is MySQL

MySQL is considered an open-source relational database management system. It is aimed at organizing data into one or more data tables.

Several popular applications use MySQL for data storage purposes. It was initially made for limited usage. Yet, now it is compatible with many essential computing platforms such as macOS, Linux Ubuntu, and Microsoft Windows.