PHP/MySQL News with Comments - Displaying the news
(Page 3 of 7 )
Like I said, the first priority is to display the news. The first thing we need to do is query the database for the news items. Then, simply display all the items we retrieved. For our purposes, I am displaying in a simple table. It isn’t pretty, but it gets the point across.
<?php function displayNews($all = 0) { /* bring in two variables * $db is our database connection * $max_items is the maximum number * of news items we want to display */ global $db, $max_items;
/* query for news items */ if ($all == 0) { /* this query is for up to $max_items */ $query = "SELECT id,title,newstext," . "DATE_FORMAT(postdate, '%Y-%m-%d') as date " . "FROM news ORDER BY postdate DESC LIMIT $max_items"; } else { /* this query will get all news */ $query = "SELECT id,title,newstext," . "DATE_FORMAT(postdate, '%Y-%m-%d') as date " . "FROM news ORDER BY postdate DESC"; } $result = mysql_query ($query); while ($row = mysql_fetch_assoc ($result)) { /* display news in a simple table */ echo "<TABLE border=\"1\" width=\"300\">\n";
/* place table row data in * easier to use variables. * Here we also make sure no * HTML tags, other than the * ones we want are displayed */ $date = $row['date']; $title = htmlentities ($row['title']); $news = nl2br (strip_tags ($row['newstext'], '<a><b><i><u>'));
/* display the data */ echo "<TR><TD><b>$title</b> posted on $date</TD></TR>\n"; echo "<TR><TD>$news</TD></TR>\n";
/* get number of comments */ $comment_query = "SELECT count(*) FROM news_comments " . "WHERE news_id={$row['id']}"; $comment_result = mysql_query ($comment_query); $comment_row = mysql_fetch_row($comment_result);
/* display number of comments with link */ echo "<TR><TD><a href=\"{$_SERVER['PHP_SELF']}" . "?action=show&id={$row['id']}\">Comments</a>" . "($comment_row[0]}</TD></TR>\n";
/* finish up table*/ echo "</TABLE>\n"; echo "<BR>\n"; }
/* if we aren't displaying all news, * then give a link to do so */ if ($all == 0) { echo "<a href=\"{$_SERVER['PHP_SELF']}" . "?action=all\">View all news</a>\n"; } } ?>
As you can see, we are going to offer up two options to the user viewing the news. The default option is to display up to $max_items worth of news. $max_items will be defined at the top of our script.
One thing to notice is that in the query, we order by the post date with ‘ORDER BY postdate’. Notice the DESC right after it. That stands for descending. By default, MySQL sorts darts in ascending order, or oldest to newest. Typically with news we would want the opposite, so we sort in descending order.
/ 28 