1 Replies - 344 Views - Last Post: 21 March 2013 - 08:00 PM Rate Topic: -----

#1 adn258  Icon User is offline

  • D.I.C Addict

Reputation: 11
  • View blog
  • Posts: 761
  • Joined: 31-August 11

Getting Mysql Columns With The Most Occurrences Of The Same Thing?

Posted 21 March 2013 - 05:35 PM

Hey guys so I have a complicated query that I am trying to use PDO as my API to solve here. So I have two tables that I'm wanting to work with. Without going into too many details one of these tables stores pages with user id's and the other table stores comments for pages but has a profile_id that corresponds with with the page so all comments with the same profile_id as the page id will load all of those comments.


So I want to select the profile pages and their key columns with the most comments. This could be done by doing many queries for instance by first looking at the comment table and using order by so that columns with the same repeating profile_id get shown first and then you could use that ID individually to select content from the profile table but this is annoying.

for example

$query = "select a.person_name_first from profiles a join comments b on a.id = b.profile_id";



For example using this query every time there is a match found of the profile ID in the comment ID it adds the person over and over again to an array but I only want them added ONCE but I still want the order of the profiles added to correspond with how many comments they have!! It's very confusing. thanks guys

Is This A Good Question/Topic? 0
  • +

Replies To: Getting Mysql Columns With The Most Occurrences Of The Same Thing?

#2 adn258  Icon User is offline

  • D.I.C Addict

Reputation: 11
  • View blog
  • Posts: 761
  • Joined: 31-August 11

Re: Getting Mysql Columns With The Most Occurrences Of The Same Thing?

Posted 21 March 2013 - 08:00 PM

So I think I figured it out. I've practically been banging my head against the wall trying to figure this out but I did. If someone can find a more efficient way let me know. Phew


"select p.person_name_first, p.id, c.profile_id, count(c.profile_id) as idnum from profiles p, comments c where p.id = c.profile_id group by c.profile_id order by idnum desc"



This will return then it appears in order by the profile that has the most comments using the same id in the comments table it needed basically a join and a group by
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1