MySQL Order By...

[Inssider]

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,&quot;author&quot;
$newstitle=mysql_result($newsdisplay,i,&quot;title&quot;
$newsdate=mysql_result($newsdisplay,i,&quot;date&quot;
$newsbody=mysql_result($newsdisplay,i,&quot;body&quot;
echo &quot;<P>$subject<P>$author - $date<BR>$body<P>&quot;;
}

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? [sig][/sig]

 

[devnull22]

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
[sig]<p>Ken<br><a href=mailto:admin@mysqlwebring.com>admin@mysqlwebring.com</a><br><a href=

http://www.mysqlwebring.com>MySQL

Webring</a><br>

http://www.411eshop.com<br>

[/sig]

 

[Inssider]

Will that order my topics or my replies? [sig][/sig]

 

[devnull22]

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.
[sig]<p>Ken<br><a href=mailto:admin@mysqlwebring.com>admin@mysqlwebring.com</a><br><a href=

http://www.mysqlwebring.com>MySQL

Webring</a><br>

http://www.411eshop.com<br>

[/sig]