Retreving data from one table using another table in php

[rayidi]

Hi firends, hope you all dn well. Here i need one thing !

table1 - Which stores all user's posts

( `id`, `post`, `who` )

table2 - Which stores relations

( `id`, `from`, `to`, `status` ) ( `status` contains `accepted` and `hold` )

here i want to retreve the posts of a friend's friends where status = 'accepted'. I want in on single line mysql_query is it possible ?

my Query would be like this.

mysql_query("SELECT `post` FROM `table1` WHERE SELECT FROM `table2` `from` OR `to` AND `status` = 'accepted'');

like in a user profile user's friend posts only should be show.

[junior_rosul]

your sql query can be like that

SELECT 
    `table1`.`post`
FROM
    `table1`
LEFT JOIN `table2`
WHERE `table2`.`status` = 'accepted'
;

[junior_rosul]

can you try your search in MySQL Workbench, its easy to use....

[Biiim]

here i want to retreve the posts of a friend's friends where status = 'accepted'. I want in on single line mysql_query is it possible ?

SELECT post 
FROM posts
WHERE 
    status = 'accepted'
    AND (to IN(SELECT user_id FROM friends WHERE iamallowedtosee = 1) 
        OR from IN(SELECT user_id FROM friends WHERE iamallowedtosee = 1)
    )
ORDER BY date ASC

Its better performance to use joins instead of subqueries as joins can make use of indexes better, but dunno how you have setup the relationship for a user to be able to see another users posts. You need a table saying "user 1 is allowed to see user 3,6,12 and 14's posts" like this:

user_post_access
inc_id,user_id,allowedtosee_id
1,1,3
2,1,6
3,1,12
4,1,14

then do subqueries to it replacing the ones above:

AND (to IN(SELECT allowedtosee_id FROM user_post_access WHERE user_id = 1) 
            OR from IN(SELECT allowedtosee_id FROM user_post_access WHERE user_id = 1)
        )