query: keeping a result at top

[robsgaming]

I've got a question about a discussion board script I've coded... it's been working great for months, but I'm adding in a few tweaks.... right now I'm using this query:

$query = "SELECT * FROM topics WHERE gameid = '$gameid' ORDER BY lastreplytime desc";

Simple enough... selects all columns from table topics where gameid=$gameid and sorts them by the last time one was replied to --- to keep it bumped to the top of course.

Ok, but now I've added in something new, as an admin, I want to be able to post a sticky topic.... a topic that only me the admin can post and it will stay at the top all the time. I have another field in the topics table named status --- if it is a sticky topic, status will be 'sticky' instead of 'open', 'locked', etc.

How can I run a query, or do something, that will check for anything with status = sticky first, then list the other results by lastreplytime as commanded in the query.

Thanks in advance for any help.

[batman]

Post all of your code so we can get a better look at how you have things set up.

[Mordecai]

I don't know if you'll be able to run through a query with this. I'd say put all the values into an array (something like $topic[id]["name"], ["poster"], ["status"], etc...), and check if $topic[id]["status"] == "sticky", if it is, print it, and move on. Something like this:

$query = "SELECT * FROM topics WHERE gameid = '$gameid' ORDER BY lastreplytime desc";
$result = mysql_query($query);
// Start getting MySQL info
while ($info = mysql_fetch_array($result,MYSQL_NUM)) {
  $topic[$info[0]]["name"] = $info[1];
  $topic[$info[0]]["status"] = $info[2];
  // All you need for now is name and status, but change the numbers accordingly
}
// Now we can loop through all of these and check status
// First loop, for stickies:
for ($i=0;$i<count($topic);$i++) {
  if ($topic[$i]["status"] == "sticky") {
    echo($topic[$i]["name"]);
    // You'll probably want a link here, and poster info, etc.
  }
  else {
    // Create a new array for non-stickies
    $nonstickies[]["name"] = $topic[$i][$name];
    // add other things than name here
  }
}
for ($i=0;$i<count($nonstickies);$i++) {
  echo($nonstickies[$i]["name"]);
  // Add a link, poster, etc.
}

Hope this helps. There might be a way to do this through the query, but I hope not (that was a lot of code I just wrote! 😛).

[robsgaming]

thanks for the replies so far....

would it be possible to manipulate the code i already have below:

while ($row = mysql_fetch_array ($query))
	{ 
		extract($row);
		if($status == "sticky")
			{
				// sticky
				$statusicon = "<img src=\"$baseurl/boards/images/topicsticky.gif\" alt=\"Topic is STICKY (will stay on top)\">";				
				$topicsubject = "<B>Sticky:</B>&nbsp;" . $topicsubject;
			}

	else if($status == "open")
		{
			if($msgcount >= 10)
				{
					$statusicon = "<img src=\"$baseurl/boards/images/topichot.gif\" alt=\"Topic is OPEN and HOT\">";
				}
				else
				{
					$statusicon = "<img src=\"$baseurl/boards/images/topicopen.gif\" alt=\"Topic is OPEN\">";
				}
		}
		else
		{
			$statusicon = "<img src=\"$baseurl/boards/images/topiclocked.gif\" alt=\"Topic is LOCKED\">";
		}


		echo	"
				<tr>
					<td bgcolor=\"$bdtb_04\" width=20>$statusicon</td>
					<td bgcolor=\"$bdtb_04\"><font class='text'><a href=\"showmessage.php?topicid=$topicid\">$topicsubject</a></font></td>
					<td bgcolor=\"$bdtb_04\" align='center'><font class='text'><a href=\"$baseurl/members/viewprofile.php?username=$topicstart\">$topicstart</a></td>
					<td bgcolor=\"$bdtb_04\" align='center'><font class='text'>$msgcount</td>
					<td bgcolor=\"$bdtb_04\" align='center'><font class='text'>$tviews</td>
					<td bgcolor=\"$bdtb_04\"><font class='textsmall'>$lastreplyuser @ $lastreplytime</td>
				</tr>
      			";
}

[Mordecai]

Yeah, look at how mine's set up. It's close, but then again, it's very different.

[batman]

Your best bet would be to run two query's. Run one to check for the status "sticky" then if there are any sticky ones echo them before the open ones. If there are not any sticky posts then just echo nothing. Then on the second query, check for all the open ones, and echo them below the sticky ones.

something like this:

$query = "SELECT * FROM topics WHERE gameid = '$gameid' AND status = 'sticky' ORDER BY lastreplytime desc";


while ($row = mysql_fetch_array ($query))
    { 
        extract($row);
        if($status == "sticky")
            {
                // sticky
                $statusicon = "<img src=\"$baseurl/boards/images/topicsticky.gif\" alt=\"Topic is STICKY (will stay on top)\">";                

                $topicsubject = "<B>Sticky:</B>&nbsp;" . $topicsubject;
            }

if($row > 0)
{
echo    "
                    <tr>
                        <td bgcolor=\"$bdtb_04\" width=20>$statusicon</td>
                        <td bgcolor=\"$bdtb_04\"><font class='text'><a href=\"showmessage.php?topicid=$topicid\">$topicsubject</a></font></td>
                        <td bgcolor=\"$bdtb_04\" align='center'><font class='text'><a href=\"$baseurl/members/viewprofile.php?username=$topicstart\">$topicstart</a></td>
                        <td bgcolor=\"$bdtb_04\" align='center'><font class='text'>$msgcount</td>
                        <td bgcolor=\"$bdtb_04\" align='center'><font class='text'>$tviews</td>
                        <td bgcolor=\"$bdtb_04\"><font class='textsmall'>$lastreplyuser @ $lastreplytime</td>
                    </tr>
                      ";

}
else
{
echo"";
}

Then for the second query do something of the sort:

$query2= "SELECT * FROM topics WHERE gameid = '$gameid' ORDER BY lastreplytime desc";


while ($row2= mysql_fetch_array ($query2)
    { 
        extract($row2;

    if($status == "open")
        {
            if($msgcount >= 10)
                {
                    $statusicon = "<img src=\"$baseurl/boards/images/topichot.gif\" alt=\"Topic is OPEN and HOT\">";
                }
                else
                {
                    $statusicon = "<img src=\"$baseurl/boards/images/topicopen.gif\" alt=\"Topic is OPEN\">";
                }
        }
        else
        {
            $statusicon = "<img src=\"$baseurl/boards/images/topiclocked.gif\" alt=\"Topic is LOCKED\">";
        }


        echo    "
                <tr>
                    <td bgcolor=\"$bdtb_04\" width=20>$statusicon</td>
                    <td bgcolor=\"$bdtb_04\"><font class='text'><a href=\"showmessage.php?topicid=$topicid\">$topicsubject</a></font></td>
                    <td bgcolor=\"$bdtb_04\" align='center'><font class='text'><a href=\"$baseurl/members/viewprofile.php?username=$topicstart\">$topicstart</a></td>
                    <td bgcolor=\"$bdtb_04\" align='center'><font class='text'>$msgcount</td>
                    <td bgcolor=\"$bdtb_04\" align='center'><font class='text'>$tviews</td>
                    <td bgcolor=\"$bdtb_04\"><font class='textsmall'>$lastreplyuser @ $lastreplytime</td>
                </tr>
                  ";
}

Just some stuff threw together.
It may or may not help,
-Blake

[robsgaming]

Thanks a ton for the suggestions. I just went with writing another query.

is an example of how it works. Thanks again for all the help.http://www.robsgaming.com/boards/viewtopics.php?gameid=b004