PHP SQL - SELECT DISTINCT in a forum

Author

SELECT DISTINCT in a forum

Nicholas Sherlock

2005-03-12, 8:57 am

Hey all,

I'm a MYSQL newbie, and I'm trying to code a simple threaded forum.

I have a table structure like this:

postid - int primary key
schoolid,threadid,playerid - int
title,text - varchar
postdate - datetime

(Threads are grouped by schoolid, and playerid is the author).

What SELECT statement should I use to show a listing of all the threads
for a certain schoolid? I'm trying something like this (but getting
nowhere):

SELECT DISTINCT threadid postid,playerid,title,postdate FROM mytable
WHERE schoolid=1 ORDER BY postdate.

Cheers,
Nicholas Sherlock

J.O. Aho

2005-03-12, 8:57 am

Nicholas Sherlock wrote:
> Hey all,
>
> I'm a MYSQL newbie, and I'm trying to code a simple threaded forum.
>
> I have a table structure like this:
>
> postid - int primary key
> schoolid,threadid,playerid - int
> title,text - varchar
> postdate - datetime
>
> (Threads are grouped by schoolid, and playerid is the author).
>
> What SELECT statement should I use to show a listing of all the threads
> for a certain schoolid? I'm trying something like this (but getting
> nowhere):
>
> SELECT DISTINCT threadid postid,playerid,title,postdate FROM mytable
> WHERE schoolid=1 ORDER BY postdate.

All messages should already be unique, so you don't use DISTINCT (use it only
if you think there are a possibility that thre are dubblets in a database).

SELECT threadid postid,playerid,title,postdate FROM mytable WHERE schoolid=1
ORDER BY postdate

A good sorce for information about different mysql functions you find at
http://www.mysql.com/search/?q=&x=9&y=5&doc=1&m=0

//Aho

Nicholas Sherlock

2005-03-12, 8:56 pm

J.O. Aho wrote:
>
>
>
> All messages should already be unique, so you don't use DISTINCT (use it
> only if you think there are a possibility that thre are dubblets in a
> database).

But I'm trying to show a list of the threads available. Multiple
messages will have the same threadid because multiple messages belong to
each thread. So I need the query to return distinct threadids.

Thanks for the link

Cheers,
Nicholas Sherlock

Floortje

2005-03-12, 8:56 pm

> But I'm trying to show a list of the threads available. Multiple messages
> will have the same threadid because multiple messages belong to each thread.
> So I need the query to return distinct threadids.

That's not the way to do it... try this !!

treads
id
threadname

text
id
text
id_thread

and then join the two tables

Select * FROM threads
INNSER JOIN text ON
(threads.id=text.id_threads)

But to awnsers your question: use the max and group functions...
SELECT max(postdate) FROM urtable GROUP BY threadid

Floortje

--
www.cavalierpage.com

J.O. Aho

2005-03-12, 8:56 pm

Nicholas Sherlock wrote:
> J.O. Aho wrote:
>
>
>
> But I'm trying to show a list of the threads available. Multiple
> messages will have the same threadid because multiple messages belong to
> each thread. So I need the query to return distinct threadids.

SELECT DISTINCT threadid,title FROM mytable WHERE schoolid=1 ORDER BY postdate

This will work as long as the title of a thread is the same in the rplies as
in the original post.

The morecolumns you take out, the less like eachother each post will be and
then you will get more or less all the posts in your table.

//Aho

Nicholas Sherlock

2005-03-12, 8:56 pm

Floortje wrote:
> That's not the way to do it... try this !!
>
> treads
> id
> threadname
>
> text
> id
> text
> id_thread
>
> and then join the two tables
>
> Select * FROM threads
> INNSER JOIN text ON
> (threads.id=text.id_threads)

Thanks, that looks like a more logical layout.

Cheers,
Nicholas Sherlock

Sponsored Links