[SOLVED] ORDER BY Help :)

[jackpf]

Good evening,

I was just wondering how to go about ordering a mysql query by the results from another query.

 

So say, in my forum, I wanted to order threads by the amount of posts they have. I don't actually have a column for "number of posts", I just work it out with a query, so

$sql = mysql_query("SELECT * FROM table WHERE `ID`='$threadid';");
$count = mysql_num_rows($sql);

 

But if I wanted to order the threads by the amount of posts it had, I'd have to do something like

//get count
$sql = mysql_query("SELECT * FROM table WHERE `ID`='$threadid';");
$count = mysql_num_rows($sql);
//real ting
$sql = mysql_query("SELECT * FROM table ORDER BY '$count';");

 

Obviously this doesn't work as it's completely incorrect, it's just there as a representation of what I'm trying to achieve. But I can't figure out how to do it.

 

So yeah, any pointers would be awesome.

Thanks,

Jack.

[Ken2k7]

Well you're ordering by the number $count, which is wrong. You want to order by the column. I don't know the column name for amount of posts, but you would want to order by that. You would need to use GROUP BY to group by amount of posts per thread.

 

Are the tables in your 2 sqls the same table?

[jackpf]

Yeah, that's what I mean though, there is no column for posts

That's why I'm having this problem - I need to work them out on the spot, in the query, and then order by the result.

 

Yeah, they're both in the same table.

 

I need to some how order by the amount of rows returned from a seperate query. Any ideas?

 

Cheers.

[Ken2k7]

What columns are in the table?

[jackpf]

