Multiusers unidirectional support ticketing system

Question

I'm trying to do a very simple unidirectional ticketing/news system in php and mysql for distribute news on my application (like: "maintenance Friday 10pm-11pm"). Initially I tought that it can do with only a simple table:

   CREATE TABLE IF NOT EXISTS `message` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` varchar(18) NOT NULL,
  `subject` varchar(200) NOT NULL,
  `message` varchar(1000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `recipient` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `read` int(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

Admin create a new news and select recipients through a multiselect select.

<select multiple="multiple" name=[]dest >
   <option value="all">All users</option>
   <option value="1">User_1</option>
   <option value="2">User_2</option>
   <option value="n">User_n</option>
</select>

'dest' will be imploded and updated to 'message.recipient' User can view news:

SELECT * FROM message m WHERE m.recipient='all' or m.recipient RLIKE '[[:<:]]".$userID."[[:>:]]'"

My problem starts when user click on news and read it, because I update with '1' 'message.read' through jQuery Ajax, and unbold the respectively news, but... this is wrong, because when first user read the news it will mark as read for any other users.

I know I can create other table and storing userID and message.id for unread news and deleted as read it but this it's not quite elegant solution...

Any suggestions will be really appreciated.

Thank you! Leo

Answer 1

You can store "read" flags in session or cookie. This way each user will have his own flags.

Another thing:

<select multiple="multiple" name=[]dest >

should be

<select multiple="multiple" name="dest[]" >

if you want a correct array in PHP.