Re: how to lock a topic in a forum?
Posted: Fri Mar 02, 2012 1:35 pm
SELECT the sticky column, ORDER BY sticky DESC. As you're iterating over the returned rows, if $row['sticky'] == 1, then label it as sticky.
A community of PHP developers offering assistance, advice, discussion, and friendship.
http://forums.devnetwork.net/
Code: Select all
<?php
//index.php
include 'connect.php';
include 'header.php';
$sql = "SELECT
categories.categoryID,
categories.categoryName,
categories.categoryDescription,
COUNT(topics.topicID) AS topics
FROM
categories
//I tried putting ORDER BY sticky here
LEFT JOIN
topics
ON
topics.topicID = categories.categoryID
GROUP BY
categories.categoryName, categories.categoryDescription, categories.categoryID";
$result = mysql_query($sql);
if(!$result)
{
echo 'The categories could not be displayed, please try again later.';
}
else
{
if(mysql_num_rows($result) == 0)
{
//no categories have been made yet
echo 'No categories defined yet.';
}
else
{
//make the table
echo '<table border="1">
<tr>
<th>Category</th>
<th>Last topic</th>
</tr>';
while($row = mysql_fetch_assoc($result))
{
echo '<tr>';
echo '<td class="leftpart">';
echo '<h3><a href="category.php?id=' . $row['categoryID'] . '">' . $row['categoryName'] . '</a></h3>' . $row['categoryDescription'];
echo '</td>';
echo '<td class="rightpart">';
//fetch last topic for each category
$topicsql = "SELECT
topicID,
topicSubject,
topicDate,
topicCategory
FROM
topics
WHERE
topicCategory = " . $row['categoryID'] . "
ORDER BY
topicDate
DESC
LIMIT
1";
$topicsresult = mysql_query($topicsql);
if(!$topicsresult)
{
//error
echo 'Last topic could not be displayed.';
}
else
{
if(mysql_num_rows($topicsresult) == 0)
{
echo 'no topics';
}
else
{
while($topicrow = mysql_fetch_assoc($topicsresult))
echo '<a href="topic.php?id=' . $topicrow['topicID'] . '">' . $topicrow['topicSubject'] . '</a> at ' . date('d-m-Y', strtotime($topicrow['topicDate']));
}
}
echo '</td>';
echo '</tr>';
}
}
}
include 'footer.php';
?>
Code: Select all
if(!$result)
{
echo 'The categories could not be displayed, please try again later.';
}