Have Geek Brain

Using joins or multiple queries in php / mysql

This is where I need help with unions. I have two tables: articles and users. when showing articles I need to display also user information like username etc. This is better if I just use joins to join articles and custom tables to get user information when displaying articles, as shown below.

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

OR it could be in php. I first get articles with below sql

SELECT * FROM articles

Then after I get the array of articles i, write it down and get the user info inside each loop as shown below

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

Which is better, I can explain why too. Thanks for any answer or opinion

+2


a source to share


4 answers


There is a third option. You can get articles first:

 SELECT * FROM articles

Then get all the matching usernames in one go:



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

This way you only need to hit the database twice, not many times, but you won't get duplicate data. Having said that, it looks like you don't have a lot of duplicate data in your queries, so the first query will work just fine in this particular case as well.

I would choose my first option in this particular case because of its simplicity, but if you need more information for each user, skip to the third option. I probably would not choose the second option, as it is not the fastest and simplest.

+5


a source


It depends on how much data is returned, if you will receive a lot of duplicate data (for example, one user has written many articles), you are better off running the queries separately.



Unless you have a lot of duplicate data, joins are always preferable since you only need to visit the database server.

+3


a source


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 need to write less code
  • No need to run multiple queries
  • Use of connections is ideal if possible
+2


a source


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

0


a source






More articles:


All Articles