finishing topics for my website

[searls03]

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.

[Psycho]

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.

[searls03]

ok, so how would I do the second one?

[Psycho]

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";

[searls03]

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?

[Psycho]

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";
}

[searls03]

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.

[searls03]

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

[searls03]

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]

[searls03]

I have order by working now