using joins or multiple queries in php/mysql

Question

Here i need help with joins. I have two tables say articles and users. while displaying articles i need to display also the user info like username, etc. So will it be better if i just use joins to join the articles and user tables to fetch the user info while displaying articles like below.

SELECT a.*,u.username,u.id FROM articles a JOIN users u ON u.id=a.user_id

OR can this one in php. First i get the articles with below sql

SELECT * FROM articles

Then after i get the articles array i loop though it and get the user info inside each loop like below

SELECT username, id FROM users WHERE id='".$articles->user_id."';

Which is better can i have explanation on why too. Thank you for any reply or views

Answer 1

There is a third option. You could first get the articles:

 SELECT * FROM articles

Then get all the relevant user names in one go:

 SELECT id, username FROM users WHERE id IN (3, 7, 19, 34, ...)

This way you only have to hit the database twice instead of many times, but you don't get duplicated data. Having said that, it seems that you don't have that much duplicated data in your queries anyway so the first query would work fine too in this specific case.

I'd probably choose your first option in this specific case because of its simplicity, but if you need more information for each user then go with the third option. I'd probably not choose your second option as it is neither the fastest nor the simplest.

Answer 2

The first approach is better if applicable/possible:

SELECT a.*,u.username,u.id FROM articles a JOIN users u ON u.id=a.user_id

• You have to write less code
• There is no need to run multiple quries
• Using joins is ideal when possible

Answer 3

It depends how much data the queries are returning - if you'll be getting a lot of duplicate data (i.e. one user has written many articles) you are better off doing the queries separately.

If you don't have a lot of duplicated data, joins are always preferable as you only have to make one visit to the database server.

Answer 4

Get the articles with one query, then get each username once and not every time you display it (cache them in an array or whatever).