GROUPS

[tinker]

I have this query, but the date created is always the first not the last, any suggestions?

$tnT = $db_table_prefix."forum_thread";	//	(id, forum_id, title, date, updated, views, owner_id, type, sticky, state)
$tnU = $db_table_prefix."login_users";//	id, uname, pass, groups, auth, email, terms, coppa, date
$tnP = $db_table_prefix."forum_post";	//	(id, thread_id, title, msg, owner_id, date_created, date_modified, format, notify)

$s = "SELECT ".$tnP.".date_created, ".$tnT.".id, ".$tnT.".forum_id, ".$tnT.".title, ".$tnT.".views, ".$tnT.".type, ".$tnT.".sticky, ".$tnT.".state, ".$tnU.".uname, COUNT(".$tnP.".thread_id) as replies
 FROM ".$tnT." 
 LEFT JOIN ".$tnP." on ".$tnT.".id = ".$tnP.".thread_id 
 LEFT JOIN ".$tnU." on ".$tnT.".owner_id = ".$tnU.".id 
 WHERE ".$tnT.".forum_id = ".$cata[1]." GROUP BY ".$tnP.".thread_id ORDER BY ".$tnT.".sticky DESC, ".$tnP.".date_created DESC, ".$tnT.".updated DESC";

 

I've tried changing the table joinings and ordering, but I don't want to use temporary tables...

 

Cheers

[toplay]

Well, it looks like you're sorting by sticky first then date_created, but you do have DESC specified correctly to get the most current date first. I assume that date_created is a DATE or DATETIME data type and not CHAR or VARCHAR type, correct?

 

Show sample data, what you're getting now, and how you want it to be.

 

[tinker]

1) Al my date vars are always generated with time()

 

2)

id forum_id title date updated views owner_id type sticky state

1 1 Help 1199852048 1199855690 5 1 0 -1

2 1 Run 1199852048 1199855989 4 1 0 -1

3 1 A Sticky 1199852048 1199852048 1 1 0 1

4 1 Another Sticky 1199852048 1199852048 0 1 0 2

5 1 Noob13 1200324096 1200324884 6 1 0 -1

 

id thread_id title msg owner_id date_created date_modified format notify

1 1 A Title Please help me! 1 1199852048 0 0 0

2 1 t Please, you gotta help me! 1 1199852048 1199852048 0 0

3 2 I am the One! Inspiral carpet burns! 1 1199852048 0 0 0

4 3 I am the One! Please help me! 1 1199852048 0 0 0

 

id uname pass groups auth email terms coppa date_reg

1 admin b11841819767d2c8dfb5cd329f54015f 10 email@a.c agree 2 0

2 user b11841819767d2c8dfb5cd329f54015f 10 email@a.c agree 2 0

3 test b11841819767d2c8dfb5cd329f54015f 1 email@a.c agree 2 0

 

3)

This is roughly what the output is...

x x Title Views Replies Starter

state 0 Another Sticky 0 1 - 9 of January 2008, at 4.14 am admin

state 0 A Sticky 1 1 - 9 of January 2008, at 4.14 am admin

state 0 Noob13 6 2 - 14 of January 2008, at 3.21 pm admin

state 0 Run 4 2 - 9 of January 2008, at 4.14 am admin

state 0 Help 5 3 - 9 of January 2008, at 4.14 am admin

 

And this was the statement used (it doesn't include the date_created from the posts table, but the result is the same as previous):

$s = "SELECT ".$tnP.".date_created, ".$tnT.".id, ".$tnT.".forum_id, ".$tnT.".title, ".$tnT.".views, ".$tnT.".type, ".$tnT.".sticky, ".$tnT.".state, ".$tnU.".uname, COUNT(".$tnP.".thread_id) as replies
 FROM ".$tnT." 
 LEFT JOIN ".$tnP." on ".$tnT.".id = ".$tnP.".thread_id 
 LEFT JOIN ".$tnU." on ".$tnT.".owner_id = ".$tnU.".id 		 
 WHERE ".$tnT.".forum_id = ".$cata[1]." 	 
 GROUP BY ".$tnP.".thread_id ORDER BY ".$tnT.".sticky DESC, ".$tnT.".updated DESC";//ASC";

 

 

X)

Not that there relevant but i've been concocting some arrangements with these snippets (if it gives any ideas, but yes i'm using mysql):

GROUP_CONCAT( ".$tnP.".thread_id ORDER BY ".$tnP.".date_created DESC) ORDER BY ".$tnT.".sticky DESC, ".$tnT.".updated DESC";

SUBSTR(GROUP_CONCAT(ORDER BY ".$tnP.".date_created DESC)  FROM 1 FOR 1) 

SELECT CustomerID, FIRST(OrderType ORDER BY OrderID) GROUP BY CustomerID

 

 

[PFMaBiSmAd]

When you use GROUP BY GROUP BY ".$tnP.".thread_id that collapses all the same rows (with the same thread_id in this case) into a single row. All the values in that resultant row are whatever they are in the first row present. So, the update date that is used in the ORDER BY is the date in the first row present in the database for any thread_id.

 

I suspect you really want the thread_id to be in your ORDER BY clause and not a GROUP BY clause?

[tinker]

yes, but then i'd need to filter out the chaff and do the count using php...

 

p.s. I just tried putting thread_id in ORDER to no avail

 

What's needed is to order before group, then order again. It seems this is possible using GROUP_CONCAT but not for normal group?

 

 

PLOT, is that Posix Lot Of Tosh! Either way i'm losing it!