CODEWITHSUNDEEP
phpmysqlsqlunique
John
John

Reputation: 147

Insert names into table if not already existing

I am trying to build an application that allows users to submit names to be added to a db table (runningList). Before the name is added tho, I would like to check firstname and lastname against another table (controlList). It must exist in (controlList) or reply name not valid. If the name is valid I would like to then check firstname and lastname against (runningList) it make sure it isnt already there. If it isn't there, then insert firstname & lastname. If it is there, reply name already used.

Below is code that worked before I tried to add the test against the controlList, I somehow broke it altogether while trying to add the extra step.

if (mysql_num_rows(mysql_query('SELECT * FROM runninglist WHERE firstname=\'' .$firstname . '\' AND lastname=\'' . $lastname . '\' LIMIT 1')) == 0)
{
 $sql="INSERT INTO runninglist (firstname, lastname)
 VALUES ('$_POST[firstname]','$_POST[lastname]')";
}
else
{
echo "This name has been used.";
 }

Any suggestion?

Upvotes: 4

Views: 254

Answers (2)

John Woo
John Woo

Reputation: 263743

The best way is to create an index on both columns: firstname and lastname

ALTER TABLE youtableName ADD UNIQUE uniqueName (firstname,lastname)

In this way, an error will be generated when you try to insert which name already exist.

Upvotes: 3

Robbie
Robbie

Reputation: 17720

Create a "unique" index on (firstname, lastname).

Then, when the insert runs it will fail. Just ignore the error (or take other action)

Edit: quote from the manual:

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row.

Upvotes: 3

Related Questions