I am trying to add a counter that displays the number of child records attached to a row. I'm doing this because I'm trying to make the interface a bit more like what gamers would use and get rid of a lot of extra stuff. Using the existing child-parent linking technically works, but it doesn't look like an interface that gamers would use to chat with each other.
There are a lot of tables and processes that go on to get to the point where i need help so I won't confuse/bore you with all those details.
The end result is that a database of video gamers can make friends with each other and then have one on one messaging to setup a play schedule. My goal is NOT to be a complete email/chat solution. I just need a simple way for them to chat and exchange details so they can then use something else for chatting like Discord. I need the built in chat ability because emails are not reliable enough due to spam blocking and such.
So - user goes to their page where they can see their "friends". On each row of this list page I have inserted a chat button that opens a pop-up. In this pop-up I'm pulling an external webpage where I have a nice chat interface built using a table called "Messages". All of this is working fine. If they open this pop-up all messages are marked as "read".
The part that I'm having trouble with is this - there is no way for a person looking at their friends page to know if there are any unread messages. They would have to click on each chat button and open the pop-up to see. So, I need a way to modify the chat button per row and add either a counter that shows how many unread messages in the message table for that friend OR at least make the button a different color... the row a different color... anything.
I know you can modify the menu and add counters on that, but I'm not sure how to modify a row based on a query that each row takes.
select all from messages where read = 1 AND friend_id = {rowID}
