Problem Checking a Unique Indentity within MySQL Database

Question

I am trying to verify that a string is unique within a MySQL column. Here is the code sample:

<?php
$con = mysql_connect("hostname", "username", "password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("table_name", $con);


if (!mysql_num_rows(mysql_query("SELECT * FROM table_name WHERE unique_string = '123456' 
LIMIT 1"))) {

die('This string is not unique.');
}
mysql_close($con);
?>

In this code sample I have input the value 123456 as an example. This example assumes that the string 123456 already exists in the column unique_string. Therefore, it should die and return the statement "This string is not unique." Sadly, it does not. Do you know why? Is there a better way to get this check done?

Answer 1

It's generally unwise to chain your function calls as you have. Particularly with database calls. The mysql_*() functions expect certain things, like statement handles, to be passed in. But they can return a boolean FALSE instead of a handle if there's a problem. This FALSE gets passed in instead of a handle and then everything's broken.

A general rule is to always assume your query will fail somehow, and program defensively:

$sql = "SELECT ....";
$result = mysql_query($sql) or die(mysql_error());
$rows = mysql_num_rows($result);
if ($row !== 0) then
    die("Not unique");
}

Beyond that, your query statement does look to be syntactically correct. Why not see what it's returning?

$row = mysql_fetch_assoc($result);
var_dump($row);

and see exactly what's being matched. Maybe you DON'T have that value in the table and are mistaken in thinking that it's there.