[SOLVED] Mysql Groups

[jackpf]

Hi all,

At the moment, for my forum, I have sticky threads which are gathered seperately from all of the other threads on display, so, for example:

 

$sql = mysql_query("SELECT * FROM table WHERE `Status`='sticky'");
while($fetch = mysql_fetch_array($sql))
{
//...display sticky threads
}
//and then
$sql = mysql_query("SELECT * FROM table WHERE `Status`!='sticky'");
while($fetch = mysql_fetch_array($sql))
{
//...display non-sticky threads
}

 

Now, I'm sure there must be a more efficient way of doing this, I'm just not sure how. I was thinking of using "GROUP BY `Status`" or "ORDER BY `Status`", however, the status could be anyting, thus shouldn't be grouped. For example, the status may be locked or private or whatever, in which case, they should be order by `ID`. Only sticky threads should be distinguished from the rest and displayed first.

 

So yeah, any help is greatly appreciated.

Thanks,

Jack.

[Daniel0]

I'd recommend storing the "stickiness" in a field called e.g. is_sticky (tinyint) instead for a couple of reasons:

 

1) It's faster to compare integers than strings.

2) Data integrity - it's easier to ensure that it's either 0 or it is not, than it is to ensure it's a specific string.

3) When fetching your data you could then easily sort descendingly by is_sticky, or whatever you choose to call it, which should solve your problem.

[jackpf]

Yeah...but the thing is, I try and use a fewer columns as possible. It just makes it easier to handle. Like, when I look at PHPBB or SMF's tables I just cringe at the amount of columns there are.

 

So there's no way of saying like... "ORDER BY `Status`='sticky', THEN `ID`" or something like that?

[Daniel0]

You can do ORDER BY IF(`Status` = 'sticky', 1, 0), but I would still recommend the former approach. Less table fields doesn't necessarily mean a better designed database. Now what if you want to lock it, or have another type of status? It would be completely sensible to have a topic which is both sticky and locked.

[jackpf]

Yeah...I agree. Atm you can't have a sticky & locked thread. I'll leave it as it is atm...change it when I have time. I am a bit OCD about the neatness of my code/tables though

 

But yeah...that code worked perfectly. It's now:

"SELECT * FROM `$tb_Forum` WHERE `Type`='thread' ORDER BY IF(`Status` = 'sticky', 0, 1), `ID` DESC"

 

Had to swap the 1 and the 0 around for the stickies not to be at the bottom.

 

How does it work, if you don't mind me asking? I understand the whole if thing, just not sure what the 1, 0 is for...

 

Thanks for your help,

Jack.

[Daniel0]

Had to swap the 1 and the 0 around for the stickies not to be at the bottom.

 

Yeah, sorry about that. My bad.

 

How does it work, if you don't mind me asking? I understand the whole if thing, just not sure what the 1, 0 is for...

Well, it's quite simple actually. You are by default always ascendingly, and because 0 comes before 1, the stickies come before the non-stickies.

[jackpf]

Ahh, makes sense.

 

Yeah, cheers for your help