I tried to simplify it as much as possible. There are four tables with data on fishing trips (two guides and two – about fishing and catch in each):

fish | river | expedition | expedition_catch
id name | id name | id river_id | expedition_id fish_id fish_count
1 Perch | 1 Oka | 1 1 | 1 1 5
2 Pike | 2 Enisey | 2 1 | 1 2 3
3 Chub | | 3 2 | 2 3 4
      | | | 3 1 6
      | | | 3 2 3
      | | | 3 1 4

In expedition – the number of the trip and the river, and in expedition_catch – information about what fish were caught and how many.

You need to display the data as follows: group all river trips and show how many fish of each species were caught for one river. In this form:

This is the output from this query:

SELECT r.name,
SUM (if (f.name = 'Perch', ec.fish_count, 0)) as 'Perch',
SUM (if (f.name = 'Pike', ec.fish_count, 0)) as 'Pike',
SUM (if (f.name = 'Chub', ec.fish_count, 0)) as 'Chub'
FROM expedition e
JOIN river r ON r.id = e.river_id
JOIN expedition_catch ec ON e.id = ec.expedition_id
JOIN fish f ON ec.fish_id = f.id
GROUP BY e.river_id

Disadvantage of this query: if you add another type of fish, chub, for example, the query will have to be redone. That is, it turns out stupid – you have to manually list the fish in the request. Tell me, is it possible to write a query using Mysql tools so that it itself “looks” all the fish and displays their sums in a string?

I understand that you can make it easier and get the data like this:

SELECT r.name, f.name, SUM (ec.fish_count) as fishcount
FROM expedition e
JOIN river r ON r.id = e.river_id
JOIN expedition_catch ec ON e.id = ec.expedition_id
JOIN fish f ON ec.fish_id = f.id
GROUP BY e.river_id, ec.fish_id

And then, using php to form the table. Still, I wonder if it’s possible to get the data in the right format right away.

Thank you!

Answer 1, authority 100%

Warning : never do this in real projects.

Hint here here (eng).

The bottom line is that with a purely query you cannot select a dynamic number of fields, but you can glue the query itself using mysql tools.

In total, your specific request will be composed like this:

SET @a = CONCAT (
  'SELECT r.name, \ n',
  (SELECT GROUP_CONCAT ('SUM (if (f.name = \' ', name,' \ ', ec.fish_count, 0)) as \' ', name,' \ '\ n') FROM fish),
  'FROM expedition e
  JOIN river r ON r.id = e.river_id
  JOIN expedition_catch ec ON e.id = ec.expedition_id
  JOIN fish f ON ec.fish_id = f.id
GROUP BY e.river_id; '
);
PREPARE stmt FROM @a;
EXECUTE @a;

The first request is to write the actual future request into a variable, the second is to “prepare” it, and the third is to execute it.

By the way, using PDO you could do this in general:

/ ** @var PDO $ db * /
$ stmt = $ db- & gt; prepare ('SELECT r.name, f.name, SUM (ec.fish_count) as fishcount
FROM expedition e
JOIN river r ON r.id = e.river_id
JOIN expedition_catch ec ON e.id = ec.expedition_id 
JOIN FISH F ON EC.FISH_ID = F.ID
Group by e.river_id, ec.fish_id ');
$ STMT- & gt; execute ();
$ data = $ STMT- & gt; fetchall (pdo :: fetch_group | pdo :: fetch_assoc);

And absolutely free Get an array of the form:

$ Data = [
   'Oka' = & gt; [
     ['name' = & gt; 'Okun', 'Fishcount' = & gt; 5],
     ['name' = & gt; 'Pike', 'FishCount' = & gt; 3],
     ['name' = & gt; 'Chavel', 'Fishcount' = & gt; 4]
   ],
   'Yenisei' = & gt; [
     ['name' = & gt; 'Okun', 'Fishcount' = & gt; ten],
     ['name' = & gt; 'Pike', 'FishCount' = & gt; 3],
   ]
];