There are 2 tables. The second depends on the first, that is, table1.id = table2.cat_id .

When I try to request one record of the first table and all the related records of the second table, I get confusion in the form:

 array('id'=>'1', ..., 'servers'=>'server1'); array('id'=>'1', ..., 'servers'=>'server2');

Instead

 array('id'=>'1', ..., 'servers'=>array([0]=>'server1', [1]=>'server2'));

That is, the output needs one array with information from the first table with a nested array containing data from the second table.

This problem is solved by analyzing all the arrays and creating a new array with the necessary content, which is very inconvenient.

Tell me, please, how to solve this problem.

Request itself

 SELECT c.id, c.name, c.class, s.name as servers FROM cat as c INNER JOIN servers as s ON s.cat_id = c.id WHERE c.id = '$id'
  • how you decide - and decide, in another way, because in relational databases, you cannot have an array as a field (and you want to get it). As an option - to do, for example, group_concat by s.name , and in the code to break this field into an array, but the option, let's say, is not very good - BOPOH
  • I think you should rebuild the application a little so that the first array is correct for it. - Naumov
  • @RAVON tried group_concat() , but this is also not the case. That is, as I understand it, you cannot resolve this issue in the request, right? - mix
  • @Naumov exactly how? - mix
  • no request, only in the loop; Now I will write another comment on how to do it, although the idea is still the same - BOPOH

1 answer 1

This problem is solved by analyzing all the arrays and creating a new array with the necessary content, which is very inconvenient.

It is impossible to completely abandon the post-processing of the request due to the tabular nature of the DBMS (both repositories and replies). You may find it more convenient to use an alternative approach, instead of INNER JOIN use LEFT JOIN , and form the name of the servers as a list, separated by commas using the GROUP_CONCAT() function.

 SELECT c.id, c.name, c.class, GROUP_CONCAT(s.name) as servers FROM cat as c LEFT JOIN servers as s ON s.cat_id = c.id WHERE c.id = '$id' GROUP BY c.id

However, the resulting list of servers servers will be just a string, in order to convert it into an array, additional processing is required. For example, you can use the PHP function explode()

 $result['servers'] = explode(',', $result['servers']);
  • Just do not forget that the default length of GROUP_CONCAT is 1024 characters. - LastDragon
  • Yes, there is such a thing, the size of 1024 is regulated by the directive group_concat_max_len, which can be dynamically changed by the way, i.e. it is not necessary to edit the configuration file my.cnf; just set the required size for the session SET @@ local.group_concat_max_len = 10000; - cheops