php - Notify user of new comments by email -

i have blog , want add function "notify me of new comments email" each post, when user comments post.

im working php , mysql.

i thinking send mail users has commented posts , has checked checkbox "notify me of new comments email" query like:

$query = mysql_query("select count(post_id) numberofcomments comments post_id = $post_id");  $row = mysql_fetch_assoc($query);  if($row['numberofcomments '] > $numberofcomments){     mail("someone@example.com","subject","message"); } 

but if deleted comment post , new comment has been added same number of comments , no mail sent. ideas on how query better?

it depends on when want notify user.

do want notified of every comment? functionality posts comment send notification. time comment posted, check users notified , notify them.

do want periodic (daily, weekly, etc.) notification there exist new comments? still don't want count. want in case check if there exist new records. there couple of ways go this:

1. based on identifiers. if know id of last comment user notified (which may need store somewhere in notifications table) check if there exist new ids above one. if so, there new comments.
2. based on date. if know timestamp of last comment user notified (which can store somewhere or calculate on fly based on period of notifications) check if there exist new comments since time. if so, there new comments.

as you've found, counting records isn't meaningful indication of you're looking for. record count tell how many records there are. if want know when records posted, need timestamp of kind. if want know if records match known records, need identifier of kind.

never assume information derived data doesn't hold information. store information need know.