[Resolved] Ordering problems

Stelf

Hi,

I hope someone can help, i've never encountered this problem before. Is there anyway I can process my 2nd table before I throw it into my query with my first table?

I'm trying to sort my threads alphabetically but the column I will use (message_title) is in another table, when I use a join, it adds the first message and any replies to my result, so I fixed this by using DISTINCTROW, but problem is if the replies to a thread's initial message has a title which would rank it higher than its suppose to be, so messing up the sort and causing a problem such as a thread which title is Zebra appearing at the top because its one of the replies has a title with A in it, for example.

SELECT DISTINCTROW(thread_id), thread_date, thread_views FROM bb_thread, bb_message WHERE thread_forum = '14' AND message_thread = thread_id ORDER BY message_title ASC

I did look at sub selects but I cannot seem to do them on the current version of MYSQL. So is there any solution I could do? Ultimately I’d like to limit the reply results to 1 record per thread if that makes sense.

Thanks in advance.

mourisj

If I understand your problem well, I think you should have an identifier of the initial message of a thread, the others being childs of this one.
You have to select only the messages typed as "initial"

Is it this wht you want to do?

JJ Mouris

kmunk

I'd suggestion following table:

id (autoincrement unique)
parentid (0 = first post)
userid
title
message
date

to order the threads do this select:


$query = "SELECT * FROM threadtable ORDER BY ".$OrderField;
$result = mysql_query($query);

in this way you can order the list by any of the fields in the table with a GET & POST variable OrderField.

then do following:


while($resarr = mysql_fetch_array($result)) {
    $threads[$resarr['parentid']][$resarr['id']] = array("Title" => $resarr['title'], "Date" => $resarr['date'], "Message" => $resarr['message'], "userid" => $resarr['userid']);
}

this will give you an associative array holding every thread and posts.
Doing some recursive programming give you the ability to print out these posts.
eg. to print out a list of every thread - and no post do following:


function printThread($arr, $id=0,  $indent=0, $all=false, $depth=0){
    if(!is_array($arr)) return;
    foreach($arr[$id] as $key) {
        for($i=0; $i<$indent; $indent) echo "&nbsp;";
        echo $arr[$id][$key]['title']."<br />";
        if(is_set($arr[$key])) && ($depth-1 >= 0 || $all)) printThread($id, $arr[$key], $indent+4, $depth-1);
    }
    return;
}

//this will print every main thread:

printThread($threads);

//this will print every post in thread-id = 10 except the mainpost.
//since the first post won't print in the function printThread 
//print it first:
echo $threads[0][10]['title']."<br />";
printThread($threads, 10, 4, true); 
//indent=4 to since first post already has been printed.

hope it makes sense

Stelf

mourisj - thanks for your reply, yep your right I should add that field.

kmunk - also thanks for your reply, this is similar to how I do pull the data out of the forum for displaying currently, the forum is fully working, although as mourisj pointed out a design flaw in the initial database structure.

Currently i'm waiting on a reply to see if they can use Temporary tables, because it looks like this is going to be the eaisest way todo what I need.

Thanks for the replies.

Stelf

Just as quick a follow up incase this helps anyone else.

I fixed my problem by using these 2 queries:

CREATE TEMPORARY TABLE sort SELECT message_thread, thread_id, thread_date, thread_views, message_title FROM bb_message, bb_thread WHERE thread_forum = '14' AND thread_id = message_thread ORDER BY message_date ASC

SELECT DISTINCTROW (message_thread), thread_id, thread_date, thread_views FROM sort ORDER BY message_title ASC

The first query allowed me to sort the messages in the correct order so when I did distinctrow in 2nd query I got the first thread message back.