mysql sort by a column

Posted on 2004-11-08
Last Modified: 2008-01-09
I have a table that stores comment info for a message board.  The table has comment_body, id, and commenter_id in it.  there is another table with all the members that can post comments (member_id, member_name).  I want to be able to sort a question by the commenter name (not id).  I can't figure out how to sort the table alphabetically by info that is stored in another table.

if it is a complicated solution, I will raise the points.

Question by:mattjp88
    LVL 8

    Accepted Solution

    Hi mattjp88,

    You should be able to sort in sql, just "select comment_body, member_name from comment_table, member_table where member_table.member_id = comment_table.commenter_id order by member_name".

    LVL 2

    Expert Comment

    try this,

    you can sort member_name as,

    select comment_body, member_name from comment_table, member_table where member_table.member_id = comment_table.commenter_id order by member_name DESC;
    LVL 8

    Expert Comment

    I think most of time alphabetically means ascending order.
    LVL 2

    Expert Comment

    You COULD read the entire array of comments in to an array, and while doing that, do lookups on the user names.

    So it would be like...

    while ($comment = mysql_fetch_row($comments))
       $username = mysql_fetch_row(mysql_query("select `Name` from `users` where `ID` = '$comment[2]' limit 1",$db))

    Put all of the comment data in to a single line of information, seperated by something so you can explode it later.
    Replace the IDs with the names, make the names the first items in the line, and do a sort() on the array...

    It's a little more complicated than doing the ordering in the SQL statement as shown above, but it's another way to do it. :)
    LVL 6

    Author Comment

    Well, actually, I am going to be sorting alphabetically either A-Z or Z-A, whichever the user selects.  Thanks for the code!

    the code is exactly like inq's, points them them.  Yours does work however.  thanks!

    Good alternate approach, although it isn't as efficient as doing it via the DB.

    Thanks all!

    Expert Comment

    Please try this this may be helpful to you

    Select c.comment_body, m.memeber_name from comment_table as c, member_table as m where c.comment_id=m.member_id order by m.member_name;

    Keep the charcter fields in appropriate syntax.
    This query is fast enough to get the desired resuts.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    Join & Write a Comment

    I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
    Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
    The viewer will learn how to dynamically set the form action using jQuery.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now