[SOLVED] Figure out which posts has been read and not read

[SkyRanger]

I am trying to figure out how to mark a post as new if a user has not read it yet. I am not exactly sure how to even start this:

 

All I know is I have to figure out how to do the function and cookie it to:

 

function viewNewPosts() {

}

 

Or would this be easier to do a session for it.

 

I need to figure out some how to do:

 

User logs in at: 2007-05-04 11:37

 

Post gets made:

 

office post id = 113 posted at: 2007-05-04 11:02

office post id = 114  posted at: 2007-05-04 12:01

 

Users reads post 113

 

User Refreshes and post 114 would be marked as new.

 

I am totally lost on this, any help would be greatly appreciated.

 

 

[clown[NOR]]

well.. even tho post 113 was written at 11:02 the user logged in at 11:37 and he hasn't read it yet.. so it should be marked as new for him until he has read it

[mpharo]

You would just query your dates, select * from posts where date > $lastlogon

[SkyRanger]

Yeah, but have to figure out how to make the unread ones stay unread until the user click on the link to read the message, then the new icon would disappear to show msg as read. lol, and yeah clown, I never even realized I typed it that way till you brought it up.

[clown[NOR]]

my bad

[Nameless12]

Left join the topics with the topic logs

[mpharo]

add another column in the table then just have a default value of 0 then when someone clicks on it update the table for that field to a 1...

 

0=unread

1=read

[SkyRanger]

Ok, I have tried a number of things, sort of got it to work, got the new to show based on users timestamp > $lastpost but can't figure out how to get it to disappear after user views the newest post .

 

The tables I have are

 

users

timestamp

 

office

oid

otitle (Original Post)

omsg

odate (original post time) echo date at: 2007-05-04 H:i (08:41)

 

office_posts (Reply Posts)

opid (reply post number)

oid (Show in office.oid)

otitle (re: otitle of office.otitle)

omsg (reply message)

odate (reply date of reply post echoed the same way as office.odate)

 

 

I am probably going to need to put in a #new anchor also, but still having some IE problems with that with IE 7 doesn't seem to like them for some reason, but this I will try and figure out in the HTML board.

 

 

mpharo how would I do that for all users though, if it changes 0 to 1 for one user that would echo the same for all of the following users.

 

 

 

 

 

 

 

[mpharo]

create new table called posts_read have the fields be username, postid, read

 

you then just create a relationship to your main posts table on the post id then another one to your personnel table, so when a person reads a post you update the posts_read table to show that that user read that post

 

$select=mysql_query("SELECT * FROM posts_read WHERE username='$username' AND postid='$postid' ") or die(mysql_error());

while($sql=mysql_fetch_array($select)){

If (!$sql[read]) {

echo "Not Read";

} Else {

echo "Post Read";

}

}

 

Then when someone clicks on a post:

 

$update=mysql_query("UPDATE posts_read SET username='$username', postid='$postid', read='1' ") or die(mysql_error());

 

something like that, the code is untested but you should get the idea...

[SkyRanger]

Ok, I have been working on what you posted and you totally lost me on:

 

you then just create a relationship to your main posts table on the post id then another one to your personnel table, so when a person reads a post you update the posts_read table to show that that user read that post

 

Sorry.

 

 

[dj-kenpo]

is this for a forum? or mail?

 

cookies could also work, and then you don't fill up a database with thousands of lines of backlog from month old posts....

 

just an idea. I'm going to go that route on a forum I'm building from scratch

[mpharo]

you just make the entries the same in those fields as in the other tables...so in your table that holds your usernames you use those same usernames in the new table and in the table that holds your postid you use those same ones as in your new table as well, when you make your insert to the new table just make sure the values are the same as in your other tables, this is called a one-to-one relationship between 2 tables

[SkyRanger]

ah ok.  That isn't too hard to do.  Just have to make sure to delete all of the inserts from posts_read when posts are deleted to save database space.

 

yeah dj-kenpo it is for a forum

[clown[NOR]]

