Mysql Query from Query results

Question

I am making something like an announcement board that requires readers to acknowledge that they read it, and was wondering if there is a more efficient way of doing this.

I have 3 Tables on MySQL side:

    +-----------------+        +-----------------+        +-----------------+
    |  Announcements  |        | Acknowledgement |        |      User       |
    +-----------------+        +-----------------+        +-----------------+
    | announce_id     |        | ack_id          |        | user_id         |
    | announce_msg    |        | announce_id     |        | user_name       |
    | ...             |        | user_id         |        | ...             |
    +-----------------+        +-----------------+        +-----------------+

When a user "reads" the announcement (by clicking a button), Acknowledgment table will be inserted with the Announcement ID and User ID. When a second user "reads" the same announcement, Acknowledgement table will be inserted again with same Announcement ID and the second User ID and so on...

    +--------------------------------+
    |        Acknowledgement         |
    +--------+-------------+---------+
    | ack_id | announce_id | user_id |
    +--------+-------------+---------+
    |   1    |      1      |    1    |
    |   2    |      1      |    4    |
    |   3    |      1      |    3    |
    |   4    |      3      |    1    |
    |   5    |      3      |    6    |
    |   6    |      3      |    2    |
    +--------+-------------+---------+

Now to the problem. On the front end, when I list all the announcements on a page, I would have to first query for all the announcements. Then, for each announcement, I would have to do another query for all the users that have read this announcement.

 $sql = "select * from Announcements";
 $result = $pdo->query($sql);
 while ($row = $result->fetch())
 {
   $announce_id = $row['announce_id'];
   $announce_msg = $row['annouce_msg'];
   $readers = "";

   $sql2 = "select u.user_name from Acknowledgement as a INNER JOIN User as u where announcement_id =".$annouce_id;
   $result2 = $pdo->query($sql);
   while ($row2 = $result2->fetch())
   {
     $readers .= $row2['user_name'].", ";
   }

   echo "id:".$annouce_id.", message:".$announce_msg.", Readers:".$readers;
 }

So if there 10 announcements on the page, there will be 10 sub-queries for each announcement. What I have now does the job right now... but what if there is 1000 announcements? Then there will be 1000 sub-queries? Sounds like the database will be really hammered. So I'm hoping there is a better way of doing this.

Also, if 1000 people in the user table reads all 1000 announcements, the acknowledgement table will have 1000x1000 entries. seems like the acknowledgement table will become really really long. Will that be a problem as time goes by?

This is a really rough example of what I'm trying to do but it did take me a long time to write all this. If more details is needed let me know.

Is there a particular reason you need to know all users who have read a given announcement? Is it because you're trying to filter out some announcements? — Adrian
– Adrian, Commented Aug 22, 2012 at 1:51
no its just something to make sure everyone read the announcements. — Soujirou
– Soujirou, Commented Aug 22, 2012 at 2:01

Gordon Linoff · Accepted Answer · 2012-08-22 02:04:40Z

3

There is a better way. You can use a single query with group_concat:

select a.*, group_concat(u.user_name separator ', ') as AllUsers
from Announcements a join
     Acknowledgement ak
     on a.Announce_Id = ak.Announce_Id join
     User u
     on u.user_ID = ak.User_ID
group by a.announce_id

This uses the MySQL feature of hidden columns to group by only one column (announce_id) but still pull in a bunch of other columns with no aggregations (everything else pulled in by the "*").

answered Aug 22, 2012 at 2:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Soujirou Over a year ago

Thanks! This is exactly what I was looking for. Is there a way i could add more columns to group_concat? like group-concat(u.first_name u.last_name separator ', ')

Gordon Linoff Over a year ago

Yes, just say: group_concat(concat(u.first_name, ' ', u.last_name) separator ', '). You can also use group_concat multiple times in the query for different columns.

Soujirou Over a year ago

Now I have a new problem. there is a announcements.user_id field that I use to identify the creator of the announcement. I use to use inner join between announcement and users to pull the creator information but I don't know how to do it with this query.

Gordon Linoff Over a year ago

@user1583432 . . . To handle that, you put in another join into the query to the users table. You can then pull information about the creator, using a join directly from Announcements to User.

Adrian · Accepted Answer · 2012-08-22 02:42:20Z

2

If your purpose here is to filter out the announcements that your current user has read, you can do this an entirely different way. Instead of querying for every announcement, and then finding out all the users that have read those announcements and examining those results to find ones that your use has read and trimming them from the displayed list, you can just query in one go for everything a particular user (or list of users) have not yet read.

Change your query to this:

SELECT * FROM Announcements WHERE Announce_id NOT IN (SELECT ANNOUNCE_ID FROM Acknowledgement WHERE User_ID = <INSERT USER ID HERE>)

That should return all Announcement rows that this particular user has not yet acknowledged. If you change that final WHERE clause to be WHERE User_ID IN () then you can specify a list of user IDs.

EDIT: Given the comment you posted above, you could use this query to get all announcements that have been read by no one:

SELECT * FROM Announcements WHERE Announce_id NOT IN (SELECT ANNOUNCE_ID FROM Acknowledgement WHERE User_ID IN (SELECT User_ID FROM User))

The logic for putting together a query to find announcements that haven't been read by someone (if not everyone) is escaping me right this second.

EDIT THE SECOND: Every announcement, and everyone who has and has not read it, requires use of a different kind of join that you've used above, a FULL OUTER JOIN. Unfortunately MySQL doesn't have that feature IIRC, but it can be simulated with a union query

SELECT A.*, ACK.*, U.* FROM Announcements AS A 
INNER JOIN Acknowledgement AS ACK ON A.Announce_ID = ACK.Announce_ID 
LEFT OUTER JOIN User AS U ON ACK.User_ID = U.User_ID 
WHERE U.User_ID IS NOT NULL
UNION ALL
SELECT A.*, ACK.*, U.* FROM Announcements AS A
LEFT OUTER JOIN Acknowledgement AS ACK ON A.Announce_ID = ACK.Announce_ID
RIGHT OUTER JOIN User AS U ON ACK.User_ID = U.User_ID

I think that should do it. No facilities to test at the moment, of course.

edited Aug 22, 2012 at 2:42

answered Aug 22, 2012 at 2:01

Adrian

2,8751 gold badge25 silver badges33 bronze badges

2 Comments

Soujirou Over a year ago

Thanks for the fast reply, I think this filters out announcements that haven't been read? I want to list all the users that did read announcement tho (there isn't many users), so as I scroll though the announcements I can see who read and who didn't read each announcement.

Soujirou Over a year ago

haha not what I'm looking for in this problem but it does solve my other problem! thanks!

Collectives™ on Stack Overflow

Mysql Query from Query results

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related