searls03 Posted March 23, 2011 Share Posted March 23, 2011 I am almost done with my website and I will use this thread to post small enhancements that would make my website better.........so #1, I have a code that will display news topics, how can I redo this code so that the topics are in reverse order and so that the title will be in bold lettering until it is read? <?php session_start(); // Must start session first thing /* Created By Adam Khoury @ www.flashbuilding.com -----------------------June 20, 2008----------------------- */ // Here we run a login check if (!isset($_SESSION['id'])) { echo 'Please <a href="login.php">log in</a> to access your account'; exit(); } //Connect to the database through our include include_once "connect_to_mysql.php"; // Place Session variable 'id' into local variable $userid = $_SESSION['id']; // Query member data from the database and ready it for display $sql = mysql_query("SELECT * FROM members WHERE userid='$userid' LIMIT 1"); while($row = mysql_fetch_array($sql)){ $name = $row["name"]; $phone = $row["phone"]; $username = $row["username"]; $address = $row["address"]; $city = $row["city"]; $state = $row["state"]; $zip = $row["zip"]; $cell = $row["cell"]; $email = $row["email"]; $accounttype = $row["accounttype"]; $rank = $row["rank"]; $badges = $row["badges"]; } ?> <?php include 'config1.php'; include 'opendb.php'; // if no id is specified, list the available articles if(!isset($_GET['id'])) { $self = $_SERVER['PHP_SELF']; $query = "SELECT id, title FROM news ORDER BY id"; $result = mysql_query($query) or die('Error : ' . mysql_error()); // create the article list $content = '<ol>'; while($row = mysql_fetch_array($result, MYSQL_NUM)) { list($id, $title) = $row; $content .= "<li><a href=\"$self?id=$id\">$title</a></li>\r\n"; } $content .= '</ol>'; $title = 'News'; } else { // get the article info from database $query = "SELECT title, content FROM news WHERE id=".$_GET['id']; $result = mysql_query($query) or die('Error : ' . mysql_error()); $row = mysql_fetch_array($result, MYSQL_ASSOC); $title = $row['title']; $content = $row['content']; } include 'closedb.php'; ?> <table width="600" border="0" align="center" cellpadding="10" cellspacing="1" bgcolor="#336699"> <tr> <td bgcolor="#FFFFFF"> <h1 align="center"><?php echo $title; ?></h1> <?php echo $content; // when displaying an article show a link // to see the article list if(isset($_GET['id'])) { ?> <p> </p> <p align="center"><a href="<?php echo $_SERVER['PHP_SELF']; ?>">Back to News</a></p> <?php } ?> so once this topic is resolved.......I will probably post another small topic to look at. Quote Link to comment Share on other sites More sharing options...
Psycho Posted March 23, 2011 Share Posted March 23, 2011 how can I redo this code so that the topics are in reverse order Use "ORDER BY" on whatever field you want them ordered by using "DESC" to make it in descending order the title will be in bold lettering until it is read? You are going to have to track what topics each user views. So, when a user selects an article, the page that displays the article will do a database insert to save a record identifying the user and the article. Then when pulling articles to display you would need to JOIN the articles table with the "viewed" table to determine which onse are read or not. Quote Link to comment Share on other sites More sharing options...
searls03 Posted March 23, 2011 Author Share Posted March 23, 2011 ok, so how would I do the second one? Quote Link to comment Share on other sites More sharing options...
Psycho Posted March 23, 2011 Share Posted March 23, 2011 ok, so how would I do the second one? I thought I already explained that. I'm not going to write the code for you as it would be too involved for a forum post and requires a knowledge of your database schema and architecture. But, here is a more verbose explanation: 1. Create a table to store viewed topics by users (e.g. "viewed_topics". You need two fields: topic_id and user_id. You also need to set both to unique. 2. On the page that displays a topic, in addition to running a SELECT query to get the topic data to display to the user, you need to run an INSERT query to update the "viewed_topics" table with the topic_id and user_id. However, you don't want duplicates, so you could use an ON DUPLICATE KEY UPDATE clause in the query. Example: $query = "INSERT INTO viewed_topics (`topic_id`, `user_id`) VALUES ($topic_id, $user_id) ON DUPLICATE KEY UPDATE `user_id`=`user_id`"; 3. When you pull the list of topics you will join the "viewed_topics" table so you can determine which ones the user has viewed. You would do this with a LEFT JOIN. But, as part of the JOIN condition you would only join those records belonging to the current user. I would put an IF condition in the SELECT statement to generate a dynamic value in the result set for viewed (true/false). You would do this by checking the user_id value from the joined records - if there was a matching record it will equal the user_id. Otherwise, that value would be NULL. Here is an example query $query = "SELECT *.topics, IF(`viewed_topics.user_id`=$user_id, 1, 0) as viewed FROM `topics` LEFT JOIN `viewed_topics` ON `topics.id` = `viewed_topics.topic_id` AND `viewed_topics.user_id` = $user_id"; Quote Link to comment Share on other sites More sharing options...
searls03 Posted March 24, 2011 Author Share Posted March 24, 2011 ok, thanks.........wait, how does it know whether to be bold or not and that is the first time I have seen LEFT JOIN, what is that? if you have where it is determining bold, could you quote it to show me? or is it that AS VIEWED in second code? Quote Link to comment Share on other sites More sharing options...
Psycho Posted March 24, 2011 Share Posted March 24, 2011 ok, thanks.........wait, how does it know whether to be bold or not and that is the first time I have seen LEFT JOIN, what is that? if you have where it is determining bold, could you quote it to show me? or is it that AS VIEWED in second code? I am almost done with my website and I will use this thread to post small enhancements that would make my website better Seriously?! Based upon your first post above I "thought" you actually did some coding on the site yourself. I can possibly understand the question about the LEFT JOIN, but you really don't have any idea how you would make the topics bold? Anyway, in a normal JOIN the records from the first table (the left) are joined to the 2nd table (the right) ONLY if there are records to join. That may not make sense as written so let's go over a couple examples. Let's say you have two tables: authors and books. Naturally, the books will be linked to the authors using an author ID. So, if you wanted a list of all the authors and their books you could run the following query SELECT * FROM authors JOIN books ON books.author_id = authors.id But, if there are any authros in the database which don't currently have any books associated with them they would be excluded from the result set because there were no records in the books table to JOIN on them. The solution is a LEFT JOIN which will join all the authors (LEFT) and the associated records from the books (RIGHT) table - but if there are no books to join on a respective author it will still be included in the result set, but the fields for the book data will be NULL. There are many differenent kinds of JOIN and there are plenty of tutorials out there that will do a better job of explaining them than I could. As for making the unread records bold I hope you know the basics on processing a result set from a DB query to display a list. In the while() loop to display the list of articles check the value of the "viewed" field inthe result set (which we dynamically created). If the value is 0 then the user hasn't read the article and you should make it bold. Rough example while($row = mysql_fetch_assoc($result)) { //Create the style for the article link $style = ($row['viewed']==0) ? 'font-weight:bold;' : ''; echo "<a href=\"displayArticle.php?id={$row['id']}\" style=\"{$style}\">$row['title']</a><br />\n"; } Quote Link to comment Share on other sites More sharing options...
searls03 Posted March 24, 2011 Author Share Posted March 24, 2011 Ok I got it now.....I thought bold was going to be some complicated method......nevermind I guess.....thanks.I will post back with questions I f I have em. Quote Link to comment Share on other sites More sharing options...
searls03 Posted March 24, 2011 Author Share Posted March 24, 2011 1 problem, the descending order thing is not working..............: Error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc' at line 1 Quote Link to comment Share on other sites More sharing options...
searls03 Posted March 24, 2011 Author Share Posted March 24, 2011 ok, I am not asking you to write this code for me, but I do need some help on what I need to change here or if I changed the wrong things..........I have two databases members and news............members is where userid comes from and news is where topic id comes from...............I will attatch screen shots of each database table...... while($row = mysql_fetch_assoc($result)) { //Create the style for the article link $style = ($row['viewed']==0) ? 'font-weight:bold;' : ''; echo "<a href=\"displayArticle.php?id={$row['id']}\" style=\"{$style}\">$row['title']</a><br />\n"; } [attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
searls03 Posted March 24, 2011 Author Share Posted March 24, 2011 I have order by working now Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.