System for keeping track of user favorites

Question

On my website, I have a table movies and a table users

I'm trying to have an "Add to favs" button that a user can click, which will add that movie to his favorites (ajax / javascript not necessary at the moment, just php).

So what's the simplest way I could do something like that? I've thought about this but I can't seem to find a solution (all I think of is way too complicated, and in my opinion not possible).

What's your thoughts?

I don't need a ready-made script, just an idea that could get me working (although if you have an example of such script, I'd be happy to look at it).

Thanks!

Answer 1

You could create a table favourites with three columns, id, mid and uid. To add a favourite:

INSERT INTO favourites (mid, uid) VALUES (3, 5)

To search for favourites of one user:

SELECT * FROM favourites WHERE uid = 7

To search for people who favourited one movie:

SELECT * FROM favourites WHERE mid = 9

Answer 2

You will need to create a new table:

user_favorite_movies
--------------------
ID (primary key)
userID (foreign key)
movieID (foreign key)
date

Then when the user clicks the 'Add Favorite' button, you just insert a new row into user_favorite_movies with the users ID from the user table, the movie id from the movie table, and the date it was added (good for sorting later).

Hope this helps!

Best,

-Eric

Answer 3

This is a many-to-many relationship. A user can favorite many movies, and a movie can be favored by many users. In an RDBMS, you represent a many-to-many relationship with a third table. I call this an intersection table but it goes by other names too.

Create a table with two columns. The columns are both foreign keys, referencing movies and users, respectively.

CREATE TABLE Favorites (
  user_id INT NOT NULL,
  movie_id INT NOT NULL,
  PRIMARY KEY (user_id, movie_id),
  FOREIGN KEY (user_id) REFERENCES Users(user_id),
  FOREIGN KEY (movie_id) REFERENCES Movies(movie_id)
); 

When a user chooses to favorite a movie:

INSERT INTO Favorites (user_id, movie_id) VALUES (?, ?)

When a user decides they don't like a movie any longer, delete the corresponding row:

DELETE FROM Favorites WHERE (user_id, movie_id) = (?, ?)

To get the set of movies favored by a given user:

SELECT movie_id FROM Favorites WHERE user_id = ?

To get the set of users who favor a given movie:

SELECT user_id FROM Favorites WHERE movie_id = ?

---

Regarding one of your comments:

You shouldn't make the "Add to favorite" a link. Indexers like Google will follow links, and then before you know it, every user has favorited every movie.

The general best practice is that read-only operations can be GET requests, while operations that write to the database can be POST requests. This means that you need to use a <form> element to submit POST requests, not an <a href="..."> element.

Answer 4

So far as I can see, you'll still need to use JavaScript or Ajax to do the post, unless you want to refresh the page every time thet mark/unmark a favorite, and also to add/remove the new favorite indicator in place at the same time.

Or am I missing something?

Answer 5

Add a third table:

CREATE TABLE user_favorites (
  user_id INT NOT NULL,
  movie_id INT NOT NULL,
  PRIMARY KEY (user_id, movie_id),
  FOREIGN KEY user_id REFERENCES users (user_id),
  FOREIGN KEY movie_id REFERENCES movies (movie_id)
)

This is called an intersection table or join table, as it joins rows in the users table to rows in the movies table (as you see, each column is a foreign key). It is also defines a many-to-many relationship, because one user can like many movies and one movie can be liked by many users.

When you go to add a favorite movie for a user, all you have to do is insert a row in this table with the ID of the user and the ID of the movie:

INSERT INTO user_favorites(user_id, movie_id) VALUES([user ID], [movie ID])

To see what movies a user has favorited:

SELECT movie_id FROM user_favorites WHERE user_id = [user ID]