Menu
Hey everyone,
I have a small thread system I put together my self. I have user that log in and have a table that keeps information about the users (username, password, last time active, name etc.).
I would like to be able to have a bold text on all the threads that the user has not read the newest reply to. What is the best way to do this?
If some one could explain or point me in the right direction, it would be great!
Sincerely,
Magnus
<i>
</i>CREATE TABLE <span><code>forumreadlist</code></span> (
threadID int UNSIGNED NOT NULL,
userID int UNSIGNED NOT NULL,
lastread int(10) UNSIGNED NOT NULL,
FOREIGN KEY (<span><code>threadID</code></span>) REFERENCES <span><code>threads</code></span> (<span><code>id</code></span>) ON DELETE CASCADE,
FOREIGN KEY (<span><code>userID</code></span>) REFERENCES <span><code>users</code></span> (<span><code>id</code></span>) ON DELETE CASCADE,
KEY (threadID,userID)
);
<i>
</i>SELECT * FROM <span><code>threads</code></span> LEFT JOIN <span><code>forumreadlist</code></span> ON <span><code>userID</code></span> = <userviewingforumID>
[code=php]
// Connect to Database works perfectly with regular mysql functions
$sql = new PDO('mysql:host=localhost;dbname=forum','root','pass');
// userid
$user = 1;
$query = $sql->query('SELECT * FROM threads
LEFT JOIN forumreadlist
ON userID
= '.(int) $user)->fetchAll();
foreach($query as $thread) {
// This only works if your thread table contains a lastpost_time preferably UNIX_TIMESTAMP
if($thread['lastread'] > $thread['lastpost_time']) {
// Make me bold
}
}
[/code]
[code=php].(int) $user)->fetchAll();[/code]
<i>
</i>$forums_threads = mysql_query("select * from forums_threads LEFT JOIN forums_readlist ON userID = '$n[id]' order by lastpost desc LIMIT $start_from,10");
while($thread = mysql_fetch_array($forums_threads)) {
if($thread['lastread'] > $thread['lastpost']) { echo "<b>"; }
echo $thread['title'];
if($thread['lastread'] > $thread['lastpost']) { echo "</b>"; }
}
<i>
</i>mysql_query("delete from forums_readlist where threadID='$thread[id]' and userID='$user[id]'");
$date = date('Y-m-d H:i:s');
mysql_query("insert into forums_readlist values('$thread[id]','$user[id]','$date')");
<i>
</i>$dataexists = mysql_query("select * from forums_reads where userID='$user[id]' and threadID='$thread[id]'");
$dataexists = mysql_num_rows($dataexists);
if($dataexists == '1') { mysql_query("update"); } else { mysql_query("insert"); }
select * from forums_threads as t1
left join forums_readlist as t2 on t2.userID='1'
Yes I'm saying some of your code is wrong in reference to:
[code=php].(int) $user)->fetchAll();[/code]
I totally don't get what that is...
You're also assuming he is using PHP 5.1 and the PDO.[/QUOTE]
i was thinking about somthing i forgot..
You should maybe change the user id of when the person is not logged in into session id/hash instead if you can view the forum without logging in of course.[/QUOTE]
SELECT * FROM forums_threads LEFT JOIN forums_readlist ON userID='3'
<i>
</i>select * from forums_threads
LEFT JOIN forums_readlist
ON threadID=id
AND userID='3'
0.1.9 — BETA 5.15