notifying user of replies

[runnerjp]

i cant really figure this one out...

 

i have set a colum in my table as email reply... if this is set to '1' then the users wants an email sent to them saying there is a reply..

 

now i thought i was going to use something like

 

if ($gettopic3['author'] == $username && $gettopic3['emailreply'] == 1) {echo 'the email is 1';}

 

but thats not right is it as its just searching to see if that user is replying to it... what would the if statement need to be i cant figure it out lol should be easy but i think im thinkin to complex

[bobbinsbro]

lol  i understood all the individual english words, but when i strung them all together i lost you completely.

i can't understand what the ***k you're trying to do 

some more code/punctuated sentences would be great 

[runnerjp]

sorry im finding it hard to explain... how about this..

 

i want to set it up on my forum that a user can have emails sent back to them saying that there thread has been replied too...

 

i have set up in my db a coloum saying emailreplys , this is set to 1 if a user wants to recieve emails..

 

im wanting to know how could i search the db to find out if users want to have an email sent and how would i send the emails to each user?

 

 

i has an idea above but it didnt pan out so might as well grab some ideas from scratch lol did that help

[Jack.C]

What forum software are you using? smf has this built in.

[runnerjp]

sorry im building my own forum... so its a start from scratch thing... not a complex project im just struggling with what query to use for the db

[GeorgeFive]

Make a forum_email table..... this has:

user_id

topic_id

mailed (enum y/n)

 

If a user wants an email when this thread is replied to, insert a row and set mailed to default = n.

 

Now, when someone makes a reply to this topic, do...

 

$query = "SELECT fe.*, u.email, u.name FROM forum_email as fe

LEFT JOIN users as u on u.user_id = fe.user_id

WHERE topic_id = '".$this_topic_id."' AND fe.user_id != '".$thepostinguser."' AND mailed='n'";

$res = @mysql_query($query,$link) or die ("sendNotification query fails :".mysql_error());

 

//nobody wants an email, stop now

if(count($user_id) == 0):

return;

endif;

 

foreach($user_id as $value) {

send emails

}

 

//set emailed to 'y' so that they don't get numerous emails... set it back to 'n' when they view this topic

$query_noted = "UPDATE forum_email SET mailed='y' WHERE topic_id = '".$this_topic_topic_id."'";

[runnerjp]

thnaks that sounds great ill use it in my code ...

 

still not sure what this means

 

//set emailed to 'y' so that they don't get numerous emails... set it back to 'n' when they view this topic

$query_noted = "UPDATE forum_email SET mailed='y' WHERE topic_id = '".$this_topic_topic_id."'";

 

and what about stopping them getting emails from there own replies..

[GeorgeFive]

Let's say you have an active topic that gets five replies before someone gets to view it again.... should they get five emails?  Nah... just one.

 

So, by default, the mailed field is 'n', meaning, they haven't been mailed yet.  When the topic gets a reply, they get a single email, and the mailed field is set to 'y' - meaning, they've been mailed, don't email them again (until they view the topic, which will set mailed back to 'n').

 

They won't get emails about their own replies, since we're checking for that in the query...

 

WHERE topic_id = '".$this_topic_id."' AND fe.user_id != '".$thepostinguser."' AND mailed='n'"

 

Translation: get all rows for...

1) this topic

2) anyone except for the currently posting user

3) hasn't been mailed yet

[runnerjp]

oh wow i hadnt thought about not sending lots and lots of emails as they havent viewed topic yet!!

 

sorry on a learning path here but i have never come across this kind of query before

 

$query = "SELECT fe.*, u.email, u.name FROM forum_email as fe

LEFT JOIN users as u on u.user_id = fe.user_id

WHERE topic_id = '".$this_topic_id."' AND fe.user_id != '".$thepostinguser."' AND mailed='n'";

$res = @mysql_query($query,$link) or die ("sendNotification query fails :".mysql_error());

 

could you help me out with a few blanks ...

 

fe.* u.email, u.name

u.email, u.name ... why u.email and not just email

fe LEFT JOIN users as u on -- no idea

 

could you clear them up for me lol

 

[GeorgeFive]

You are using MySQL, right?  I kinda assumed so, but if not, just change the query to meet your own demands.

 

If you are and you just don't know...

 

FROM forum_email as fe means "get stuff from this table and refer to it as fe"

 

fe.* means "get everything from the forum_email table"

 

