MySQL select where equal to multiple values

Question

I have two databases, where I store who is following who and another which stores the posts the user makes.

I want to select all of the people a user is following from the following database and echo out the usernames of those who that user is following and query the posts database for posts of that user.

My problem is what if a user is following multiple users, I echoed out of the user id's of the people this user is following and I get 44443344330

When I separate each id with commans, I get:

44,44,33,44,33,0,

so let's give that a variable of $user_ids;

$user_ids = "44,44,33,44,33,0, ";

the query:

$get_posts = mysql_query("SELECT * FROM posts WHERE userid = '$user_ids'");

but all it does is show the records of the first user id, 44.

How can I retrieve all of the records for all the users?

Kerrek SB · Accepted Answer · 2011-07-29 18:59:52Z

39

The query should be:

SELECT * FROM posts WHERE userid IN (44,44,33,44,33,0)

However, you may have to rethink your data model and make sure it is normalized, so that you can express this construction directly in the databse without echoing into a comma-separated string.

Why do you have two databases? Do you mean two tables?

answered Jul 29, 2011 at 18:59

Kerrek SB

480k96 gold badges904 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

james_bond · Accepted Answer · 2018-03-18 23:39:32Z

28

Maybe you want to use IN

SELECT * FROM posts WHERE userid IN ($user_ids)

edited Mar 18, 2018 at 23:39

answered Jul 29, 2011 at 18:58

james_bond

6,9163 gold badges30 silver badges39 bronze badges

1 Comment

Jeff Over a year ago

if the data type for the id's is set to Integers this won't work. It needs to be ($user_ids) not ('$user_ids')

Callum · Accepted Answer · 2020-12-07 09:52:49Z

4

Given that you have two tables, not two databases, the easiest way to match multiple values for a specific column would be the following:

SELECT * FROM posts WHERE userid IN (44,44,33,44,33,0)

*A small point that I ran into when doing this. If you are matching to a column that is of type VARCHAR(n), be sure to wrap your matching values in 'single quotes', not "double quotes"

e.g:

SELECT * FROM posts WHERE name IN ('foo','bar','alpha','beta')

answered Dec 7, 2020 at 9:52

Callum

414 bronze badges

Comments

ain · Accepted Answer · 2011-07-29 19:11:30Z

1

Assuming you have two tabels, not databases, and that the table (lets call it "friends") which describes who is following who is like

table friends(
  userid
  friendid
)

then query to get posts posted by X's friends would be

SELECT
 p.* 
FROM posts p
  JOIN friends f ON(p.userid = f.friendid)
WHERE f.userid = X

answered Jul 29, 2011 at 19:11

ain

22.8k3 gold badges60 silver badges76 bronze badges

1 Comment

Uzumaki Naruto Over a year ago

hey what if I replace the values ... it is possible that user id has the id of my friend and friend id has my id then what will you do ?? if a person sends me a friend request and I accept then the sender will be in user id and receiver will be friend id ... can u help ??

Shaun Hare · Accepted Answer · 2011-07-29 18:59:18Z

0

$get_posts = mysql_query("SELECT * FROM posts WHERE userid in '($user_ids)'");

answered Jul 29, 2011 at 18:59

Shaun Hare

3,8712 gold badges26 silver badges36 bronze badges

Comments

Luca Detomi · Accepted Answer · 2015-12-22 11:44:48Z

0

$var= str_replace(","," or userid = ","userid =$your_data_from_db");

your myqsl_query = SELECT * FROM posts where $var

edited Dec 22, 2015 at 11:44

Luca Detomi

5,7668 gold badges58 silver badges81 bronze badges

answered Dec 22, 2015 at 11:40

Ethilé Landry Tanon Abédjinan

11 bronze badge

1 Comment

user1978550 Over a year ago

Is there a benefit to using the or userid = version of this instead of using just in?

Collectives™ on Stack Overflow

MySQL select where equal to multiple values

6 Answers 6

Comments

1 Comment

Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related