table join

[doddsey_65]

im trying to join my likes table with my post table to display who likes the relevant post. Unfortunatly it is displaying the post several times for how ever many likes there is because of the way i have joined it:

 

$post_query = mysqli_query($link, "SELECT 
                                p.*,
                                u.user_id, u.user_username, u.user_avatar,
                                u.user_sig,
                                l.post_id, l.like_username

                                FROM ".TBL_PREFIX."posts as p
                                
                                LEFT JOIN ".TBL_PREFIX."members as u
                                ON p.post_poster = u.user_username
                                
                                INNER JOIN ".TBL_PREFIX."post_likes as l
                                ON p.post_id = l.post_id
                                
                                WHERE p.topic_id = '$topic_id'
                                
                                ORDER BY post_time ASC
                                ") or die(mysqli_error($link));

        $likes = array();
        while ($post_info = mysqli_fetch_array($post_query, MYSQLI_ASSOC))
        {    
         $likes[] .= $post_info['like_username'];

 

what would be the best way to perform this join?

 

like table is as follows:

 

like_id             post_id           like_username
   1                  340                doddsey65

[Muddy_Funster]

I'm not sure I get exactly what it is that you want to do here.  You are reffering to the same TABLE_PREFIX rather than giving an actual table name.  could you post the table structure for each of the three tables you are using, with the fields you are looking for highlighted?

[doddsey_65]

forum_posts:
post_id  post_subject

forum_post_likes:
like_id  post_id  like_username

 

basically show all like_usernames under the post they refer to.

[jdavidbakr]

I think you're wanting to use "group by"

 

select post_id, post_subject, group_concat(like_username separator ', ') likes
from forum_posts
left join forum_post_likes using (post_id)
group by forum_posts.post_id

 

should return something like:

 

post_id  post_subject  likes

1          'likable post'  'joe, sam, fred'

2          'stupid post'    NULL

[doddsey_65]

thanks it works fine. One last thing though. I want to display the first 4 usernames and then show the rest as a numerical representation. eg:

 

user1, user2, user3, user4 and 5 others like this

 

any ideas?

[DarkKnight2011]

Hi doddsey_65,

 

you would deal with that side of things using php, not the query and there are a number of ways to do it, one possible way would be to load all the usernames into an array from the database for that post and then you could do something like...

 

// example array
$likesArray = ('jon', 'bob', 'fred', 'frank', 'james');

// Count the number of others (total minus the number your displaying by name
$others = count($likesArray) - 2;

// build the string
$likeString = ucwords($likesArray[0]) . ", " . ucwords($likesArray[1]) . " and $others like this";

 

which should in theory give you - Jon, Bob and 3 others like this

 

note: ucwords capitalises the first letter of the word

 

 

[doddsey_65]

i used the table join as above and added the result in an array. however when there are no likes for a post it displays that there is 1 like for it. a count of the array shows that 1 person likes the post when there is no one in the table that likes it:

 

$like_list = "";
            $likes = explode(", ", $post_info['likes']);
            $amount_likes = count($likes);
            $slice = array_slice($likes, 0, 3, true);
            $remain = array_slice($likes, 4, $amount_likes, true);
            $remain_num = count($remain);
            
            if ($amount_likes > 4)
            {
                for($i=0; $i<=3; $i++)
                {
                    $like_list .= $likes[$i].", ";
                }
                $like_list .= " and $remain_num others like this - $amount_likes";
            }
            elseif ($amount_likes < 1)
            {
                $like_list .= "0 Users Like This - $amount_likes";
            }
            elseif ($amount_likes <= 3)
            {
                $like_list .= $post_info['likes']." likes this - $amount_likes";
            }

[DarkKnight2011]

Oh yeah, well spotted