1

Topic: 1 advanced query or a little the simple?

There are 3 tables:
users (id, type, online);
colors (id, name);
user_colors (user_id, color_id)
It will be necessary to receive sampling in which list of colors for users which online, and the type is equal to the set.
At me the algorithm about such turned out.
<? php
$result = SELECT * id FROM users WHERE type = $type AND online = true;
$result = SELECT * color_id FROM user_colors WHERE id = the array id which returns the first request
$result = SELECT * name FROM colors WHERE id = the array color_id which returns the second request
Whether it is possible it is all to make one request? An example please.
That it will be better from the point of view of productivity, such 3 requests or 1 big and terrible (if it is possible).
Correct errors in syntax, my variant all the same does not work.
The second request can/will return counterparts, whether it is possible to restrict it in request?

2

Re: 1 advanced query or a little the simple?

Whether

PavelRemm wrote:

It is possible it is all to make one request? An example please.

, somehow so approximately:

SELECT DISTINCT ` colors `. ` name`
FROM ` users`
JOIN ` user_colors ` ON ` users `. ` id ` = ` user_colors `. ` user_id`
JOIN ` colors ` ON ` colors `. ` id ` = ` user_colors `. ` color_id`
WHERE ` users `. ` type ` = $type AND ` users `. ` online ` = 1
PavelRemm wrote:

That will be better from the point of view of productivity, such 3 requests or 1 big

the Task is simple enough. How much I understand, on the client it is not provided any data handling between requests. In that case, I do not see sense to drive the data between the server and the client. Any explicitly notable difference in productivity at the specified structures of tables hardly find. Correctly it is necessary to put down indexes anyway.
PS: And  the list of colors irrespectively bindings to specific users is necessary simply? To "color" users the colors - the clear task, and to fall down colors of the active users in one heap - not quite.

3

Re: 1 advanced query or a little the simple?

' Colour ' - analogy for question simplification.
There are 2 types of users (Type 1, Type 2).
The user of type-1 at registration on a site selects some points from the list "Colour".
Example:
The list 1., 2., 3. 4.....
The user can select one or more variants.
There is a form for users of type-2 in which there is a tag <select>
It is necessary to fill it only with those ' Colours ' which users of type-1 at registration and which selected at present online on a site.
Here at me 3 labels also turned out.
The table user_colors - binding between two others.  communication (many to many).
I understood it so. If I am mistaken, please .

4

Re: 1 advanced query or a little the simple?

vkle wrote:

correctly it is necessary to put down Indexes anyway.

About indexes it is possible more in detail? I not absolutely understand.

5

Re: 1 advanced query or a little the simple?

Whether

PavelRemm wrote:

It is possible it is all to make one request?

........
That it will be better from the point of view of productivity, such 3 requests or 1 big and terrible (if it is possible).

Priorities not so are placed.
For debugging use three requests. It will be possible to look at the intermediate data.
After algorithm sanity check - unite in one.
Check up both variants for the speed.