Database check for uniqueness of two columns (and output of error message in PHP if it doesn't satisfy)

Question

I'm trying to make a database check in my webpage that will check to make sure two columns (first_name and last_name) are unique. In MySql I already have those two columns as UNIQUE.

I want to have this check performed on the webpage, and return an error message if a first_name and last_name are already listed in the table. ex. If Tom Jones is in the table already, an error will show up if Tom Jones is input into the form. My statement below checks for this uniqueness, but it checks across all rows in the table. I need it to check row by row. member_no is the primary key, and I thought about searching with this, however, I want to check each row for uniqueness and I don't have a specific number to search.

This is just on the website/form side, since the UNIQUE statement for first_name and last_name is already active on MySql.

Or is there a more direct way to check the uniqueness in the table?

This is my statement:

$command = "SELECT * FROM member_info";
$member_result = mysql_query($command);
$mdata = mysql_fetch_object($member_result);
if ($first_name == $mdata->first_name && $last_name == $mdata->last_name) {
$error_message = "The first and last names must be unique. ";
}

This will check the table but will not discriminate between the rows. ex. if row 1 is Tom Jones, and row 2 is Bob Smith, and if I put into the form 'Tom Smith' the error will come back as not unique.

I guess a simpler way to put it is this: first_name and last_name are flagged as UNIQUE in my table. If I'm in MySql and I put in a name that is already used, I will get an error message. What I want to do is take that error message and output it to my webpage form. — Scott_Lew
– Scott_Lew, Commented May 16, 2013 at 23:00

Sverri M. Olsen · Accepted Answer · 2013-05-16 22:04:46Z

1

If first_name and last_name are in a multiple-column UNIQUE index then you can just go ahead and insert values. If the INSERTed values are already in the database then you will get an error saying so, and then you can take it from there.

To create a multiple-column UNIQUE index you can ALTER the table like so:

ALTER TABLE `member_info` ADD UNIQUE (`first_name`, `last_name`);

And then INSERT stuff like this:

$sql = "INSERT INTO `member_info` (`first_name`, `last_name`) VALUES ('{$first_name}', '{$last_name}')";

You should first make sure $first_name and $last_name are safe, though. If they are not then you will have security holes.

answered May 16, 2013 at 22:04

Sverri M. Olsen

13.2k3 gold badges38 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

flaschenpost · Accepted Answer · 2013-05-16 21:58:04Z

0

You can use a where-clause:

select * from member_info where first_name = {$first_name} and last_name = {$last_name}
You can optimistically try to insert and then react reasonably to the duplicate-key-message.

answered May 16, 2013 at 21:58

flaschenpost

2,2352 gold badges15 silver badges30 bronze badges

4 Comments

Arjan Over a year ago

Even if there is no duplicate record (based on the first option), the insert could still fail. Another process could have inserted a record in the time you were checking whether it is present.

flaschenpost Over a year ago

Fortunately the query does not only return "Ummm tha is wrong", but errornumbers, so it is easy to distinguish duplicate key from syntax error or server gone away. I think a two-liner test script shows the differences.

Arjan Over a year ago

That's true. I was merely trying to point out that it's not very useful to check if the record is already present.

flaschenpost Over a year ago

@Arjan I don't understand what you mean. If you insert optimistically and get duplicate key error, some other Tom Smith was faster, if not, you have inserted the first one. But you are right, the solution number 1 has that problem, as long as you don't lock or transact the area between select and insert.

km6zla · Accepted Answer · 2013-05-16 22:11:21Z

0

First mysql_ function are dprecated. Use mysqli.

First you want to clean the users input. I'll assume from this point that $first_name and $last_name are cleaned variables ready for use in a MySQl statement.

$query = "SELECT COUNT(*) FROM `member_info` WHERE `first_name`='$first_name' AND last_name='$last_name'";
$member_result = mysqli_query($command);
$mdata = mysqli_fetch_assoc($member_result);

if($mdata['COUNT(*)'] > 0) {
    //user with that first and last name already exists
}

answered May 16, 2013 at 22:11

km6zla

4,9272 gold badges32 silver badges53 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:05:22Z

0

For keeping track of unique string values, I believe the most generic solution (but may be overkill for some cases) is to maintain another column with the hash of the string value and adding a UNIQUE constraint to that column. The overhead comes when modifying record data; you should also update the hash value (could be accomplished automatically with the use of triggers).

Bear in mind that indexes on character columns have a byte limit (problem & solution also referred in this question ). For short strings though, you could use the composite index solution as proposed by @sverri .

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered May 17, 2013 at 17:55

georgepsarakis

1,9574 gold badges21 silver badges24 bronze badges

Collectives™ on Stack Overflow

Database check for uniqueness of two columns (and output of error message in PHP if it doesn't satisfy)

4 Answers 4

Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related