CREATE TABLE IF NOT EXISTS `Forum` (
  `ID` int(5) NOT NULL AUTO_INCREMENT,
  `Type` varchar(30) NOT NULL,
  `Thread` int(11) NOT NULL,
  `Subject` varchar(75) NOT NULL,
  `Post` longtext NOT NULL,
  `Author` varbinary(50) NOT NULL,
  `Unix` int(11) NOT NULL,
  `Stats` int(11) NOT NULL,
  `Status` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=794 ;

 

[Ken2k7]

Is there a post table?

[jackpf]

No, both threads and posts are in this one table.

 

The column `Thread` holds the `ID` of the thread that the post relates to.

 

So for example, my query to display a thread:

"SELECT * FROM `$tb_Forum` WHERE `ID`='$threadid' OR `Thread`='$threadid' ORDER BY `ID` ASC"

[Ken2k7]

SELECT COUNT(`id`) AS `count`
FROM `Forum`
GROUP BY `thread`
ORDER BY `count`

[jackpf]

No...can't seem to get it to work.

[Ken2k7]

What kind of results is it returning? Maybe I misunderstood how your Forum table works.

[jackpf]

It seems as though the same result is being returned, but actual values aren't being fetched from the database.

 

It's like this:

"SELECT * FROM `$tb_Forum` WHERE `Type`='thread' ORDER BY IF(`Status`='sticky', 0, 1), `ID` DESC"//gets all threads, stickies at the top.

 

Then later on to get the number of posts I do this:

$csql = $mysql->query("SELECT COUNT(*) AS `count_rows` FROM `$tb_Forum` WHERE `ID`='$id' OR `Thread`='$id';") or $mysql->trigger_error();
$count = $mysql->fetch_array($csql);
$count = $count['count_rows'];

 

So you see, there is no field for the number of posts, I just count rows returned. But I can't figure out how to do this in a query. There must be  a way.

 

Cheers for your help though.

[Ken2k7]

Is your ID a PRIMARY_KEY?

[jackpf]

Yup.

[Ken2k7]

But then wouldn't this

SELECT COUNT(*) AS `count_rows` FROM `$tb_Forum` WHERE `ID`='$id' OR `Thread`='$id';

return one row at most?

[jackpf]

`ID`='$id'

This selects the thread, and ID is primary so will only select one.

 

 

OR `Thread`='$id'

This selects all of the posts for that thread.

[Ken2k7]

I am so confused.

[jackpf]

Same.

[fenway]

Yikes... let's start again.... why can't you simply count the number of posts per thread???????

[jackpf]

Lol yeah.

 

Basically, because I don't have a column with the number of posts. I just work it out by the number of rows returned from a query. So, for example:

$sql = mysql_query("SELECT * FROM table WHERE `ID`='$threadid';");
$count = mysql_num_rows($sql);

Will give me the number of posts for a certain thread.

 

But I don't know how to order by this result in another query. I guess it'd be something like:

$sql = mysql_query("SELECT * FROM table WHERE `Type`='thread' ORDER BY (SELECT COUNT(*) FROM `Forum` WHERE `Thread`=`ID` AND `Type`='post');");

But I can't figure out how to do it.

 

Yeah, cheers for the reply.

[kickstart]

Hi

 

The suggestion from Ken2K7 should pretty much do it. I cannot see why that idea wouldn't work:-

 

SELECT `thread`, COUNT(`id`) AS `PostCount`
FROM `Forum`
WHERE `Type`='post'
GROUP BY `thread`
ORDER BY `PostCount`

 

Only thing I can think of is that the row with the Id that other threads refer to is just a marker for the thread. Ie, the master row has a type which is not "post". Treating it as 2 tables joined (which may not be required) something like this

 

SELECT a.id as ThreadId, COUNT(b.id) AS PostCount
FROM Forum a 
LEFT OUTER JOIN Forum b ON a.id = b.thread
WHERE a.Type = 'thread'
AND b.Type = 'post'
GROUP BY ThreadId
ORDER BY PostCount

 

Also, in your table design it seems odd that Id is a Int(5) while Thread is an Int(11), when Thread cannot be larger than the largest Id.

 

All the best

 

Keith

[jackpf]

SELECT `thread`, COUNT(`id`) AS `PostCount`

FROM `Forum`

WHERE `Type`='post'

GROUP BY `thread`

ORDER BY `PostCount`

 

How would that work exactly? I'm not sure... (I can't test it right now as I'm at school) but isn't that selecting all posts rather than threads?

 

And joins...confusing

Never really used them before.

 

I'll give it a go later and post back here how it went.

 

Cheers

[jackpf]

Wait...I've got an idea; like this?

 

mysql_query("SELECT `ID` AS `post_subquery` FROM `Forum` WHERE `Type`='thread' ORDER BY (SELECT COUNT(*) AS `PostCount` FROM `Forum` WHERE `Thread`=`post_subquery` AND `Type`='post') `PostCount`;")

 

I have no idea if this works or not. Probably not.

[kickstart]

SELECT `thread`, COUNT(`id`) AS `PostCount`

FROM `Forum`

WHERE `Type`='post'

GROUP BY `thread`

ORDER BY `PostCount`

 

How would that work exactly? I'm not sure... (I can't test it right now as I'm at school) but isn't that selecting all posts rather than threads?

 

It has a group by. Imagine that the select is bringing back a row for every single record, with each row having 2 fields, the thread field and the id field. So if there were 2 threads, each with 3 posts:-

 

Thread, Id

,3

1,4

1,5

2,6

2,7

2,8

 

The group by and count means that of those returned rows it counts all the seperate Id fields for each thread:-

 

Thread, Count(id)

1,3

1,4

 

It should work unless there is something strange with the structure. If there is something strange then you might need to use a join of some kind.

 

Wait...I've got an idea; like this?

 

mysql_query("SELECT `ID` AS `post_subquery` FROM `Forum` WHERE `Type`='thread' ORDER BY (SELECT COUNT(*) AS `PostCount` FROM `Forum` WHERE `Thread`=`post_subquery` AND `Type`='post') `PostCount`;")

 

I have no idea if this works or not. Probably not.

 

That is a mishmash and not going to work in most flavours of SQL (MySQL seems to cope with it, if you remove the PostCount alias at the end of the sort). You are trying to use a subselect as the order by clause. You could try something like this:-

 

mysql_query("SELECT `ID` AS `post_subquery`, (SELECT COUNT(*) AS `PostCount` FROM `Forum` z WHERE z.Thread = a.ID AND z.Type='post') `PostCount` FROM `Forum` a WHERE a.Type='thread' ORDER BY PostCount;")

 

but it would not be an efficient way to do things, and isn't easy to read (also think that using a number.

 

Just set up a test table and both my earlier suggestions will work fine

 

All the best

 

Keith

[Ken2k7]

Did you mean this:

SELECT `thread`, COUNT(`id`) AS `PostCount`
FROM `Forum`
WHERE `Type`= 'thread'
GROUP BY `thread`
ORDER BY `PostCount`

?

 

I added the Type thread match. Though you should look into DB normalization after this. I guess I don't understand what Type is.

[jackpf]

Hmm...I guess I'm just being stupid about this then. I appreciate all the effort.

 

This is what I now have:

"SELECT `Thread`, COUNT(`ID`) AS `PostCount`, `Subject`, `Status`, `Stats`, `Author`
FROM `Forum`
WHERE `Type`='post'
GROUP BY `Thread`
ORDER BY `PostCount`"

 

Which gives this result:

 

 

[attachment deleted by admin]