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 "&lt;TABLE border=\"1\" width=\"300\"&gt;\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'], '&lt;a&gt;&lt;b&gt;&lt;i&gt;&lt;u&gt;'));                  /* display the data */         echo "&lt;TR&gt;&lt;TD&gt;&lt;b&gt;$title&lt;/b&gt; posted on $date&lt;/TD&gt;&lt;/TR&gt;\n";         echo "&lt;TR&gt;&lt;TD&gt;$news&lt;/TD&gt;&lt;/TR&gt;\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 "&lt;TR&gt;&lt;TD&gt;&lt;a href=\"{$_SERVER['PHP_SELF']}" .              "?action=show&amp;id={$row['id']}\"&gt;Comments&lt;/a&gt;" .              "($comment_row[0]}&lt;/TD&gt;&lt;/TR&gt;\n";                  /* finish up table*/         echo "&lt;/TABLE&gt;\n";         echo "&lt;BR&gt;\n";     }          /* if we aren't displaying all news,       * then give a link to do so */     if ($all == 0) {         echo "&lt;a href=\"{$_SERVER['PHP_SELF']}" .              "?action=all\"&gt;View all news&lt;/a&gt;\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.

Next: Displaying the comments >>
 

More Database Articles Articles
More By Matt Wade

Comments On: PHP/MySQL News with Comments

· dfsf    · Thanks for a great tutorial.    · hiI've search the web for ages to find a newssystem that does excatly what this...    · I seem to be getting the problem where it's not showing the news table at all and...    · sounds like it doesn't interact with your "news"-database as it...    · file loaded: http://luna-illusions.co.uk/weetom/newsscript.phpThere's no design...    · Hi.I just love this Tutorial and it works perfectly except for 1 thing. When you...    · Warning: mysql_connect() [function.mysql-connect]: Access denied for user...    · I've discovered a problem with this tutorial. The Comment can only be 500 Characters...    · Tutorial is nice, but previous one was better, this one is just final script cut...    · Hello Mr/Mrs Tutorial Creator (xD)I'm new toP and MySQL and I suck basically...   >>> Post your comment now!