Coding to check if similar data is already inserted into MySQL

Hey everyone,

We have 'groups' (Column A in a table) and 'members' (Column B in a table). I don't want to insert members into groups more than once. Anyone have any coding ideas via PHP? Thanks in advance,

Kevin M.

 

Maybe this can be solved with an index in mysql? Like if this was your table:

mysql> describe test_table;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| groupName  | varchar(25) |      |     |         |       |
| memberName | varchar(25) |      |     |         |       |
+------------+-------------+------+-----+---------+-------+

Code (markup):

You could create an index like this:

CREATE UNIQUE INDEX unique_pair ON test_table (groupName,memberName);

Code (markup):

Then replace your INSERTs in PHP with REPLACE. So, for this example, we could do the following and be assured that no group will have the same member twice, even though PHP may have tried to insert it twice:

REPLACE INTO test_table (groupName,memberName) VALUES('Group 1','First User');
REPLACE INTO test_table (groupName,memberName) VALUES('Group 2','First User');
REPLACE INTO test_table (groupName,memberName) VALUES('Group 1','First User');

Code (markup):

Even though we did a REPLACE INTO for "First User" twice to "Group 1" the table will only have the most recent:

mysql> SELECT * FROM test_table;
+-----------+------------+
| groupName | memberName |
+-----------+------------+
| Group 1   | First User |
| Group 2   | First User |
+-----------+------------+

Code (markup):

This may, or may not be what you are looking for though.. simply replacing the INSERT query in PHP to REPLACE won't give your user any feedback like "A user by that name already exists in the selected group"... then again, depending on your application you may not care.

I hope this helps!