Question:
Let's say I have 2 tables, one with phone numbers, and one with sectors:
Ex:. tb_extensions: id, extension, group_id(fk).
id | ramal | grupo_id
01 | 1234 | 01
02 | 2345 | 01
03 | 3456 | 02
04 | 3457 | 02
tb_groups: id, sector, manager.
id | setor | gestor
01 | setorA | Carlos
02 | setorB | Jose
Is there a way to get a sector-indexed associative array?
So that all data in the tables would be available in a single array and through a single query, being possible to iterate it as follows:
<?php
foreach ($resultado['setorA'] as $setorA) {
// código
}
foreach ($resultado['setorB'] as $setorB)
// código
}
Currently I can do this through two sql queries, assigning a clause where setor = 'setorX'
to each of them…
But I would like to know if there is a way to get to the same result, making just one query and returning an associative array indexed by the sector, and if, this would be a good practice with a high number of data, where it is necessary to consider, if it is interesting the volume of data in a single query, or if it is even more interesting, do this in 2 queries, dividing the data into 2 arrays.
Doubt is ref. pure mysql query, so no need to talk about PDO or other classes.
Answer:
I believe this solves your clustering issue, but the solution is with PDO and PDOStatement
<?php
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'root';
$password = 'senha';
$pdo = new PDO($dsn, $user, $password);
$sts = $pdo->prepare("SELECT b.setor, a.grupo_id, a.id, a.ramal, b.gestor
FROM tb_ramais a inner join tb_grupos b on a.grupo_id = b.id
ORDER BY b.setor");
$sts->execute();
$resultado = $sts->fetchAll(PDO::FETCH_NAMED | PDO::FETCH_GROUP);
print_r($resultado['setorA']);
echo '<br>';
print_r($resultado['setorB']);
echo '<br>';
//Setor A
Array
(
[0] => Array
(
[grupo_id] => 1
[id] => 2
[ramal] => 2345
[gestor] => Carlos
)
[1] => Array
(
[grupo_id] => 1
[id] => 1
[ramal] => 1234
[gestor] => Carlos
)
)
//Setor B
Array
(
[0] => Array
(
[grupo_id] => 2
[id] => 3
[ramal] => 3456
[gestor] => Jose
)
[1] => Array
(
[grupo_id] => 2
[id] => 4
[ramal] => 4567
[gestor] => Jose
)
)
Foreach
foreach ($resultado['setorA'] as $setorA) {
print_r($setorA);
print('<br>');
}
Array
(
[grupo_id] => 1
[id] => 2
[ramal] => 2345
[gestor] => Carlos
)
Array
(
[grupo_id] => 1
[id] => 1
[ramal] => 1234
[gestor] => Carlos
)
Regarding performance, I believe it will depend on factors, but you can easily use this in your projects.