alert user of new forum post

[final60]

Hi

Im currently coding a simple php/mysql forum and am stuck on how to alert the user when a someone has replied to a post on the fourm.

 

Currently I have the following tables:

 

forum_posts

post_id pk

user_id

title

content

date

is_sticky

cat_id

 

forum_replies

reply_id pk

user_id

reply_date

reply_content

post_id

 

post_is_read

post_id

user_id

is_read (0/1)

 

SO far Ive thought that the best way to do it is when a new post is created a row is made in post_is_read for all users with is_read set to 0. Then a text can be displayed saying "new post". Then when the user views the post the row that is equal to their username, the post_id has the is_Read set to 1. Then when a new reply is made on the post it sets is_Read back to 0 for all users again.

 

But How do I first retrieve each user_id from users table and insert a new row in is_read table with post_id, user_id, is_read=0 for each user?

 

Is there a better way to do the whole thing ?

thanks for any help

[final60]

is this in the right direction? Ive not yet started using foreach loops but it seems the right kind of thing.

 

SO when a new post or reply is created something similar to this code is used

$users = mysql_query("SELECT user_id FROM urbex_users");
foreach($users as $each_user)
{
mysql_query("INSERT INTO urbex_forum_posts_is_read AS is_read (post_id,user_id,is_read)VALUES("$_GET[post_id]","$each_user","0")");
}

 

and when the user views the post it sets is_read back to 1

mysql_query("UPDATE urbex_forum_posts_is_read SET is_read='0' WHERE post_id='$_GET[post_id]' AND user_id='$_SESSION[urbex_user_id]'");

 

 

not sure if im barking up the right tree tbh

 

[Muddy_Funster]

is this in the right direction? Ive not yet started using foreach loops but it seems the right kind of thing.

 

SO when a new post or reply is created something similar to this code is used

$users = mysql_query("SELECT user_id FROM urbex_users");
foreach($users as $each_user)
{
mysql_query("INSERT INTO urbex_forum_posts_is_read AS is_read (post_id,user_id,is_read)VALUES("$_GET[post_id]","$each_user","0")");
}

no need to use a foreach, as a simple simple while loop would do. 

There really is absoloutly no need to alias the table name. 

And be carefull with your quotes.

$qry = "SELECT user_id FROM urbex_users";
$users = mysql_query($qry) or die("ERROR:<br>Unable to process query -- $qry<br>The following was returned by the server -- ".mysql_error());
while ($user = mysql_fetch_array($users){
$pid = $_GET[post_id];
$in_qry = "INSERT INTO urbex_forum_posts_is_read (post_id,user_id,is_read)VALUES('$pid','$user','0')";
mysql_query($in_qry) or die("ERROR:<br>Unable to process query -- $qry<br>The following was returned by the server -- ".mysql_error());
}

 

The "or die" statements are only there for design stage, and should not be used in a production setting.

 

[final60]

thanks that helped alot

 

would the foreach have worked just out of interest?

[Muddy_Funster]

It would have done the job, but while loops are generaly used with SQL queries - not sure if there is a real reason for this, but I preffer them because they are easier to follow (for me at least) the logic of and see what you are doing with them.