Jump to content

[SOLVED] ORDER BY Help :)


jackpf

Recommended Posts

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

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.

Link to comment
Share on other sites

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 ;

 

:D

Link to comment
Share on other sites

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"

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 :P

Never really used them before.

 

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

 

Cheers :)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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]

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.