System for tracking selected users

Question

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

I am trying to create an “Add to favs” button that a user can click to add this movie to their favorites (ajax / javascript is not needed at the moment, just php).

So what is the easiest way I could do something like this? I thought about it, but I can’t find a solution (everything that I think is too complicated and, in my opinion, impossible).

What do you think?

I do not need a ready-made script, just an idea that can make me work (although if you had an example of such a script, I would be glad to look at it).

Thanks!

Answer 1

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 the intersection table or join table because it joins the rows in the users table with the rows in the movies table (as you can see, each column is a foreign key). It also defines many-to-many relationships because one user may like many films, and one film may appeal to many users.

When you add your favorite movie to the user, all you have to do is insert a row in this table with the user id and movie id:

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

To find out which films the user liked:

 SELECT movie_id FROM user_favorites WHERE user_id = [user ID] 

Answer 2

This is a many-to-many relationship. A user may like a lot of films, and many users may like a movie. In a DBMS, you represent many-to-many relationships with a third table. I call this the intersection table, but it is also called by other names.

Create a table with two columns. Columns are both foreign keys and links to movies and users.

 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 selects a favorite movie:

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

When the user decides that the movie no longer likes, delete the corresponding line:

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

To get a set of movies approved by this user:

 SELECT movie_id FROM Favorites WHERE user_id = ? 

To get a set of users who prefer this movie:

 SELECT user_id FROM Favorites WHERE movie_id = ? 

---

Regarding one of your comments:

You must not include the "Add to Favorites" link. Indexes like Google will follow the links, and then, before you know it, every user prefers every movie.

It is common practice that read-only operations can be GET requests, and operations that are written to the database can be POST requests. This means that you need to use the <form> element to send POST requests, not the <a href="..."> element.

Answer 3

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 simply insert a new line into user_favorite_movies with the user ID from the user table, the movie ID from the movie table and the date it was added (useful for sorting later).

Hope this helps!

Best

Eric

Answer 4

You can create a table of favourites with three columns, id , mid and uid . To add favorites:

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

To search for favorites of one user:

 SELECT * FROM favourites WHERE uid = 7 

Search for people who used one movie:

 SELECT * FROM favourites WHERE mid = 9 

Answer 5

As far as I can tell, you still have to use JavaScript or Ajax to publish, if you do not want to refresh the page every time you mark / deselect a favorite, and also add / remove a new favorite indicator at the same time.

Or am I missing something?