CODEWITHSUNDEEP
phpmysql
Nibbler
Nibbler

Reputation: 1

Get row where a table contains a user id

I have a table with 4 columns id, user, text and date.

I'm trying to get the rows where "user" contains one of many userid. There can be one to infinity user id's there.

Example of user id's

"4, 5, 23"

Example of "user" column

"4, 12, 44"

Basically I'm trying to make a status update system where only friends of the user can see the status updates, a bit like facebook and twitter.

Upvotes: 0

Views: 147

Answers (2)

sark9012
sark9012

Reputation: 5727

If I understand your problem correctly, you want to break this into multiple tables.

A status table with id, userid, text and date.

A user table with id, username, etc (up to you).

A friendship table with id, userid, friendid. (two entries for a friendship, you could do friend1 and friend2 but that would mean checking both fields both ways round for each query).

The last table is based on the need for a friendship to be two ways! So the facebook convention as opposed to the twitter, following but maybe no followed back, convention.

You would then show updates from a user that you (as the logged in user) have a friendship with.

SELECT s.text, s.date, u.username, s.userid FROM status s INNER JOIN username u ON s.userid = u.id INNER JOIN friendship f ON f.friendid = s.userid WHERE f.userid = 'LOGGED IN USER ID' ORDER BY s.date DESC LIMIT 10

Change LOGGED IN USER ID to the userid of the current logged in user. This is usually set using a session variable.

The LIMIT 10 will show the newest 10 status updates!

Then display the text and date and the username with the userid linking to their profile (if applicable)!

Upvotes: 1

Fiona - myaccessible.website
Fiona - myaccessible.website

Reputation: 14921

You shouldn't have a column to store multiple user IDs. Make a table and refer to it and then the lookup will be simple.

Upvotes: 5

Related Questions