[SOLVED] Problem with SQL Syntax....

[xyn]

Hey,

I've been creating my own forum. It was going really well,

Until i realised I needed to set all threads with NEW replies,

to be shown above any recently posted threads. And I thought

making an SQL table, which stores the THREAD ID, and USER

when they have viewed the thread.

 

Well, i then had to make a syntax to paginate all my threads.

and then this is where my error occours...

 

My code:

$sql_threads = mysql_query("SELECT `subject`,`views`,`replies`,`closed`, 
		   CASE WHEN `thread_id` IN ($thread_id) THEN 1 
		   ELSE 2 END as `threadsort` 
		   FROM `bb_threads` 
		   WHERE `forum_id`='".$forum['forum_id']."' 
		   ORDER BY `threadsort`,`started_on` ASC 
	  	   LIMIT $limitvalue, $limit
	  	   ") or die("MySQL Error: ".mysql_error());

 

Mysql Error: picks up on line 2. which is

CASE WHEN `thread_id` IN ($thread_id) THEN 1

 

Does any one know How i could get around this error, using a simelar method.

Or is there a different way of knowing if a USER has viewed the topics with recent replies...

 

It's hard to express what i'm actually trying to do. sorry.

[unidox]

So you just trying to go page to page with pagination?

[xyn]

Nope, I have the pagination.

The problem is putting threads with NEW posts/replies, above all new threads.

 

Say someone posted a thread 2 years ago, and theres been 100's since, then

someone replies to that thread 2 years ago, i want that to be placed ABOVE

the threads which have been posted to the recent day (for example today)

 

And i can only do it, but getting the thread ID's by a certain user (in seperate table)

then imploding the thread ID's, and thats where my error is.

line 2

[unidox]

When selecting from a table use ORDER BY

[unidox]

Here:

 

SELECT * FROM table ORDER BY name DESC;

[xyn]

Nope, that's not what my queries about.

Because the CASE clause. is arranging what i need.

 

But i need to get my IMPLODED id's into that query and run them correctly.

 

which is why i use CASE.

 

because the ORDER BY will order the Topics with new replies first,

then everything else after it, and both are arranged in DATE order.

[biohazardep]

Make a column in your threads table that stores the last time a reply was posted for that thread and ORDER BY that column.

[xyn]

ok, i'll give that a bash.

thanks...

if it works, i'll announce the topic solved.

[xyn]

well it seems to be working...

Thanks for the logical answer (: