CODEWITHSUNDEEP
phpmysqlsql
dystopia
dystopia

Reputation: 93

Problem using MySQL query using WHERE IN clause

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?

Upvotes: 0

Views: 832

Answers (4)

JStead
JStead

Reputation: 1730

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");

Upvotes: 0

dabest1
dabest1

Reputation: 2502

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.

Upvotes: 0

Mr Coder
Mr Coder

Reputation: 8186

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");

Upvotes: 0

Kerrek SB
Kerrek SB

Reputation: 476930

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;

Upvotes: 4

Related Questions