Member Avatar for 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

Edited by elamigosam
  • 2 Contributors
  • 1 Reply
  • 562 Views
  • 3 Weeks Discussion Span
  • Latest Post Latest Post by SeanDevoy

Dani AI

Generated

Quick, practical summary and fixes for the approach in this thread (refs: , ).

Keep the separate annComplete table — it’s the correct many‑to‑many mapping (many users can acknowledge many announcements). Do not move userId/annCompleteDatetime into the announcements table; that would only record a single user or the last user and breaks the model.

Better queries

  • Prefer a LEFT JOIN ... IS NULL or a NOT EXISTS check that filters by the current userId. These avoid the NULL pitfalls of NOT IN and let the database return only announcements the current user hasn’t acknowledged. Example (replace ? with a bound parameter):
SELECT a.annId, a.annTitle, a.annContent, a.annDatetime
FROM announcements a
LEFT JOIN annComplete ac ON ac.annId = a.annId AND ac.userId = ?
WHERE ac.annId IS NULL
ORDER BY a.annDatetime DESC;

Prepared statements in PHP

  • Use prepared statements (mysqli or PDO) and bind $userId to avoid injection and parsing overhead. Fetch only the columns you need (avoid SELECT *) and paginate/limit results if the list can grow.

Schema and performance tips

  • Make (userId, annId) a UNIQUE key (or primary key) on annComplete and add an index on those columns for fast lookups.
  • When inserting acknowledgements, use INSERT IGNORE/ON DUPLICATE KEY UPDATE (or handle duplicate errors) so clicks won’t create duplicates.
  • Add an active window (start/end datetimes or an active flag) on announcements to avoid scanning old items.
  • For very high traffic, consider caching per-user ack sets or using a job to precompute “unseen” counts.

These changes let the DB do the filtering correctly and efficiently while preserving correct behavior for multiple users.

Member Avatar for 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

Edited by SeanDevoy
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.