favourite function on php dating site

S3NTIN3L

At the moment I have a link where u can favourite a member of my site.

It will add the id of the user to the database with the person favourited (1 row).

If another user is added as a favourite then another row with that id and the username of the favourite will be added to (2 rows).

This is making duplicated data - does anyone recommend have comma separted values in one row instead. The problem with this is if a favourite is added already there has to be a check for a double add (if you catch my drift), the code might be difficult for this?

Please recommend a good approach to this.
Cheers anyone

dagon

you should have another table called favourite with 2 cols, person1 and person2

S3NTIN3L

probably my bad explanation but i want to add as many favourites as are on the site - more than two.

Or does your idea still allow that or does your idea mean - use sql to add a column each favourite added.
cheers

dagon

you get one row for each favourite pair, so you can have an infinite number

S3NTIN3L

ALTER TABLE favourites ADD favourite VARCHAR(60);

This kind of thing?

S3NTIN3L

Im not sure about this.

Are you saying add columns each favourite added?

bradgrafelman

S3NTIN3L;10975042 wrote:
does anyone recommend have comma separted values in one row instead.

Definitely not - this violates normalization techniques and simply generates more work for you and/or the server.

S3NTIN3L;10975058 wrote:
Are you saying add columns each favourite added?

Definitely not. What dagon is suggesting is a simple two-column table structure, e.g. (user1, user2). Both columns would be foreign keys back to your users table to show the relationship "user1 ---> [added favorite] ---> user2".

Thus, if you wanted to find the names of all users that I have added as a favorite, you'd do something like:

SELECT u.name
FROM favorites f
  INNER JOIN users u ON u.user_id = f.user2
WHERE f.user1 = 123

(where my user_id value is 123).

dagon

not column, row.

S3NTIN3L

if im wrong say but wont this make duplicates i.e. user1.
e.g. user1 has favourite x
user2 has favourite y

or am i completely up s##t creek with this?

bradgrafelman

No, those will be two unique tuples. "user1" and "user2" both refer to the same foreign key - user_id (in my example above). The "favorites" table is a many-to-many cross-reference table for a directed relationship. In other words, you can't switch the columns. "user1 [favorited] user2" doesn't mean "user2 [favorited] user1".

In your example above, give the entities actual values...

user1 -> user_id=1
user2 -> user_id=2
x -> user_id=3
y -> user_id=4

Given your description, you'd see two tuples in the 'favorites' table I've described above: (1,3) and (2,4).

S3NTIN3L

so if I was using usernames instead which I probably will do it would look like this:

john64 -> alex32
john64 -> tim37
tim32 -> alex34

Feel free to give up on me.
etc

bradgrafelman

That's the gist of it, yeah.

dagon

user ids are better than user-names, smaller,faster, index better, allow user to change name with out breaking everything.

S3NTIN3L

ok great.
now if i wanted to check that a favourite had not been added before then what kind of select statement would i use? i suppose i would count same favourites for that username and if it was over 1 i would not allow an add.

could u help me with this select statement

S3NTIN3L

and yes id's it is then

dagon

add a unique key for the two cols then you wont ever get duplicates. you may still want to check so you can give the user feedback.

bradgrafelman

Why would you need to do that at all?

One way to do it (and the way I'd probably do it myself) is to make (user1, user2) the PRIMARY KEY for the 'favorites' table.

That way, you'd simply perform the INSERT and then check if it failed due to error # 1062 - duplicate data for key.

S3NTIN3L

okay ive added a unique key.
ill have to update my insert statement:

"INSERT INTO favourites(unique_key, id, favourite_id) VALUES(0, '$user_id','$favourite')"

it does add anything now - is my syntax right?

cheers for help sofar

bradgrafelman

What is this unique_key column supposed to be? There should only be two columns - id, favorite_id.

S3NTIN3L

bradgrafelman its not possible to mkae them both primary keys is it?