MySQL Preventing duplicate entries (newbie)

PHP
1

Having been through the excellent PHP / MySQL tutorial at webmasterbase.com, I’m now dangerous.

Have a table “Categories” in my MySQL database, which has two columns; ID (INT NOT NULL AUTO_INCREMENT PRIMARY KEY), Category (TEXT).

From a PHP form, want to prevent duplicate entries in the Category column (i.e. the same text string) being inserted into the table.

From the PHP form, the MySQL command I’m using is;

“INSERT INTO Categories SET Category=$cat”

where $cat is a PHP variable.

Is there a way to enhance this command to get it to ignore the INSERT if there is already an entry with the same string as $cat?

Have tried;

“INSERT IGNORE INTO Categories SET Category=$cat”

but this doesnt work (my guess because it checks for a duplicate ID value as well ? ).

Many thanks.

2

you’ll probably need to run two queries the first being:
$sql = “SELECT * from Categories WHERE Category = ‘$cat’”;
$result = mysql_query($sql);
if(mysql_num_rows($result) > 0) {
print “Sorry that category already exists”;
}
else {
$sql2 = “INSERT INTO Categories SET Category=‘$cat’”;
$result2 = mysql_query($sql2);
}

3

Just make sure the Category field is marked as unique, you can determine if it is or not by looking at the mysql error message.

To make the key unique: ALTER TABLE Categories ADD UNIQUE(Category), then do a query like this:

$res = @mysql_query( “INSERT INTO Categories SET Category=‘$cat’”);

if( $res ){

} else {
// An error occured

if( mysql_errno() == ‘code used for not unique’ )

echo “please enter a unique value”;

}

}

I’m not sure off the top of my head what the error number is for a duplicate entry but you can easily find out by trying it and echoing the result of the mysql_errno()

4

Freddy, Karl,

Many many thanks - gobsmacked by how fast you’ve answered. Have used Karls suggestion and has worked. Like Freddies thinking - giving me ideas to become truly dangerous.

Thanks.