How to Check if a Record Exists in a MySQL Database

I usually use a database abstraction layer with PDO to access MySQL Databases, but yesterday, I needed to use the php mysql functions the ‘old-fashioned’ way. I didn’t have access to my usual classes and libraries. It’s been a while since I used mysql this way, so I had a little ‘relearning’ to do.

I spent way too much time on this problem and had trouble finding the answer. Mostly because I was in a rush and didn’t want to take the time to read things as thoroughly as I needed to read them, but I thought it would make a good topic for today’s web tip.

Somehow I had it in my head that I should try something like this to find out if a record already exists in my database:

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

I didn’t have any products in my database table yet and still I was getting a lot of ‘found’ messages.

What I had forgotten is that the mysql_select function returns a resource unless the query has an error. An empty result set is not an error. It’s perfectly ok to have an empty set. The query is still a valid query and just because it didn’t find any matches does not mean there was an error with my query.

I had to change my code to something like this to test whether a product existed in my database already:

$query = "SELECT * FROM products WHERE code = '$code'";
$result = mysql_query($query);
if ($result) {
  if (mysql_num_rows($result) > 0) {
    echo 'found!';
  } else {
    echo 'not found';
  }
} else {
  echo 'Error: '.mysql_error();
}

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top