1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Ordering data using mysql

Discussion in 'MySQL' started by aleco, Apr 3, 2005.

  1. #1
    I've just about finished setting up http://mms.mob453.com/ - but if you go to the comments section, it shows the first comment rather than the latest when there's more than one comment for the pic.

    Basically i've got a table with imageid, commentid, commenttext, etc in, I use the GROUP BY imageid to only show one comment for each unique image, and then ORDER BY commentid, so the latest comments are at the top.

    However, GROUP BY shows the first comment rather than the latest - so my question is, is there a way or make the GROUP BY show the latest comment rather than the first?

    I'm currently using this:
    SELECT * FROM `commentstable` GROUP BY `imageid` ORDER BY `commentid` DESC

    And example data:


    commentid      imageid     commenttext
    1                5         blah blah this is my first comment
    2                6         this is my first comment on a different image
    3                5         this is my second comment for the first image
    Code (markup):
    And instead of showing commentid 3 when grouped, it shows commentid 1

    Anyone follow me as I'm getting confused myself!? If so, any suggestions?

    Thanks!
     
    aleco, Apr 3, 2005 IP
  2. nullbit

    nullbit Peon

    Messages:
    489
    Likes Received:
    19
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Edit: Sorry, stupid suggestion.
     
    nullbit, Apr 3, 2005 IP
  3. aleco

    aleco Active Member

    Messages:
    54
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    91
    #3
    Thanks for the quick reply - I tried that but unfortunately that didn't help either :(
     
    aleco, Apr 3, 2005 IP
  4. daboss

    daboss Guest

    Messages:
    2,249
    Likes Received:
    151
    Best Answers:
    0
    Trophy Points:
    0
    #4
    i don't think you can do what you want to do using a simple sql command. also, the group by clause is not supposed to be used for that purpose - you would typically use it to facilitate a function like count(*)...

    what you can do is to select your records ordered by imageid and then by commentid. then using a loop, loop through the resultset and only pick out the first record for each unique imageid...
     
    daboss, Apr 3, 2005 IP
  5. aleco

    aleco Active Member

    Messages:
    54
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    91
    #5
    That's done the trick - thanks! Was only looking at it from a mysql point of view, didn't even think about looping it in php :eek:

    Thanks for all the help! :D
     
    aleco, Apr 3, 2005 IP
  6. J.D.

    J.D. Peon

    Messages:
    1,198
    Likes Received:
    65
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Sorting in PHP is quite a bit less effective than using SQL, especially with large data sets. Remove the quotes from your SQL:

    SELECT * FROM commentstable GROUP BY imageid ORDER BY commentid DESC

    J.D.
     
    J.D., Apr 3, 2005 IP