Problem using MySQL query using WHERE IN clause

Question

On my website I am trying to program a feature where a user can choose to get 'updates' from another user. When a user clicks 'receive updates,' it throws that users ID in the database, alongside with other users they want to receive updates from. For Example (4,5,6,7,8). My tables look like this

members | updates
-----------------
     id | id
updates | member_id 
        | content
        | content_date

When I query the database I want to pull out of the updates table only the updates from the already specified users in members:updates.

$sql_updates = mysql_query("SELECT * 
                              FROM updates a, 
                                   members b 
                             WHERE a.member_id IN b.updates 
                          ORDER BY a.content_date DESC 
                             LIMIT 10");

while($row = mysql_fetch_array($sql_updates)) {

Is this the best way to go about it?

Does the b.updates column store the values as a comma delimited list? — OMG Ponies
– OMG Ponies, Commented Jul 12, 2011 at 1:41

Kerrek SB · Accepted Answer · 2011-07-12 01:41:07Z

4

First off, spell you column names correctly -- mem_id or member_id, what shall it be? Second, this is just a plain inner join:

SELECT members.id AS mem_id, updates, content, content_date
FROM members JOIN updates ON(members.id = updates.member_id)
ORDER BY content_date DESC
LIMIT 10;

answered Jul 12, 2011 at 1:41

Kerrek SB

480k96 gold badges904 silver badges1.1k bronze badges

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

1 Comment

dystopia Over a year ago

didn't mean to have mem_id in there, typed it up in a hurry. Thanks!

Mr Coder · Accepted Answer · 2011-07-12 01:46:50Z

0

in Mysql "IN" is like "OR" of programming operator if you are suing IN you need to provide an array of b.updates not just the column updates or use subquery to return array

$sql_updates = mysql_query("
SELECT * FROM updates a, members b 
WHERE a.mem_id IN (SELECT updates from members)
ORDER BY a.content_date DESC LIMIT 10");

Other or better way could be using JOIN

$sql_updates = mysql_query("
SELECT * FROM updates a JOIN members b ON (a.mem_id = b.updates) 
ORDER BY a.content_date DESC LIMIT 10");

answered Jul 12, 2011 at 1:46

Mr Coder

8,1965 gold badges49 silver badges74 bronze badges

Comments

dabest1 · Accepted Answer · 2011-07-12 01:53:13Z

0

I would recommend using a third table "subscriptions" which would link "members" and "updates" tables.

Sample table structure:

subscriptions  
-------------  
id  
member_id  
update_id

For each subscription (click on "receive updates"), you would create a row in "subscriptions" table.

edited Jul 12, 2011 at 1:53

answered Jul 12, 2011 at 1:47

dabest1

2,5426 gold badges26 silver badges26 bronze badges

Comments

JStead · Accepted Answer · 2011-07-12 02:18:33Z

0

A join can give you extra results in cases where an in would make sense. Usually I recommend the exists operator because it performs the same as a join but gives the behavior of an in. You can save yourself using a distinct later on.

$sql_updates = mysql_query("SELECT * 
                              FROM updates a
                      where exists (select * from members b                              
                        WHERE a.member_id  b.updates)
                          ORDER BY a.content_date DESC 
                             LIMIT 10");

answered Jul 12, 2011 at 2:18

JStead

1,73011 silver badges12 bronze badges

Collectives™ on Stack Overflow

Problem using MySQL query using WHERE IN clause

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related