Okay, here's what I've considered.
Let there be three tables:
users
userID int not null auto_increment,
primary key (userID),
userName varchar (20)
links
userID int not null,
alertID int not null
(no need for primary key)
alerts
alertID int not null auto_increment,
primary key (alertID),
alert varchar or text (the message)
recordCreatedDate datetime
The columns that I've described in each of the tables is just the bare minimum that you'd need to follow my logic. If you add additional columns such as a "passwords" column for the users table or an "agent_id" column for the alerts... it would make no difference in the logic flow that I'm going to be covering.
Since this is to be solved with PHP, there is no way to "push" an alert to a user. Instead, something has to be triggered in a script for the user to see an alert.
As an example of this trigger, a user comes to a login.php page and attempts to login. As part of the login.php script, the user's username/password is checked, of course, and a subsequent query is done of the links table. This is done in the script sample that I've posted below.
If the userID/alertID combination exists for a given userID and alertID, it means that yes, the user has been presented an alert matching links.alertID for a user identified by links.userID. If that's the case, you can take any number of alternate actions (e.g. continue on to some other page after having logged on, etc.). In the sample code, I've chosen to just redirect the user to another page.
If the combination does not exist in that table, it means the user has not seen the alert. So, we present it to him. In my sample script, I did it with an "echo" statement, but you can get more fancy with JavaScript pop-up windows if you wish.
I'm open to suggestions for improvement.
<?php
ob_start();
$db_name = "my_db";
$db_user = "db_admin";
$db_pwd = "secret";
$db_host = "localhost";
$connection_p = @mysql_pconnect($db_host, $db_user, $db_pwd)
or die( "<p>Couldn't connect to database: ".mysql_error()."</p>");
$db = @mysql_select_db($db_name, $connection_p )
or die("<p>Couldn't select database: ".mysql_error()."</p>");
/* you can use whatever flags you want for the intial query
such as a timetamp (e.g. all alerts before 13 June 2004). I've
user the "alertID" to keep things simple. */
$alertID = 4;
$userID = 2;
$sql = "SELECT l.userID
FROM links AS l
WHERE l.userID = '$userID'
AND l.alertID = '$alertID'";
$result = mysql_query($sql, $connection_p)
or die("<p>Could not select userID: ".mysql_error()."</p>");
$num = mysql_num_rows($result);
if ($num == 0) // user has not seen this alert
{
$sql_alert = "SELECT a.alert
FROM alerts AS a
WHERE a.alertID = '$alertID'";
$result_alert = mysql_query($sql_alert, $connection_p)
or die("<p>Could not select alert: ".mysql_error()."</p>");
$row_alert = mysql_fetch_array($result_alert);
// display alert to user:
ob_end_flush();
echo "<p>This is an alert: ".$row_alert['alert'];
/* now, part 2: insert userID and alertID into "links"
table to indicate that user will be seeing alert */
$sql_insert = "INSERT INTO links
VALUES
('$userID', '$alertID')";
$result_insert = mysql_query($sql_insert, $connection_p)
or die("<p>Could not insert into links table: ".mysql_error()."</p>");
}
else // redirect user to some other page if he has seen this alert:
{
header("Location: [url]http://www.php.net[/url]");
}
?>
