ORDER BY

[Lamez]

in my query I have it to order by sticky, locked, datetime, and then id.

 

well if I have a topic that is sticky, and locked, and one that is just sticky and unlocked it puts the unlocked on bottom between the two.

 

This is what I wanted, however when it is just two regular topics, it order the locked one above the unlocked one. I want it to order like it would be if it was just datetime.

 

I am kinda lost, and I am pretty sure this topic did not make sense to anyone who reads it.

 

here is my query:

 

$sql="SELECT * FROM $tbl_name ORDER BY sticky DESC, locked DESC, datetime DESC, id DESC";

[haku]

I read that 3 times, but I really couldnt understand what you were asking. I'm kind of guessing at what you were getting at, but may be set locked to be ASC

[Yesideez]

I know what you're after and I think you're better off removing the order by locked from the query.

 

My guess is that you're writing your own forum so I'd stick to ordering by sticky, datetime, id

[marun]

1. Easiest way is probably make a few queries.

 

-> Select sticky topics ordered by ...

-> Select normal topics ordered by ...

 

--------------------

 

2. If table is something like:

 

create table Message(id integer auto_increment, sticky bool, locked bool, PRIMARY KEY(id));

 

To sort in order:

 

1. Sticky and locked.

2. Sticky and unlocked

3. Unsticky and unlocked

4. Unsticky and locked

 

SELECT * FROM Message ORDER BY sticky DESC, IF(sticky AND locked, true, false) DESC, IF(NOT STICKY AND LOCKED, true, false) ASC;

 

For data set:

 

+----+--------+--------+
| id | sticky | locked |
+----+--------+--------+
|  1 |      0 |      0 |
|  2 |      0 |      1 |
|  3 |      1 |      0 |
|  4 |      1 |      1 |
|  5 |      1 |      0 |
|  6 |      0 |      1 |
|  7 |      1 |      1 |
|  8 |      0 |      0 |
+----+--------+--------+

 

It will produce output:

+----+--------+--------+
| id | sticky | locked |
+----+--------+--------+
|  4 |      1 |      1 |
|  7 |      1 |      1 |
|  3 |      1 |      0 |
|  5 |      1 |      0 |
|  1 |      0 |      0 |
|  8 |      0 |      0 |
|  2 |      0 |      1 |
|  6 |      0 |      1 |
+----+--------+--------+

[Lamez]

that last idea could work.

 

but they way I can tell if a topic is sticky or empty is by putting a one in the the column

 

so

if sticky = 1

topic is sticky

 

same with the locked

 

structure:

CREATE TABLE `forum_question` (

`id` int(4) NOT NULL auto_increment,

`username` varchar(65) NOT NULL default '',

`topic` varchar(255) NOT NULL default '',

`detail` longtext NOT NULL,

`locked` int(1) NOT NULL default '0',

`sticky` int(1) NOT NULL default '0',

`datetime` varchar(25) NOT NULL default '',

`view` int(4) NOT NULL default '0',

`reply` int(4) NOT NULL default '0',

PRIMARY KEY (`id`)

) TYPE=MyISAM AUTO_INCREMENT=1 ;

 

CREATE TABLE `forum_answer` (

`question_id` int(4) NOT NULL default '0',

`a_id` int(4) NOT NULL default '0',

`username` varchar(65) NOT NULL default '',

`a_email` varchar(65) NOT NULL default '',

`a_answer` longtext NOT NULL,

`a_datetime` varchar(25) NOT NULL default '',

KEY `a_id` (`a_id`)

) TYPE=MyISAM;

 

 

could I still use that code if I changed it to 1's and 0's from trues and false?

[marun]

bool is alias for tinyint(1) in MySQL so I guess it will work.