Explode users id into array and get if chat post = unread?

[slyte33]

I've spent 3 hours trying to achieve this and decided to come for help

I have tried exploding to insert each ID then using if hasread!=in_array(players_id,hasread)..can't seem to get it working correctly.

I have a chatroom with tabs to change the room including user private messaging..

my chat table looks like this:

 

user_id

message_id

chatroom_id

message

time_sent

user_has_read

 

 

Let's say you're in the general chat..when sending a message it will insert as chatroom_id=1

private messaging chat_id's are mt_rand'd but all open rooms appear at the top of the chat.

 

Tabs at the top display chat rooms name and how many new chat posts since you last visited the page, I.E.

[General (0) *viewing now*] [Trade (2)] [slyte33(5)]

 

So basically, I need to update the "user_has_read" field which each player's ID that entered that chatroom where players_id is not in the field.

user_has_read could be displayed like this

(1)(5)(3)

(users with ID 1,5,3 entered chatroom, so all messages unread for those users now become read)

 

Any help would be greatly appreciated!

 

[jcbones]

   Why not just update each user as they enter the specific chat room.  There should be no need to explode anything. Just update the field when they enter. 

The only thing that scares me, is I think you are saying you want to save a CSV list to a column. This would mean that your database is not even close to being normalized, which would lead to collisions like you are having.  Depending on the modeling of your database, normalization could have many different forms, but I would think that you need a table to tie the user to the chat room.

Perhaps also tie the personal messages to a chatroom of the same ID as the user, maybe like userID+10000 (that way each is unique, and would not collision like mt_rand could do).

[slyte33]

But then I cannot update the database for each player that visit the chat room. I need it to update based on the players ID because if I don't it will update the database for every player instead :/

[jcbones]

I think you need a major update of your logic.

Updating the database for each player that enters the chat should happen when that single player enters the chat.  Which should be a single column update into a table that joins that single person to that specific chatroom. Maybe with a 5 minute cronjob that removes players that have had no chatroom interaction during the elapsed time. There should be NO CSV lists in any column in your database, because databases are NOT spreadsheets. Niether, should there be any duplicate data, as that leads to data collisions.

 

In order for your project to be efficient as possible, I suggest you studying up on normalization
 

[mac_gyver]

because you have a one to many relationship (each message can have many users that have not read it) you should not have the unread information stored with the message. you need a separate table to hold the message_id/user_id of the users who are in the corresponding chatroom, but have not read the message, one row for each message_id/user_id. when any user has read the message, simply remove his row from this separate table.

[slyte33]

I got it working using your method.

I made a new table "has_read_chat" and had it JOIN the table with message_id in table "chat".

It's working very nicely, thanks!