PHP MYSQL Nested While loop skips iterations

Question

Need help to get through this Nested WHILE loop.

The Scenario is

These are the 5 tables in my db

user_info(userid,name,picurl)
user_friends(userid,friend_id)
user_post(userid,post_id,post,time)
user_likes(userid,post_id)
user_comments(userid,post_id)

I want to access user_post table and populate the data in my android application. i can access userid,post_id and time from user_post table and using friend_id of the user from friends table. Now to display a complete post with pic and name i need to access name and picurl of the person who's post it is from user_info table. If i need to display one 1 friends post i can do this simply but when there are couple of friends with more than 1 post i can't get the name and picurl of the user and it displays null in json response. I'm using Nested While loop for this operation following is my code.

$userid = $_POST['userid'];

$query = "SELECT * FROM user_friend WHERE userid = '$userid'";
$result = mysql_query($query);

$return_arr = array();
$return_arr['newsfeed'] = array();

//Access userid
while($row = mysql_fetch_assoc($result))    {

echo "Friend Name is ".$row['friend_id']. "<br>";       

$friend_id = $row['friend_id'];
$friend_id = mysql_real_escape_string($friend_id);

//accessing user_info table to access user info
$picquery = "SELECT * FROM user_info WHERE userid = '$friend_id'";
$picresult = mysql_query($picquery);

$pic = mysql_fetch_assoc($picresult);

$row_array['name'] = $pic['name'];
$row_array['picurl'] = $pic['picurl'];

$query2 = "SELECT * FROM user_post WHERE userid = '$friend_id'";
$result2 = mysql_query($query2);

//Access Posts Against userids
    while( $row = mysql_fetch_array($result2) ) {

                $post_id = $row['post_id'];

                //for number of likes
                $likesQuery = "SELECT COUNT(*) as totallikes FROM post_likes        WHERE post_id = '$post_id'";
                $likesResult = mysql_query($likesQuery);
                $likesvalues = mysql_fetch_assoc($likesResult); 
                $num_of_likes = $likesvalues['totallikes']; 

                //for number of comments
                $commentsQuery = "SELECT COUNT(*) as totalcomments FROM post_comments WHERE post_id = '$post_id'";
                $commentsResult = mysql_query($commentsQuery);
                $commentsvalues = mysql_fetch_assoc($commentsResult); 
                $num_of_comments = $commentsvalues['totalcomments'];

        $row_array['post_id'] = $row['post_id'];
        $row_array['userid'] = $row['userid'];
        $row_array['post_text'] = $row['post_text'];
        $row_array['post_time'] = $row['post_time'];
        $row_array['post_num_likes'] = $num_of_likes;
        $row_array['post_num_comments'] = $num_of_comments;

        array_push($return_arr['newsfeed'],$row_array);

    }
}
date_default_timezone_set('Asia/Karachi');
$date = date(' h:i:s a d/m/Y', time());         
echo json_encode($return_arr,JSON_UNESCAPED_SLASHES);

the $picresult query returns picurl and name of the first user/same username only.. for other users its null.. — deejay
– deejay, Commented Nov 28, 2015 at 11:49
please add your db structure for totalcomments, totallikes, user_post, user_info and user_friend. I think there's a much easier way to get most of the info you want with one simple query. Some dummy data also helps. You could use sqlfiddle.com for an easier share — davejal
– davejal, Commented Nov 28, 2015 at 12:37
totallikes and totalcomments are just numeric values returned by SELECT COUNT function to show num of comments and likes for post. And the table structure is user_post(userid, post_id,post_text, post_time) user_info(userid, name, picurl) user_friend(userid, friend_id) from user_friend table the friend_id will be used and matched in user_post table(friend_id from friend table will be userid in user_post) to access the friend's post for all the data required.. — deejay
– deejay, Commented Nov 28, 2015 at 13:28
and @davejal if you can come up with one simple query that will b great.. i'm trying to figure out my error for a couple of days now.. tried many things i saw online but no use.. if you can help me please.. — deejay
– deejay, Commented Nov 28, 2015 at 13:33

Ryan Vincent · Accepted Answer · 2015-11-28 14:14:49Z

1

something like this could help. As I don't have any test data I cannot see how it's working. It should display the post_id along with the count of the likes and the comments

SELECT
  p.post_id,
  COUNT(c.post_id) AS comments_count,
  COUNT(l.post_id) AS like_count
FROM user_post p,
     user_likes l,
     user_comments c
WHERE p.post_id = l.post_id
AND p.post_id = c.post
GROUP BY p.post_id

edited Nov 28, 2015 at 14:14

Ryan Vincent

4,5137 gold badges24 silver badges33 bronze badges

answered Nov 28, 2015 at 13:58

davejal

6,19110 gold badges45 silver badges85 bronze badges

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

2 Comments

deejay Over a year ago

okay now on the android side.. can you help me with the LIST VIEW and LIST VIEW Adapters? i'm complete noo0oob in android..

davejal Over a year ago

I love sql and coding (php using laravel), but haven't looked at android YET. That will be my next step. Sorry can't help you with that, but whenever you run into a problem after doing your research you can ask here on stackoverflow

Collectives™ on Stack Overflow

PHP MYSQL Nested While loop skips iterations

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related