Jump to content

Recommended Posts

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

Link to comment
https://forums.phpfreaks.com/topic/224819-table-join/
Share on other sites

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?

Link to comment
https://forums.phpfreaks.com/topic/224819-table-join/#findComment-1161656
Share on other sites

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

Link to comment
https://forums.phpfreaks.com/topic/224819-table-join/#findComment-1162070
Share on other sites

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

 

 

Link to comment
https://forums.phpfreaks.com/topic/224819-table-join/#findComment-1162398
Share on other sites

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

Link to comment
https://forums.phpfreaks.com/topic/224819-table-join/#findComment-1162432
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.