Help Sorting Results

[Canman2005]

Hi all

 

I wonder if someone can help, im really really stuck.

 

I have a little message board that I setup, there is one table which runs the whole thing, this is a sample of the table;

 

-------FORUM---------

 

ID  |  TYPE  |  TOPICID  |  TITLE  |      DATE      |  TIME

1          1              1            post 1      2007-04-04    13:23:54

2          1              2            post 2      2007-04-07    23:12:32

3          2              1            reply 1      2007-04-10    08:23:53

4          2              2            reply 2      2007-04-10    12:32:12

 

Let me explain what each field means

 

ID - this is an auto ID

TYPE - 1 means its a main topic \ 2 means its a reply to a main topic

TOPICID - this is the ID number of the main topic

TITLE - the title of the topic or reply

DATE - date posted

TIME - time posted

 

What I currently do, is run a query to get all rows which have a TYPE as 1, basically this gets all the main topics, so if I run the QUERY

 

SELECT * FROM `forum` WHERE `type` = '1'

 

This returns all the topic posts.

 

Once a topic is clicked on, I run another query to get the replies to the topic clicked on, this query is;

 

SELECT * FROM `membersforum` WHERE `type` = '2' AND `topicid` = '".$_GET['topicid']."'

 

This then grabs the replies to the topic selected.

 

What I want to do is to alter the first QUERY

 

SELECT * FROM `forum` WHERE `type` = '1'

 

so that it returns all rows that have the `type` as 1, but ORDER these results with the first row returned, being the row that had the last reply (type 2) left on it.

 

Basically I want to order all rows which have `type` as 1 in an order which has the row with the last reply to it, at the top, so I can sort them by last replied.

 

Does that make sense?

 

Sorry,  I tried to write this as best I can to explain my problem.

 

Does anyone understand what im trying to do and can anyone help?

 

Thanks in advance

 

Dave

[Canman2005]

Just to try to make it easier

 

So with my table example

 

ID  |  TYPE  |  TOPICID  |  TITLE  |      DATE      |  TIME

1          1              1            post 1      2007-04-04    13:23:54

2          1              2            post 2      2007-04-07    23:12:32

3          2              1            reply 1      2007-04-10    08:23:53

4          2              2            reply 2      2007-04-10    12:32:12

 

When the query is run, it should return the rows as

 

ID 2

ID 1

 

The reason it should return ID 2 first, is that the last row to be posted was ID4 (posted at 12:32:12 on 2007-04-10) and as ID 4 has a TOPICID as 2 then it would return row 2 (ID 2) as the first row, as that was the last replied to TOPIC.

 

As we are only returning rows with a TYPE as 1, then it wouldnt return ID 3 or ID 4, but ID 4 was the last posted row.

 

Does that make anymore sense?

 

Thanks

[Canman2005]

If you need anymore info, just ask, im working on this until I either crack it or someone manages to figure it out

 

Thanks

[hvle]

so you like to sort topic by topic which has most recently reply?

 

if this so, which table contains the replies and reply time.  You may want to post the structure of this table as well

[php_tom]

This is a bit of work, but what I'd do is to make your columns TIME and DATE into one column STAMP which contains a timestamp (can be obtained by time() ). When somebody posts a reply to that topic, update the topic's stamp to time().

 

$ins = "INSERT INTO forum (TYPE,TOPICID,TITLE,DATE,STAMP) VALUES (2,...,'test post', ".time().")";
$upd = "UPDATE * FROM forum WHERE TYPE=1 AND TOPICID=... SET STAMP=".time();

 

Then just do

SELECT * FROM forum WHERE type=1 ORDER BY stamp DESC

to get get all topics in the order of latest reply.

 

It's just how I'd do it, maybe there's another way... Hope that helps.

[Canman2005]

hi php_tom

 

Thanks for that, I did think of that, but it seemed like alot of work.

 

I guess that's the best way to do this.

 

Thanks

 

Dave

[teng84]

"""TYPE - 1 means its a main topic \ 2 means its a reply to a main topic"""

eg.

 

topic

id

1

3

4

reply

id  topic

1    3

2    4

 

to select simply

select your filed from topic join reply on topic.id = reply.topic where topic.id = 1

 

maybe this might help!

[Canman2005]

hi teng84

 

Yes, your right about what each TYPE means.

 

How can I do this join? Not something i've ever looked into.

 

Can you give me some guidance?

 

Thanks

[Canman2005]

In my sample table

 

ID 1 & ID 2 - these are main topics (hence `type`=1)

ID 3 & ID 4 - these are replies to main topics (hence `type`=2)

 

ID 3 is a reply of ID 1 as it has TOPICID set to 1 (ID number 1)

ID 4 is a reply of ID 2 as it has TOPICID set to 2 (ID number 2)

[teng84]

ok dude check this

 

http://w3schools.com/sql/sql_join.asp

[Canman2005]

thanks teng84, ill check that out another time, I think php_tom's idea is the easiest for the moment.

 

question, how can I convert an old date and time to the time() format?

[teng84]

sample???

[Canman2005]

okay

 

2007-10-02 13:24:43

 

converting that date to

 

time();

[teng84]

time(2007-10-02 13:24:43) gives you 13:24:43

 

select time(yourfield) from table 

is that what you mean?

[php_tom]

int mktime ( [int $hour [, int $minute [, int $second [, int $month [, int $day [, int $year [, int $is_dst]]]]]]] )

 

So I'd do this:

$old_time = "2007-10-02 13:24:43";
list($date, $time) = explode(" ", $old_time);

$date_arr = explode("-", $date);
$time_arr = explode(":", $time);

$hour = $time_arr[0];
$minute = $time_arr[1];
$second = $time_arr[2];

$year = $date_arr[0];
$month = $date_arr[1];
$day = $date_arr[2];

$stamp = mktime ($hour, $minute, $second, $month, $day, $year);

 

I'm pretty sure this will work.

[Canman2005]

thanks