1

I tried searching and searching for an answer to my problem but haven't successfully found one so I'm hoping one of you more experienced gurus can help me out with this MySQL issue I'm having.

On one of my websites I allow people to basically POST on their profile. Then on the side of my website, I list the last 8 profile posts that are authored by the recipient (in other words they post on their own profile) and do not show on the side posts done by other people. I call these "Updates"

Recently I noticed a woman had posted twice on her profile and it was showing two posts on the side of the website from her. I only want to show one post by each person, and only the last 8 "Updates"...

What I did which seemed to work at first, is use a GROUP BY in my query as you see here: 

SELECT `ratemynudepics`.* FROM `ratemynudepics` WHERE `user_id` = `author` AND `body` <> '' GROUP BY `author` ORDER BY `id` DESC LIMIT 8

Her multiple posts on the side went down to just one post and I thought it was fixed until I tried posting a test "Update" of my own on my own profile. It didn't show up on the side at all.

I tried using a DISTINCT instead of GROUP BY as seen here:

SELECT DISTINCT `user_id`, `author`, `id`, `body`, `date` FROM ratemynudepics WHERE `user_id` = `author` AND `body` <> '' ORDER BY `id` DESC LIMIT 8

That change made it so my post did in fact appear on the side, but the woman's two updates were back underneath mine.

I've tried all sorts of variations and used both DISTINCT and GROUP BY at the same time and no matter what I try nothing will properly show mine up top, (which should be the case since mine is the last record in the database) with only one listing of the woman's. I checked many times to make sure she didn't post with a different user_id and sure enough testing the query in phpmyadmin shows two listings for her, both having the same user_id, and author. I'm not sure why DISTINCT is allowing for multiple rows with the same user_id using the above query.

I tried the following query:

SELECT DISTINCT `user_id`, `id`, `author`, `date`, `body` FROM ratemynudepics WHERE `user_id` = `author` AND `body` <> '' GROUP BY `author` ORDER by `id` DESC LIMIT 8

and it only shows her once but doesn't show me in any of those 8 results even though again my row is the last one inserted into the database.

Can someone please help me to understand what I'm doing wrong here so that I can properly display only a maximum of 1 row per user but not abandon my latest database result which should be the 1st result? Much appreciated!! Peace

Edit - Here is some sample result data to match the queries Notice the 2nd result set shows my 'test' update but shows a user's post twice, which she did post the same text twice, about a month apart.

SELECT DISTINCT `id`, `user_id`, `author`, `body`, `date` FROM ratemynudepics WHERE `user_id` = `author` AND `body` <> '' GROUP BY `author` ORDER BY `id` DESC LIMIT 8

AND 

SELECT `ratemynudepics`.* FROM ratemynudepics WHERE `user_id` = `author` AND `body` <> '' GROUP BY `author` ORDER BY `id` DESC LIMIT 8
Both above queries produce the following  

id   user_id   author   body                          date
122  4391      4391     Email me at [blocked]         1497299836
83   4270      4270     I&#039;m back..lol..ho        1474258804
79   4303      4303     Send me a message if y        1473959358
76   4362      4362     This place is a morgue.       1472580597  
68   4358      4358     Smile, have a nice day        1470897755
57   4344      4344     Can someone rate my bo        1467946896
55   4338      4388     hey lets chat                 1466792249
50   4319      4319     hi whats up                   1465604578

SELECT DISTINCT `id`, `user_id`, `author`, `body`, `date` FROM `ratemynudepics` WHERE `user_id` = `author` AND `body` <> '' ORDER BY `id` DESC LIMIT 8 

produces the following results

id   user_id   author   body                          date
153  1         1        test                          1510212341
135  4391      4391     Email me at [blocked]         1508374921
122  4391      4391     Email me at [blocked]         1497299836
83   4270      4270     I&#039;m back..lol..ho        1474258804
79   4303      4303     Send me a message if y        1473959358
76   4362      4362     This place is a morgue.       1472580597  
68   4358      4358     Smile, have a nice day        1470897755
57   4344      4344     Can someone rate my bo        1467946896

The bottom result shows my test row, but shows the woman's multiple posts. The first result shows only one of hers but leaves mine out... Any ideas? Thanks!!

WhoCaresAboutMyName
  • 11
  • 3

1 Answers1

0

Thanks to another post on this website I found the answer to my problem if I use the following query: 

SELECT `id`, `user_id`, `author`, `body`, `date` 
FROM ratemynudepics 
WHERE id IN ( SELECT MAX(id) 
              FROM ratemynudepics 
              WHERE `user_id` = `author` AND `body` <> '' 
              GROUP BY user_id ) 
ORDER BY `id` DESC LIMIT 8

Produces

    id   user_id   author   body                          date
    153  1         1        test                          1510212341
    135  4391      4391     Email me at [blocked]         1508374921
    83   4270      4270     I&#039;m back..lol..ho        1474258804
    79   4303      4303     Send me a message if y        1473959358
    76   4362      4362     This place is a morgue.       1472580597  
    68   4358      4358     Smile, have a nice day        1470897755
    57   4344      4344     Can someone rate my bo        1467946896
    55   4338      4338     hey lets chat                 1466792249

Thanks for everyone's help nonetheless. Happy Halloween! :D

Here is the post that helped me with the answer How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
WhoCaresAboutMyName
  • 11
  • 3