How to create SQL query with nested data?

Question

MySQL

Task: display the data of each yacht in a loop (Fig.). Problem with getting subarray of engines.

How to get an array of data using a sql query and include the engin subarray in it, i.e. get the following structure:

spoiler

Array
(
[0] => Array
(
[id] => 1
[name] => SL78
[width] => 5,74 м
[engin] => Array(
[0]=>2 x MTU10V2000M96 - 1523HP,
[1]=>2 x MTU10V2000M96 - 1523HP)
)
... и т.д.
)

MySQL database:

spoiler

CREATE TABLE `sl` (
  `id` int(11) NOT NULL,
  `order_yacht` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `img` varchar(255) NOT NULL,
  `length` varchar(100) NOT NULL,
  `width` varchar(100) NOT NULL,
  `draft` varchar(100) NOT NULL,
  `guests` varchar(100) NOT NULL,
  `command` varchar(100) NOT NULL,
  `fuel` varchar(100) NOT NULL,
  `max_speed` varchar(100) NOT NULL,
  `flag` tinyint(4) NOT NULL DEFAULT '0',
  `premiere` tinyint(4) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `sl` (`id`, `order_yacht`, `name`, `img`, `length`, `width`, `draft`, `guests`, `command`, `fuel`, `max_speed`, `flag`, `premiere`) VALUES
(1, 1, 'SL78', 'sl-1.jpg', '24,64 м', '5,74 м', '1,80 м', '8', '2', '5,350 л', '28 -30 узлов', 1, 0),
(2, 2, 'SL86', 'sl-2.jpg', '26,76 м', '6,35 м', '1,90 м', '8', '3', '8,100 л', '30 -32 узла ', 1, 0),
(3, 4, 'SL106', 'sl-3.jpg', '32,2 м', '7,05 м', '2,00 м', '8', '4/5', '8,300 л', '28 - 30 узлов', 0, 0),
(4, 5, 'SL118', 'sl-4.jpg', '36,50 м', '7,60 м', '2,25 м', '10', '5', '13,700 л', '27 узлов', 1, 0),
(5, 3, 'SL102', 'sl-5.jpg', '31,10 м', '7,10 м', '2 м', '-', '-', '12,100 л', '28 узлов', 1, 1);


CREATE TABLE `engin_sl` (
  `id_eng` int(11) NOT NULL,
  `engin_name` text NOT NULL,
  `yacht_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `engin_sl` (`id_eng`, `engin_name`, `yacht_id`) VALUES
(1, '2 x MTU10V2000M96 - 1523HP', 1),
(2, '2 x MTU10V2000M96L - 1622HP', 1),
(3, '2 x MTU 12V2000 M96 - 1822 HP ', 2),
(4, '2 x MTU 12V2000 M96L - 1947 HP', 2),
(5, '2 x MTU 16V2000M96 - 2434 HP ', 3),
(6, '2 x MTU 16V2000M96L - 2637 HP', 3),
(7, '2 x MTU 16V 2000 M96L ', 4),
(8, '2 x MTU 16V2000 M86 – 2216 HP', 5);

The problem is that I can't (don't know how) to add an array of engines to [engin], is it possible to do it using sql? If not, please advise how to solve this problem.

Answer 1

Mysql does not allow you to store tree structures.
Most likely here you will need to store the engines in a separate table and an auxiliary many-to-many relationship table, and then do something like this:
Select * from boats limit 10; - choose boats.
Then you collect these id boats in one line and
Select * from engine_to_boats as etb left join engine as e on etb.engine_id = e.id where etb.boat_id in 2,3,4,5,6,7,8,9;
And then, when you draw, you prescribe your engines there.
You can, of course, use an ajax-field with data on engines, but this is not good, as it leads to base denormalization and it is better to use plain text with the same success.