×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

MySQL Order By...

MySQL Order By...

MySQL Order By...

(OP)
I'm currently coding a forum of my own, and need some help. I have all the information stored in a MySQL database in the same table but on different rows (author, date, subject, body, etc.) and I would like the threads on the main page to be displayed in such a way so that the most-recently replied-to topic is at the top. Now, I have been displaying the topics by using the following snippet:


mysql_connect(myhost,myuser,mypass);
$displaynews=mysql(myname,"select * from newsposts");
$newsnum=mysql_numrows($displaynews);
for($i=0;$i<$numrows;$i++)
{
    $newsauthor=mysql_result($newsdisplay,i,"author");
    $newstitle=mysql_result($newsdisplay,i,"title");
    $newsdate=mysql_result($newsdisplay,i,"date");
    $newsbody=mysql_result($newsdisplay,i,"body");
    echo "<P>$subject<P>$author - $date<BR>$body<P>";   
}

What this does is display the topics so that the one most recently _POSTED_ is on the top, and the next recent one is next, and so on. However, what I want to make it do is have the most recently _REPLIED TO_ topic on the top, and so on. Is there a way for me to do this?

RE: MySQL Order By...

Hi Inssider,
   Where are you stor'n the replies? you'll need to give us
the table defs.
I'd recommend keeping your forum topics seperate from your
replies. Don't put everything (topics,reply) in the same
table.

once the reply is in a seperate table (ie reply_table).
do a
   SELECT * FROM reply_table
   WHERE reply_table.topic_id = '$topic_id'
   ORDER BY reply_table.date DESC


hope this helps
Ken

Ken
admin@mysqlwebring.com
MySQL Webring
www.411eshop.com

RE: MySQL Order By...

(OP)
Will that order my topics or my replies?

RE: MySQL Order By...

HI Inssider,
   heres the table def.
one table will hold your topics.
one table will hold your replies.
topics_table will have topic_id (auto_increment)
reply_table will be link to topics via
topic_id.

that's how those two tables a linked.

when someone replies , insert it into the reply_table
along with the topic_id it relates to.

when you want to get all replies in a topic then..

SELECT * FROM reply_table WHERE topic_id=2;

the above will give you all reply from topic_id 2.

here is example table def.

CREATE TABLE topic(
        topic_id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
        forum_id        INT UNSIGNED NOT NULL,
        topic           VARCHAR(255) NOT NULL,
        body            TEXT,
        creator         VARCHAR(20) NOT NULL,
        post_date       DATETIME NOT NULL,
        last_modify     TIMESTAMP(18),
        PRIMARY KEY     (topic_id),
        INDEX           (forum_id),
        INDEX           (post_date),
        INDEX           (topic),
        INDEX           (creator)
);

CREATE TABLE reply(
        reply_id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
        topic_id        INT UNSIGNED NOT NULL,
        body            TEXT,
        creator         VARCHAR(20) NOT NULL,
        post_date       DATETIME NOT NULL,
        last_modify     TIMESTAMP(18),
        PRIMARY KEY     (reply_id),
        INDEX           (topic_id),
        INDEX           (post_date),
        INDEX           (creator)
);

Hope this helps.

Ken
admin@mysqlwebring.com
MySQL Webring
www.411eshop.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close