[SOLVED] double unique

[rarebit]

Hey,

 

Let's say I have a table (not that it's like this at all, just so you get the id):

$s = "CREATE TABLE test_unique (user varchar(32) unique, buddy varchar(32) unique )";
$ret = mysql_query($s, $conn);

$s = "INSERT INTO test_unique VALUES ('me', 'you'), ('me', 'them'), ('you', 'me'), ('me', 'them') ";
$res = mysql_query($s, $conn) or die(mysql_error());

(hmm, even the above won't insert all of 'em!)

 

What I wanna do is insert them and these:

$s = "INSERT INTO test_unique VALUES ('them', 'you'), ('them', 'me') ";

 

Do you see what I mean?

 

I want to block duplicates in a single mysql call, based on each user...

 

 

[metrostars]

You can only have 1 row with them as the 'user' as it is 'unique'. I might not be following exactly what you want.

 

If you want to have multiple rows with them in it, you will have to make it not unique.

[rarebit]

mmm, reading it doesn't really make much sense...

 

As it currently stands there are no unique fields in my table. I have to search for each name/buddy pair and if not there, insert it. However I want to do this in a single call and was trying to figure it using unique, but really it won't do it...

 

???

[Barand]

mysql> CREATE TABLE `test_unique` (
    ->   `user` varchar(32) default NULL,
    ->   `buddy` varchar(32) default NULL,
    ->   UNIQUE KEY `user` (`user`,`buddy`)
    -> );
Query OK, 0 rows affected (0.17 sec)

mysql> INSERT INTO test_unique VALUES
    -> ('me', 'you'),
    -> ('me', 'them'),
    -> ('you', 'me'),
    -> ('them', 'you'),
    -> ('them', 'me');
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test_unique;
+------+-------+
| user | buddy |
+------+-------+
| me   | them  |
| me   | you   |
| them | me    |
| them | you   |
| you  | me    |
+------+-------+
5 rows in set (0.00 sec)

 

This fails as the combination already exists

 

INSERT INTO test_unique VALUES ('me', 'them')

[rarebit]

Thought it should have something to do with unique. Thanks very much!