u.email / u.name means "get the email and name fields from the users table."  If your users table has a "email" field and your forum_email table also has a "email" field, you'll get a conflict ("you said you wanted the email field, but, uh, which one?").  So, specify which "email" field you want.  In this example, you could drop the u., but I need them - I simplified my query for this example.

[runnerjp]

so bascily thats how to shorten the code for querys down...  and correct i do use mysql...

 

is there any where i can read up on this..

 

so let me get this right

 

"SELECT fe.* (what happens if you have one called fans events for example....would this not conflict?), u.email, u.name FROM forum_email as fe

LEFT JOIN users as u ( means from now on u.*somthing* means users table) on u.user_id = fe.user_id (so compare the userid on the users table to the userid on the forumemail table)

 

and if i where to echo anything out from this what would it be?

[GeorgeFive]

http://dev.mysql.com/doc/refman/5.0/en/join.html

That should help.

 

The fe in the example is how we named it in the query...

 

select fe.user_id from forum_email as fe

 

You could just as easily do...

 

select ilikecats.user_id from forum_email as ilikecats

 

Then you join users and refer to it as ilikedogs

 

inner join ilikecats.user_id on ilikedogs.user_id=ilikecats.user_id

 

This naming stuff just lasts for the life of the query... it doesn't affect tables and it doesn't conflict with tables.

 

The join... uh, let's see if I can explain this.  You're joining the users table with the forum_email table.... you've got a user_id in both.  So, match fe.user_id to u.user_id, and get the email address in the users table that matches this id.

[runnerjp]

ok found a problem here... if im creating a new topic... and the topic id is auto incrament then how am i supposed to ge tthe topic_id when posting it ?

[runnerjp]

ok here is what i have tried

 

<?php

if (isset($_POST['submit1'])) {
if ( ( time() - $userinfo['last_post'] ) >= 30 ) {
    $yourPost = mysql_real_escape_string($_POST['yourpost']);
    
    if (isset($_POST['yourpost']) && trim($_POST['yourpost']) == "") {
        $errMsgPost = "Error: You did not enter any text."; //no post entered
      } else {

        $insertpost = "INSERT INTO `forumtutorial_posts` (`author`,`forum`,`title`,`post`,`showtime`,`realtime`,`lastposter`,`parentid`) values('$username','$forum','$subject','$yourpost','$thedate','$thedate','$username','$forumpostid')";
        mysql_query($insertpost) or die("Could not insert post"); //insert post
        $updatepost = "UPDATE `forumtutorial_posts` SET `numreplies`=`numreplies`+'1', `lastposter`='$username', `lastrepliedto`='$thedate' WHERE `postid`='$forumpostid'";		
        mysql_query($updatepost) or die("Could not update post");
        $updatep = "UPDATE `users` SET `post_count`=`post_count`+'1', last_post=$thedate WHERE `Username`='$username'";
        mysql_query($updatep) or die("Could not update post");

	$query = "SELECT fe.*, u.email, u.name FROM forum_email as fe
LEFT JOIN users as u on u.user_id = fe.user_id
WHERE topic_id = '".$forumpostid."' AND fe.user_id != '".$id."' AND mailed='n'";
$res = @mysql_query($query,$link) or die ("sendNotification query fails :".mysql_error());

//nobody wants an email, stop now
if(count($id) == 0):
return;
endif;

foreach($id as $value) {
//define the receiver of the email
$to = $userinfo['email'];
//define the subject of the email
$subject = 'Test email for reply';
//define the message to be sent. Each line should be separated with \n
$message = "im sending an email too myself.";
//define the headers we want passed. Note that they are separated with \r\n
$headers = "From: admin@runningprofiles.com";
//send the email
$mail_sent = @mail( $to, $subject, $message, $headers );
//if the message is sent successfully print "Mail sent". Otherwise print "Mail failed" 
echo $mail_sent ? "Mail sent" : "Mail failed";
}

//set emailed to 'y' so that they don't get numerous emails... set it back to 'n' when they view this topic
$query_noted = "UPDATE forum_email SET mailed='y' WHERE topic_id = '".$forumtopicid."'";

       // header("Location: index.php?page=message&forum=$forum&id=$forumpostid&pagenum=last");



    }

}
}

?>	

 

but i sadly get "sendNotification query fails :" .... but no error from mysql_error()

[runnerjp]

what other things can i run to produce the error