but dj-kenpo... by using cookies to check all posts you will use way to much space on your computer by saving every single post you visit in a cookie.. and also.. when you delete all cookies every single post in the forum will be marked as unread again...

[SkyRanger]

Ok, I must be missing something, when a user posts a new post the oid = id of the post goes into the (mytable) office and I would also insert it into the posts_read.  How would I then be able to tell the script to look at both the office and posts_read tables to see if I had read it or not.  I would have to insert every username into the posts_read table when somebody posts a new thread and set everybody to 0 until it is read.

 

 

Can you tell I am totally confused...lol

[mpharo]

nah when a person creates a new thread you create it in your main thread table, then when someone views the post you insert it into post_read to say that it has been read, the 0 or 1 thing dosent really work after I thought about it.....so what your doing is making a thread then when someone tries to view it you check post_read to see if it is there or not, if not then it is marked as new if it is then you mark it as read...here is a breakdown...

 

 

1. new thread -> inserted into tbl_threads

2. person visits site

3. check to see if that user and that thread are present in post_read -> if not mark as new -> if is mark as read

4. after user clicks on thread if it was previously marked as new you insert post_read with username, postid

5. user revisites site and the thread they viewed is found in post_read and it is marked as read

 

I think that makes a little more sense...

 

you can then make it complicated and add a read value to post_read, where everytime someone makes a new post to an existing thread you do an update to post_read on the read field...when a person then views the site you check to see if the post they viewed has any new replies even after they have already read it....so it would then be marked as thread read, but new unread reply....

 

[SkyRanger]

ah, ok, now i get it. Thanks.

[SkyRanger]

Ok for some reason those codes are not working for me.

 

The pull nor update are working for me.

 

On my main forum page I have:

 

$postid = $row['oid'];

$select=mysql_query("SELECT * FROM posts_read WHERE username='$logged' AND postid='$postid' ") or die(mysql_error());

while($sql=mysql_fetch_array($select)){

If (!$sql[read]) {
echo "<img src=\"images/new.gif\">";
} Else {
echo "";
}
}

 

And on the viewpost page I have:

 

$update=mysql_query("UPDATE posts_read SET username='$logged', postid='$oid', postread='$rid'") or die(mysql_error());

 

Not sure exactly what the problem is.

 

I checked the first code against some old code I that I had, seems fine, so not sure what the problem is.

 

It should show new for all of the main posts because there is nothing currently in the posts_read table.

[mpharo]

do the following inside of your while loop :

 

print_r($sql);

 

post the results from that, also in your statement you have a different field....

 

(!$sql[read])

 

postread='$rid'

 

make the following changes:

 

If (!$sql[postread]) {

 

because I assume that is the name of your field...

[SkyRanger]

Stuck in the print_r($sql);

 

Got nothing to display, which is wierd.

 

Could it be possible because there is nothing in the posts_read table?

 

#start of new button

$postid = $row['oid'];

$select=mysql_query("SELECT * FROM posts_read WHERE username='$logged' AND postid='$postid' ") or die(mysql_error());

while($sql=mysql_fetch_array($select)){
print_r($sql);
if (!$sql[postread]) {

echo "Not Read";
} else {
echo "Post Read";
}
}

#end of new button

 

Had to change it to postread because php does did not like read

 

 

[mpharo]

if there is nothing in the table it will not return anything, which is how it should act, so if you are doing !$sql[postread] then it should be working as intended right?

[SkyRanger]

Yeah it should be, but for some reason it isn't....this is really wierd.

[mpharo]

so right now with that same code as you posted before you are getting a echo of "Post Read" ??

[mpharo]

try this....

 

#start of new button

$postid = $row['oid'];

$select=mysql_query("SELECT * FROM posts_read WHERE username='$logged' AND postid='$postid' ") or die(mysql_error());

$count=mysql_num_rows($select);

if ($count==0) {

echo "Not Read";

} else {

echo "Post Read";

}


#end of new button

[SkyRanger]

Woohoo, got that part working, thanks, not just have to figure out the INSERT ... ON DUPLICATE KEY UPDATE Syntax for it so it registers that it has been read.