Select attachment_id from one table and user_id in another

Question

I want to select 5 random image from phpbb_attachments and show author username name from phpbb_users.

phpbb_attachments:        phpbb_topics_posted:        phpbb_users:

attach_id  topic_id       user_id  topic_id           user_id  username
1          10             21       10                 21       Tom
2          5              53       5                  53       Maria
3          15             11       15                 11       John

$result = mysqli_query($con,"SELECT * FROM phpbb3_attachments WHERE mimetype = 'image/jpeg' ORDER BY RAND() LIMIT 5");

And what is your problem? – Gordon Linoff Jul 12 '14 at 15:55 — Gordon Linoff, Jul 12 '14 at 15:55
@GordonLinoff show image attachment and author name – Jul 12 '14 at 16:07 — , Jul 12 '14 at 16:07

score 0 · Accepted Answer · edited May 23 '17 at 11:50

You can try this:

SELECT att.attach_id,
       u.username
FROM phpbb_attachments att
JOIN phpbb_topics_posted t ON (t.topic_id=att.topic_id)
JOIN phpbb_users u ON (u.user_id=t.user_id)
WHERE att.mimetype='image/jpeg'
ORDER BY rand() LIMIT 5

As it says here, it is best to avoid ORDER BY RAND(). Look there for some alternatives.

Also, I only included the fields you showed, you should tweak it to include fields such as file location,etc.

Last, but not least, your example query references phpbb3_attachments, and the sample data uses a different name. You should adjust that accordingly.

Hope this helps.

Select attachment_id from one table and user_id in another

1 Answers1