unapproved vs unread messages

[tmatrix]

Hello,

I'm hoping that someone here could tell me how to tell the difference in the database between an unapproved message and an unread message. For example group messages and visitor messages both have the unread and unapproved messages. How can one tell from poking inside the database which is which.

Thank you incredibly much.
Cheers
Murias

[kh99]

In the visitormessage table, the state column is either 'visible', 'moderation', or 'deleted', so if it's 'moderation' then it hasn't been approved. There's also a 'messageread' column that's either 0 or 1.

It looks like groupmessage has the same state column, but for "has read" there's a groupread table that has a record for each user and group with the last time that user read that group, so I suppose you'd have to compare the message timestamp with the "readtime" for a given userid/groupid.

[tmatrix]

Wish to thank you greatly for this information. It has done an incredible amount in my understanding of the db and how vB works under the hood.

I'm working on getting certain notifications from vBulletin into another site, so users could be aware, then have a suitable link to follow.

I was looking at the groupmessage table, trying to link it to the social group's owner. Linking discussionid to the discussion table, which then provides a groupid. Linking to the socialgroup table where I can get the creatoruserid (moderator/owner of group). Yeah! But.. I saw in both of those tables (discussion and socialgroup) there is a column "modereration", along with visible and deleted. How are these columns used in the discussion and socialgroup tables. I attempted to do the math for some various groups, it does not add up.

Thank you again,
Cheers
Murias