Acknowledged items table

[elamigosam]

I am working on a anouncement system.
the announcements are displayed everywhere in the website, every anouncement has a acknowledge button, basicaly to mark it as seen.

what i have:
Table: announcements: annId, annTitle, annContent, annDatetime
Table: annComplete: annCompletedId, annId, userId, annCompleteDatetime

I first pull all the announcements from the table
$result = mysqli_query($link, "SELECT * FROM announcements"); if (mysqli_num_rows($result) > 0) { while($row = mysqli_fetch_assoc($result)) {
durring the while portion I go to the annComplete:
$check = mysqli_query($link, "SELECT * FROM annComplete WHEREuserId= "$userId" ANDannId= '".$row['annId']."'"); if(mysqli_num_rows($check) == 0){
in here I check if there was an entry here. if there is, then I dont display the announcement (user marked it as read)

question, is there a better way of doing this?
I have to display the announcements that are not marked as read.

thanks you

[SeanDevoy]

Let the database do the work for you. Change your first select to only return the rows that are not read:

select * from announcements where annID not in (select annID from annComplete)

That is standard SQL, I am a Sql Server guy with limited MySQL use, but I think it is the same.
For query optimization, it would be better to do an outer join of the 2 tables and return only the rows that have no match in annComplete but that is more complex and likely different in MySQL.

You may want to concider putting the userID and CompleteDate fields in the first table (as NULL), then update them when "Complete." You can remove the annComplete table, simplify your query and speed up query time as well.

HTH Sean