[RESOLVED] Help with query please

[Dolemite50]

Hi,

I have a site where members will get various types of messages for things like new email or new friend invites. I use one table as a counter/notifier for new messages that looks like this:

tbl_message_counter
memberid -medium int
has_activity - tinyint
new_email - tinyint
new_friend_invite - tinyint
new_meeting_request - tinyint
...

When an event occurs such as a new email I set the has_activity field to one and add one to the new_email field. I use the has_activity so that I can do an easy query on pages to see if a member has ANY new messages.
The table actually has about 9 different categories, and for each category there is a seperate table holding the data. (tbl_email, tbl_friend_invite..) and so on.

My question is; how would I design a query that only queries the data tables if the corresponding value in tbl_message_counter is > 0?
I know I could just query the tbl_message_counter table and then hard-code it like:
if(new_email > 0){
SELECT FROM tbl_email....
if(new_friend_invite > 0){
SELECT FROM tbl_friend_invite....

..but I'm trying to work out a join to do it all in one fair swoop. Any suggestions?

Thanks, and btw; my version is shy of supporting subqueries. :bemused:

[roooss]

have you tried using a switch stement after the querie that would then call a function that would dynamically query the table with the new alerts in it? for example if you stored the table names in an array or something like that

[Dolemite50]

Thanks rooos,
I took your advice and created a function using a for_each loop and the mysql_field_name function which seems to be working great.
Thanks for pointing me in the right direction.