How to save the items that a user bought in the database and prevent him to buy it again

Question

I need some help storing the files a user bought and downloaded. I have a table with files and their ID. When users buy and file it's just redirecting him to the download link. But I want to make PHP script insert into the database the ID of the files that user bought. But how to do it with multiple files and store their ID in 1 field for each user. For example I have 10 files and the user bought files 1,2,3,8,9. I want to store in the database these numbers and do a while to show the link that corresponds to these ID's.

Answer 1

You need to create a table user_files that has two columns, user_id and file_id forming a composite primary key:

CREATE TABLE user_files (
  user_id         INT NOT NULL,
  file_id         INT NOT NULL,
  PRIMARY KEY (user_id, file_id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (file_id) REFERENCES files(id),
);

If user 1 buys files 1,2,3,8,9, you would then do

INSERT INTO user_files (user_id, file_id) VALUES (1,1), (1,2), (1,3), (1,8), (1,9)

and then, when you need to check if a user has bought you just check if that user_id / file_id combination exists in the table.