0

I'm creating a website with PHP and MySQL, and currently working on a page that deals with notifications (e.g. When someone comments on a post by you, you get a notification to that effect). The notification is sent not as an email, but as an alert in the website when you login to your account (Facebook style).

Now my problem is in determining when to mark a notification as read. In short: what is that event (such as a click, a view) that after it happens you can be sure the notification has been read, and you can change it to read?

Peter O.
  • 32,158
  • 14
  • 82
  • 96
gthuo
  • 2,376
  • 5
  • 23
  • 30
  • after user "mouseover" on the notification - this will also cover close/reply events – zb' Dec 06 '12 at 14:32
  • thanks eicto(and sorry for my late response). But what about users on phone(no "mouseover" feature) or malicious users who just turn off javascript so that they can see me suffer? – gthuo Dec 18 '12 at 08:51
  • but I already got a way around it. Let me just share may be it might help someone: – gthuo Dec 18 '12 at 08:52

1 Answers1

0

but I already got a way around it. Let me just share may be it might help someone: Every alert ought to direct the user to a certain URL e.g. if someone commented on your post, on clicking the alert you should be directed to e.g. "www.example.com/posts/?post_id=5". I cal this the target URL. when generating the alert, i store its target URL together with the alert details in the alerts table, and get the AI unique field(alert_id), which i will use to generate the href part of any link on the alert eg <a href="changetoread.php?alert_id=56">G Thuo</a> commented on your post. this is my approach: on clicking any alert, it first takes you somewhere where its status is changed to read(basing on the alert_id in the link). After it's changed to read, then we fetch the target URL from the table and redirect the user there. here's my code:

$alert_id=$_GET['alert_id'];
$user_id=$_SESSION['user_id'];
//create the SQL to update the read_status
$sql="UPDATE alerts_log SET read_status=1 WHERE alert_id=$alert_id AND user_id=$user_id";
$res=mysql_query($sql) or die(mysql_error());
//we can now extract the targer_url from the alerts table and redirect the user automatically to that URL
$sql="SELECT target_url FROM alerts WHERE alert_id=$alert_id";
$res=mysql_query($sql) or die(mysql_error());
$row=mysql_fetch_assoc($res);
$url=$row['target_url'];
//redirect the user to that URL
header("Location: $url");

`

gthuo
  • 2,376
  • 5
  • 23
  • 30
  • Your code is **VERY** prone to SQL injection. `changetoread.php?alert_id=0 OR 1=1 --` would change **ALL** the `alerts_log` rows to `read_status` 1. Consider using MySQLi or PDO with prepared statements: http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php – h2ooooooo Dec 18 '12 at 09:38
  • Huh I'm a bit scared. What if I only run the query after checking that `is_numeric($_GET['user_id'])` evaluates to true? – gthuo Dec 18 '12 at 09:49
  • In theory that'd work, but what about when you need a letter? Then you have the same problem again, without being able to do anything. If you don't want to switch to MySQLi or PDO, at **least** use the `mysql_real_escape_string` function on **any** variable you put into your SQL (`$sql = "UPDATE alerts_log SET read_status=1 WHERE alert_id=" . mysql_real_escape_string($alert_id) . " AND user_id=" . mysql_real_escape_string($user_id)`). Note, however, that MySQL is deprecated ([check the red box on top of all the `mysql` pages page](http://php.net/manual/en/function.mysql-connect.php)) – h2ooooooo Dec 18 '12 at 09:56
  • or just `$alert_id=$_GET['alert_id']*1` `$_SESSION['user_id']*1` – zb' Dec 18 '12 at 10:14
  • @eicto, what does the `*1` do? – gthuo Dec 18 '12 at 13:33