Anonymous Anonymous - 2 years ago 222
PHP Question

What's the best practice of getting a user's list of article likes from a database?

I'm sort of working on a CMS type structure. I've got to the point where mostly everything is in place except for small things that I keep pondering on like this one.

So let us suppose there is a user table and an articles table. Now, ofcourse if someone 'likes' an article, the user's ID will be stored in a column in the article's table. Something like this

. (these are random user IDs)

Now lets say that there's a user's profile page as well and I want to iterate through the user's liked posts and display it there.

Now, I've thought up of 2 ways of doing this.

First method being that the article IDs that the user has liked are stored in the user's table in his row and we get it from that but this seems sort of inefficient. Because if the user has liked a lot of articles, then I could run out of space in the column of the database?

Second method would be to go through every article and see if the user's ID exists in the list of likers saved of the article. However this method would be really slow and a really bad practice, IMO.

So what's the best practice for this?

Answer Source

Create a table that has two columns: user_id and article_id. When a user “likes” an article, insert a row into this table. Then when you want to see which articles user #123 has liked, you can just issue a query like this:

SELECT `article_id` FROM `article_user` WHERE `user_id` = '123';

If you need the article data, it’s easy enough to just join on the article_id column:

SELECT `article`.*
FROM `article`
LEFT JOIN `article_user` ON `article`.`id` = `article_user`.`article_id`
WHERE `article_user`.`user_id` = '123';
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download