[SOLVED] ordering a fetch array with specific row at the top

[AdRock]

Is there a way I can get a specific row of a database to be first in the list even when the rest of the list is ordered descending?

 

I am making a small message board and need the rules at the top of the list and the rest of the rows in descending order so newest first

 

The way i have it at the moment is to have 2 fetch arrays with the first getting the row with the rules in then the other with all messages but that doesn't work when i paginate the results

 

What i need to do is have my rules row at the top and everything displayed underneath

 

while ($row = $getrules->fetch()) {
	$datetime = change_date(strtotime($row['last_msg_date']));
	$topic = ucwords($row['topicname']);
	$topicname = strtolower(str_replace(' ','-',$row['topicname']));
	$topicid = $row['topicid'];

	echo "<div class='tdTopic td sticky'><p><a href='/forum/$getboardname/$topicname/'>{$topic}</a></p></div>";
	echo "<div class='tdAuthor td sticky'><p>{$row['tauthor']}</p></div>";
	echo "<div class='tdReplies td sticky'><p>{$row["msg_count"]}</p></div>";
	echo "<div class='tdReplies td sticky'><p>{$row["counter"]}</p></div>";
	echo" <div class='tdInfo td sticky'><p>{$datetime}</p><p>by {$row['last_msg_author']}</p></div>";
}

if($err !=0) {	
	while ($row = $result->fetch()) {
		$datetime = change_date(strtotime($row['last_msg_date']));
		$topic = ucwords($row['topicname']);
		$topicname = strtolower(str_replace(' ','-',$row['topicname']));
		$topicid = $row['topicid'];

		if($row['topicname'] != "Forum Rules And Guidelines") {	
			echo "<div class='tdTopic td'><p><a href='/forum/$getboardname/$topicname/'>{$topic}</a></p></div>";
			echo "<div class='tdAuthor td'><p>{$row['tauthor']}</p></div>";
			echo "<div class='tdReplies td'><p>{$row["msg_count"]}</p></div>";
while ($row = $getrules->fetch()) {
$datetime = change_date(strtotime($row['last_msg_date']));
$topic = ucwords($row['topicname']);
$topicname = strtolower(str_replace(' ','-',$row['topicname']));
$topicid = $row['topicid'];

echo "<div class='tdTopic td sticky'><p><a href='/forum/$getboardname/$topicname/'>{$topic}</a></p></div>";
echo "<div class='tdAuthor td sticky'><p>{$row['tauthor']}</p></div>";
echo "<div class='tdReplies td sticky'><p>{$row["msg_count"]}</p></div>";
echo "<div class='tdReplies td sticky'><p>{$row["counter"]}</p></div>";
echo" <div class='tdInfo td sticky'><p>{$datetime}</p><p>by {$row['last_msg_author']}</p></div>";
}

if($err !=0) {	
while ($row = $result->fetch()) {
	$datetime = change_date(strtotime($row['last_msg_date']));
	$topic = ucwords($row['topicname']);
	$topicname = strtolower(str_replace(' ','-',$row['topicname']));
	$topicid = $row['topicid'];

	if($row['topicname'] != "Forum Rules And Guidelines") {	
		echo "<div class='tdTopic td'><p><a href='/forum/$getboardname/$topicname/'>{$topic}</a></p></div>";
		echo "<div class='tdAuthor td'><p>{$row['tauthor']}</p></div>";
		echo "<div class='tdReplies td'><p>{$row["msg_count"]}</p></div>";
		echo "<div class='tdReplies td'><p>{$row["counter"]}</p></div>";
		echo" <div class='tdInfo td'><p>{$datetime}</p><p>by {$row['last_msg_author']}</p></div>";
	}
}
if($err > 1) pagination_one($total_pages,$page);				
}
else {
echo "<div class'message'><div class='tdFull td'>There are no topics at the moment.  Be the first to create a new topic.</div></div>";
}

 

[soak]

What does your SQL look like to select the rules and the normal data?

[AdRock]

$sql = "SELECT b.boardname
	 , b.boardid
	 , t.topicid
	 , t.topicname
	 , t.author AS tauthor
	 , t.counter
	 , t.locked as locked
	 , tm.msg_count
	 , tm.last_msg_date
	 , lm.author AS last_msg_author
  FROM boards AS b
INNER
  JOIN topics AS t
	ON t.boardid = b.boardid 
INNER
  JOIN ( SELECT topicid
			  , COUNT(*)-1 AS msg_count
			  , MAX(`date`) AS last_msg_date
		   FROM messages 
		 GROUP
			 BY topicid ) AS tm
	ON tm.topicid = t.topicid 
INNER
  JOIN messages AS lm
	ON lm.topicid = t.topicid
   AND lm.date = tm.last_msg_date
 WHERE b.boardid = '$boardid'
  ";

$count = "SELECT COUNT(*)
  FROM boards AS b
INNER
  JOIN topics AS t
	ON t.boardid = b.boardid 
INNER
  JOIN ( SELECT topicid
			  , COUNT(*)-1 AS msg_count
			  , MAX(`date`) AS last_msg_date
		   FROM messages 
		 GROUP
			 BY topicid ) AS tm
	ON tm.topicid = t.topicid 
INNER
  JOIN messages AS lm
	ON lm.topicid = t.topicid
   AND lm.date = tm.last_msg_date
 WHERE b.boardid = '$boardid'
  ";

$query = $sql." ORDER BY tm.last_msg_date DESC";
$rules = $sql." AND t.topicname = 'Forum Rules And Guidelines'";

 

[soak]

Not with that query you can't.

 

You need a flag in the topics table to indicate if it is a rule or not (TINYINT will do, 1 or 0, suggest you call it topic_type or similar and you leave room for expanding it later to add stickies etc) .

 

Then just ORDER BY topic_type, tm.last_msg_date DESC

 

Not sure offhand if you need ASC or DESC on the topic_type.

 

If you store last_post_id in the topics table it would be better to order by that than the last_msg_date (assuming you move any relevant indexes).

[AdRock]

Yeah...i did that.  I have an extra field called sticky and it's either 'y' or 'n'.

 

I've set the rules to 'y'

 

I didn't know if i could put it in another array of some sort becuase ordering by date would put the sticky at the bottom of the list whereas i need all stickies at the top

[soak]

Then just ORDER BY topic_type, tm.last_msg_date DESC

 

You can order by more than one thing at once. Order by sticky first and date second.

[sasa]

ORDER BY t.topicname = 'Forum Rules And Guidelines' DESC, tm.last_msg_date DESC

[soak]

Good call, didn't realise you could do that.

[AdRock]

Thanks chaps...that sorted it and easier than i thought