Forum - ORDER BY `POSTS`

[jackpf]

Hi all,

I've made a forum, and I've decided to allow people to order threads by whatever, eg date, views, author etc.

 

Something like so...

	#order of threads
	if(!isset($_GET['order']))
	{
		$order = 'ID';
		$ascension = 'DESC';
	}
	else if(isset($_GET['order']))
	{
		$order = mysql_real_escape_string($_GET['order']);
		$ascension = 'ASC';
	}
#use
$sql = "SELECT * FROM `$tb` WHERE `Type`='thread' ORDER BY $order $ascension";

 

Anyway, I wanted to let people order by posts as well (as in the amount of replies the thread contains), but it currently works out the number of posts by simply counting the replies it's got; there isn't a dedicated column in my forum table.

 

I was just wondering if anyone has any suggestions on how to order by replies/posts without me having to make a dedicated column..? I've been wracking my brain and I can't figure it out...

 

Thanks for any help,

Jack.

[Mikedean]

You can order using a column you've defined in the query.

 

E.g.

 

SELECT count( posts ) as threadPosts FROM forum ORDER BY threadPosts ASC

 

So just check whether they want to order it by posts (like you've already done) and then the order in which it needs to display.

 

		if(!isset($_GET['order']))
	{
		$order = 'ID';
		$ascension = 'DESC';
	}
	else // No need for if else
	{
		$order = "threadPosts";
		$ascension = 'ASC';
	}

$sql = "SELECT count( posts ) as threadPosts FROM forum ORDER BY $order $ascension";

[jackpf]

Yeah, but...the thing is, I don't have a column for threadPosts. It simply works it out by counting all the replies from the table. So I can't actually order it by threadPosts because it doesn't exist...

 

Thanks for your help though.

 

Anyone at all..?

[xylex]

Do a subselect of the post count and order by that?

 

And mysql_real_escape_string() only secures you on quoted values, and you're not quoted in the order by clause there.  Use a whitelist of values instead.

[jackpf]

Sorry, how would I do that? My mysql isn't quite up to scratch...

 

And yeah, idk, I guess I'm just a bit ocd about escaping stuff.

 

Thanks though.

[Mikedean]

I think you've misunderstood me so I'll try to explain better.

 

In your current query to get the threads are you counting the amount of posts per thread? If yes, you then rename that virtual column and order by that.

 

So essentially

SELECT count( posts ) as threadPosts FROM forum ORDER BY threadPosts ASC

is

SELECT count( posts ) FROM forum ORDER BY count( posts ) ASC

 

You don't need to have the column in the table, you just need to add a temporary one on the fly.

 

If this still doesn't make any sense, please post your code for retrieving the post count and I'll take another look.