How do I make sure a table entry is unique?

[Robo]

Using PHP/MySQL, got a form that submits things to MySQL, how can I check to make sure that every entry is unique in that column? like, give an error when the user submits something that's already there in the MySQl db.

[vinasite]

You can always make a field unique when you first create the table.

[Robo]

I went to PHPMyAdmin, and clicked on Unique on that field, and got this error:

Duplicate entry '' for key 2

Does it have anything to got to do with that I have a Primary Key already?

[atomicmunky]

how's about this. i use somthing similar to the following to start a user reg script where it asks for a username,password,pass confirmation, and email address.

let me know if it helps

::::::::::::::::::::::::::::::::::::::::
<?php

$DBhost = "localhost";
$DBuser = "YOUR-USERNAME";
$DBpass = "YOUR-PASS";
$DBName = "YOUR-DB-NAME";
$table = "YOUR-DB-TABLENAME";

$db = mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");

//select the DB

@mysql_select_db("$DBName") or die("Unable to select database $DBName");

//check to see if the 2 entered passwords match

if ($upass == $upass2) {
$sqlquery = mysql_query("SELECT * FROM $table WHERE (uname='$uname')");

if (mysql_num_rows($sqlquery) > 0) {

printf("We are sorry to inform you that the User Name <B><?php echo $Name ?></B> Is already Taken.");

}

else {

//insert the values into the Db

$sqlquery = mysql_query("INSERT INTO $table VALUES('$uname','$upass','$Email')");

$sqlquery = mysql_query("SELECT * FROM $table WHERE (uname='$uname')");


printf("You have registered.<BR>

User Name: $uname

<BR>

Password: $upass");
}
}
else {
printf("Your Two Passwords Did Not Match");
}
?>

[Son Nguyen]

Originally posted by Robo
I went to PHPMyAdmin, and clicked on Unique on that field, and got this error:

Duplicate entry '' for key 2

Does it have anything to got to do with that I have a Primary Key already?

I'm not so sure, but tried another field (that could make unique!) or remove the primary key (normally for auto_increment field) and put unique key for that field.

[Robo]

Thanks atomicmunky, that script should work for me.

[freddydoesphp]

A couple of serious concerns with atomicmunky's code
1) printf() being used incorrectly
printf("We are sorry to inform you that the User Name <B><?php echo $Name ?></B> Is already Taken.");

This should never be done you can't embed <? ?> tags inside your print statement and it will surely cause a parse error furthermore printf() takes an argument and this should have been written like
printf("We are sorry to inform you that the User Name <B>%s</B> Is already Taken.", $Name);

and again this would be the proper syntax

printf("You have registered.<BR> User Name: %s<BR> Password: %s", $uname, $upass);

2) in the else statement he inserts the data and then runs a query to get it out again what a waste of resources since if he inserted the data then the data is already available to the script and doesn't need to be retireved again.


3) the last printf() statement should be a regular print "" because no vars are being used, this can really cut down on processing time.

[Robo]

I only needed the bit about if (mysql_num_rows($sqlquery) > 0) anyway...but yeah, I could see how those things could cause problems.

What is with those print, printf, and echos anyway? I normally uses echos, and seems easy enough to use as well, somehow it's easier to type the word echo, then print... for the above, I would have gone:


echo "You have registered.<BR />User Name: " . $uname . "<BR />Password: " . $upass;


Would that be acceptable?

[freddydoesphp]

You are correct there isn't much difference the only thing I like printf() for is you can print a whole string with html and not worry about escaping double quotes or concatenating you vars into the statement take this for example

echo "<tr><td width=\"100\">". $somevar ."</td></tr>";

could be written

printf('<tr><td width="100">%s</td></tr>', $somevar);

The key is the %s that means substitue the arg given at the end with the %s in this case $somevar some other useful reasons to use it is to format numbers let's say you have a number like 34 and you want to display it as 000034 you could use printf('%06d', $num);

<Edited by freddydoesphp on 12-20-2000 at 11:09 PM>

[atomicmunky]

ok you turkeys, i never claimed to be a php expert.
i'm over here trying to help and for some reason you can't help but practically tell me that my code is evil and should be exorsized.
i appreciate the creative critisism though. i guess it gives me a higher "standard" to live up to

i'll give your "suggestion" a try freddy, but if it craps out on me, i'll know who to blame

[Robo]

Alright, sorry, I shouldn't have been such a jerk, lets ki...I mean shake hands and make up.

So will you forgive me now?

I'll take that as a yes.

OK, now help me with my script! :P

Here's the script:

// check to see if Anchor Name is unique
$sql = mysql_query("SELECT * FROM items WHERE anchor_name='$anchor_name'"); 
if (mysql_num_rows($sql) > 0)
{
echo "<p>Hey, <b>" . $anchor_name . "</b>had already been use, be more creative</p>";
}

// more error checkings
if ()
{
blah;
}

// even more error checkings
if ()
{
blah;
}

else
{
//process form
$sql = "INSERT INTO table....";
// run SQL against the DB to confirm
$result = mysql_query($sql);
echo "<p>Record updated/edited!</p>";

Here's the problem:
When I submit an anchor name there's already in the db, it'll echo the msg about being more creative, but also the one about being Record updated/edited! And a new record is added, even though it should stop.

And I get a phrase error if I take the single quotes off the $anchor_name at the SELECT line, but all my other scripts never have quotes like that.
<Edited by Robo on 12-21-2000 at 01:41 AM>

[Robo]

Think I figued out part of the problem, is it because I have to use if, elseif, elseif, else, not if, if, if, else?

But I still dunno why I need the single quotes around $anchor_name.

[atomicmunky]

i was just going to suggest the elseif.
try that, and also try putting parenthesis around :: anchor_name='$anchor_name' :: and see if that works like so.

::::::::::::::::::::::
$sql = mysql_query("SELECT * FROM items WHERE (anchor_name='$anchor_name')");
::::::::::::::::::::::

....another question is : what are your other 2 if statments checking. maybe those are the culprits??

i'll keep thinking.

ps-i forgive you

[Robo]

The code works fine with just


$sql = mysql_query("SELECT * FROM items WHERE anchor_name='$anchor_name'")


But I normally go:


$sql = mysql_query("SELECT * FROM items WHERE anchor_name=$anchor_name")


Dunno why it needs the quotes this time...

[Kevin Yank]

The original error message that you obtained when setting the column unique was, I suspect, due to the fact that you already have multiple rows with an empty string value ("") in that column. As a result, MySQL complained because you were trying to set a column unique that was not, in fact unique to begin with.

[Kevin Yank]

Robo,

You should always put quotes around any string value. In your example, if the string contained a space, a comma, or any number of special characters, your SQL statement would become invalid. The quotes ensure that MySQL can tell where the string begins and ends.