php / mysql成员和标记功能

Question

I'm creating a messageboard for a team of volunteers. On this board people can write messages. These messages can be marked as read. Underneath each message a table with photos of the members is shown. The url of this photo is stored in a database. If a member clicks on his photo this photo changes to another image, so he know he has red the message. This function is working just fine.

But all these photo's are added manually into the script. This means that whenever a new volunteer joins our club, I have to change the code (add his name) in order to have him marking too.

What I need is that the volunteers are automatically connected to the table. So, if I add a volunteers name into the database (without editing the code), his photo will be displayed too. And whenever I delete a volunteer, his photo won't be displayed anymore.

I can achieve this by creating a database holding all volunteers names and create a mysql_fetch_array function right beneath the message. But when I do this and volunteer A clicks his photo, all photos of volunteer A changes, not just fot the message he just has read, also all the other message are marked too. I need that every message gets a unique row of photos that can be marked, so that the user knows he has marked that single message, not all of them.

How can this be achieved?

What I have right now:

The database db_users

id (AI), primary
name (varchar), 255
address (varchar), 255
email (varchar), 255
stillActive (varchar), 5 //if `value` is set to yes, his photo is displayed beneath a message. If `value` is set to no, his photo won't be visibile anymore.

The database db_messages

 id (AI), primary
 name (varchar), 255 //the name wo entered the message
 message (varchar), 999 //the message itself
 urlUserA // the url of the photo of volunteer A
 urlUserB // the url of the photo of volunteer B
 urlUserC // the url of the photo of volunteer C

The mark_read.php

$sql2 = "SELECT * FROM $tbl_name WHERE id = ".$_GET['id']; //to get the specific message
$result2 = mysql_query($sql2);
$url = $_GET['url']; // the url of the MARKED image             
$recover = $_GET['recover']; // the url of the UNMARKED image       
$tabel = $_GET['tabel'];
$id = $_GET['id'];
$date = $_GET['date'];
$row = mysql_fetch_assoc($result2);         
$tabel_content = $row[$tabel];

if ($tabel_content == $url){
    $sql = "UPDATE " . mysql_real_escape_string($tbl_name) .
       " SET ".$_GET['tabel']." = '".$_GET['recover'].
       "' WHERE id = ".$_GET['id'];
  $result = mysql_query($sql);
} 
elseif ($tabel_content == $recover) {
    $sql = "UPDATE " . mysql_real_escape_string($tbl_name) .
       " SET ".$_GET['tabel']." = '".$_GET['url'].
       "' WHERE id = ".$_GET['id'];
  $result = mysql_query($sql);
}

The if code above is used to see whether the user already marked the message or not. If it is marked and he'll click his photo again, the photo will be unmarked again.

As you can see, if I need to add a new volunteer, I have to add a table to the database, change the code of add_message.php. A lot of work. I need something that whenever I add a user into the db_user and save it, he'll be able to mark messages too, without having to create a new table urlUserD before he can use it.

Any help on this will be very much appreciated.

---UPDATE---

Okay, the message is added using this page:

addMSG.php

mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$subject = $_POST['subject'];
$date = $_POST['date'];
$message = $_POST['message'];
$name = $_POST['name']; // the name from the person who posted the msg

$sql="INSERT INTO $tbl_name(
date,
archive,
year, 
message, 
name,
subject
)
VALUES(
'$date',
'no',
'2014', 
'$message', 
'$name',
'$subject'
)";

$result = mysql_query($sql);

How (and where) do I add your code in order to bind all the users to the newly created message?

Answer 1

You have some database schema problems, that until you fix them, will continue to make your life difficult. Specifically you should have a table that relates users to the messages intended for them. So your schema might better look like this:

db_users

id (AI), primary
name (varchar), 255
address (varchar), 255
email (varchar), 255

db_messages

id (AI), primary
created_by, // user id who create the message. Don't use name here!
message (varchar), 999 //the message itself

db_message_queue

message_id // foreign key to db_messages.id  first column in compound primary key
user_id // foreign key to db_users.id  second column in compound primary key
message_read // tinyint field with 0/1 value to indicate whether message has been read

Now, when a message is created, you would add a row into the messages table, as well a rows in the message queue for all intended recipients. When the message in the queue is read by the intended recipient, you would mark it as read.