CODEWITHSUNDEEP
phpmysql
Zai Blitz
Zai Blitz

Reputation: 41

Get all friends post using mysql

I'm currently developing a social networking site that display the posts of login user including the posts of his/her friends I've successfully created the script to show all posts by getting first all the friends of login user. Then put them in a session. Then get all posts using in_array();

MY DB SCHEMA
person_tbl  
  pr_id
  pr_name 

post_tbl 
  po_id
  po_message
  po_prid  (reference pr_id )

friend_tbl
 fr_id
 fr_from_prid
 fr_to_prid

SAMPLE DATA
pr_id   pr_name   
1       Jeniffer
2       Dale
3       Melissa

po_id   po_prid    po_message
1         2        Post by Dale
2         2        Post by Dale 2
3         1        Post by Jen
4         3        Post by Melissa

fr_id  fr_from_prid   fr_to_prid
1          1             2         // Jennifer and Dale were friends

Here is my code.

$LOGIN_USER_ID = 1 // e.g id of login user ( person_tbl pr_id in database)

unset( $_SESSION['friends'] );
$_SESSION['friends'] = array();

$frdt = ( get all friends by LOGIN USER ); // from database
foreach( $frdt as $fkey => $fval ) {    // get all friends
    $fr_from = $fval['fr_from_prid'];
    $fr_to = $fval['fr_to_prid'];

    if( $LOGIN_USER_ID != $fr_from ) {
      $_SESSION['friends'][] = $fr_from;
    }
    if( $LOGIN_USER_ID != $fr_to ) {
      $_SESSION['friends'][] = $fr_to;
    }
}

$_SESSION['friends'][] = $LOGIN_USER_ID;  // include login user id in friends session

// e.g display of id of friend ( 2, 3 and 1 ) 1 for  login user.
$friends_of_user = $_SESSION['friends'];  

$post_tbl = get_all_post();
foreach( $post_tbl as $key => $val ) {
   $id = $val['po_id'];

   if( in_array( $po_prid, $friends_of_user ) ) {
       // DISPLAY ALL POST
   }    
}

Just like I said, I've successful get all post. However what I want is to limit the post when loading the page then like facebook style if page is scroll down it will load another posts. To make it short, all I want is select post limit 0, 20 then if user scroll down limit 20, 40 then 20, 60 and so on. Is it possible to get posts only using the sql query without doing stuff like session? Thanks in advance.

Upvotes: 1

Views: 915

Answers (1)

Zai Blitz
Zai Blitz

Reputation: 41

You inspired me brah @Galithiel! Got the answer to my own question!

select po_message
from post_tbl
LEFT JOIN friend_tbl
  ON po_prid=fr_from_prid OR po_prid=fr_to_prid
WHERE po_prid={$LOGIN_USER_ID}
  OR fr_from_prid={$LOGIN_USER_ID}
  OR fr_to_prid={$LOGIN_USER_ID}
LIMIT 0 , 10

Upvotes: 2

Related Questions