Strange ORDER BY problem

atokatim

I have just found out that my forum I made for my site is not properly putting the Sticky topics at the top of the lists. I have tried several things, and have finally gotten the Sticky posts to stay at the top, but.... Now, the topics are not showing in order by last post date, they are showing in order from thread creation data. Here is my code :

//$tid = forum_topic id
mysql_query("SELECT ft.id FROM forum_threads AS ft, forum_posts AS fp WHERE fp.id = '$tid' AND ft.tid = '$tid' ORDER BY ft.sticky DESC, fp.time_stamp DESC LIMIT $start, $perpage") or die(mysql_error());

To better explain :

Table :
forum_threads
->id
->tid (Topic ID)
->mid (Member ID who created thread)
->subject (Title of the thread)
->time_stamp (Date the thread was created in unix timestamp format) INT(99)
->views (How many times the thread was viewed) INT(99)
->sticky (either a 1 - ON or 0 - OFF) TINYINT(1)

Table :
forum_posts
->id
->tid (Thread ID it is attached to)
->mid (Poster Member ID)
->time_stamp (UNIX timestamp when the post was created)
->message (Text for Post)
->notifyme (either a 1 - yes or 0 - no to send poster a message if someone posts in thread)

What would be the best way to get the sticky threads at the top of the results, and then threads with the newest posts directly after? Something is just not clicking for me.....

Bonesnap

For the forum I created, what I did was actually execute two queries when getting a list of threads. The first one retrieved the sticky threads (if any) and added them to an array (sorting them appropriately). The second retrieved the normal threads.

I then just looped through them with a foreach to display them.

Lars_Berg

select ft.tid,
       ft.subject,
       max(fp.timestamp) as lastPost 
  from forum_threads as ft
  join forum_posts as fp 
    on ft.tid = fp.tid
 where ft.tid = '$tid'
 group by ft.tid
 order by ft.sticky desc, 
       lastPost desc
 limit $start, $perpage

atokatim

The code above is still doing the same thing 🙁 This is really strange! Any other ways to do it in one query?

Bonesnap

Is there a particular reason why this must be done in a single query?

Derokorian

You could use two queries and a union to get it in one result set =D

johanafm

atokatim;10990387 wrote:
The code above is still doing the same thing

There are only three things I can think of that might break his query. The first is that he lacks ft.title in the group by clause. If MySQL is not in strict mode, such queries will execute, but you cannot rely on them retrieving the proper data. It is possible that this query actually will retrieve the data you are looking for, since ft.id is in the group by clause and there is a one-to-one relationship between ft.id and ft.title. But I wouldn't count on it, and there's no reason for not following the standard which states that in a query with a group by clause, every selected field must be part of the group by clause or an aggregate function (MAX() is an aggregate function).

The second reason would be if you've mixed differenet types of ids when joinining tables. For example, your threads have id and tid (topic id), while your posts have tid (thread id), yet you happily join topic id and thread id since they happen to have the same names...

The third reason would be if you have added columns which aren't part of the group by clause or an aggregate function (such as including substr(post.message) among the selected fields.

CREATE TABLE thread (
	id		INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	title	VARCHAR(30),
	sticky	BOOL,
	created	DATETIME
);

CREATE TABLE post (
	id			INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	thread_id	INT UNSIGNED,
	title		VARCHAR(10),
	created		DATETIME,
  FOREIGN KEY (thread_id) REFERENCES thread(id)
);

INSERT INTO thread (title, sticky, created) VALUES
('First', 0, '2011-10-01 14:00'),
('Second (sticky)', true, '2011-10-03 14:00'),
('Third (last posted to)', 0, '2011-10-10 14:00');

INSERT INTO post (thread_id, title, created) VALUES
(1, '(1) Oldest post', '2011-10-01 14:00'),
(1, '(1) Last post in thread', '2011-10-02 14:00'),
(2, '(2) First post', '2011-10-03 14:00'),
(2, '(2) Last post in thread', '2011-10-10 15:00'),
(3, '(3) First post', '2011-10-10 14:00'),
(3, '(3) Last post', '2011-10-11 16:00');

SELECT t.title, t.created, t.sticky, MAX(p.created)
FROM thread t
INNER JOIN post p ON p.thread_id = t.id
GROUP BY t.title, t.created, t.sticky
ORDER BY t.sticky DESC, p.created DESC;

output

[b]Thread title 	Sticky 	Thread created 		Last post created[/b]
Second (sticky)	1	2011-10-03 14:00:00	2011-10-10 15:00:00
Third (last posted to)	2011-10-10 14:00:00	2011-10-11 16:00:00
First			2011-10-01 14:00:00	2011-10-02 14:00:00

If you try adding post.title to the selected fields, you don't get data that makes any sense (MySQL non-strict mode), or the query won't execute at all (strict mode or other DBMS).

I had no topic id in my example, but the principles remain the same.

atokatim

There is no title field though. I am trying to sort by numeric variables in the database since searching by text seems to be slower. The time_stamp is an INT not text, the ID is an INT, and the sticky is an INT. Since they are all numbers, I figured the Query would be simple. But since I am trying to sort by 2 different tables, I think something is being lost.

johanafm

No, nothing is lost. My example is just that, an example. Looking closer at your table structure, you can alter my post table to hold message instead of title. The point is, you can take MAX(id) [int] or MAX(created) [DATETIME, or if you use unix timestamps, INT], but you cannot do an equivalent on message, which is why I brought it up.

Once again, looking at my example, the ordered data contains threads with id 2 (sticky = 1), 3 (sticky 0), 1 (sticky = 0), whereas the ordering of the latest post alone would be 3, 2, 1. As such, it clearly orders first by sticky and then by forum post time. In my example, had there been another sticky thread, the individual ordering among the sticky threads would also be done according to which had the latest created post.

My point is that you must be doing something which you should not do. Can you post SHOW create TABLE for the tables involved, the relevant thread and post data as well as the SQL query that doesn't do what you want?