1) To get the result you expect purely with SQL, you can use UNION. However, this option does not work if you increase the number of objects. It responds to your need with 2 objects:
SELECT grupo, objeto1 FROM objetos
UNION
SELECT grupo, objeto2 FROM objetos
Proof: http://sqlfiddle.com/#! 9/f19f2/2/0
Upshot:
One option with JOIN, allowing infinite groups and objects.
2) RECOMMENDED: Option sorting directly by SQL
http://sqlfiddle.com/#! 9/bcbcb/30
SELECT g.grupo, o.objeto
FROM objetos o
LEFT JOIN grupos g ON g.id = o.grupoid
WHERE g.id IN (SELECT id FROM grupos ORDER BY grupo)
3) Option sorting by PHP
- not recommended, shown here only as an example
Structure of the Database (example): http://sqlfiddle.com/#! 9/bcbcb/14/0
QUERY SQL returns grouped by groups, but the PHP code sorts the way you asked the question.
ATTENTION: this code works since you always enter the same amount of objects per group.
include("connect.php");
$consulta = "SELECT g.grupo, objeto
FROM objetos o
LEFT JOIN grupos g ON g.id = o.grupoid";
$con = $mysqli->query($consulta) or die($mysqli->error);
$objetos = [];
$grupos = [];
$qtdObjetosGrupoKey = '';
foreach($con->fetch_array() as $linha) {
if(!in_array($linha['grupo'], $grupos)) {
$grupos[] = $linha['grupo'];
}
if(!isset($qtdObjetos)) {
$qtdObjetos = [$linha['grupo']] => 0;
$qtdObjetosGrupoKey = $linha['grupo'];
}
if(!isset($objetos[$linha['grupo']])) {
$objetos[$linha['grupo']] = [];
}
$objetos[$linha['grupo']][] = $linha['objeto'];
if(isset($qtdObjetos[$linha['grupo']])) {
$qtdObjetos[$linha['grupo']]++;
}
}
$itensPorGrupo = $qtdObjetos[$qtdObjetosGrupoKey];
$itemPos = 0;
for($i = 0; $i < $itensPorGrupo; $i++) {
foreach($grupos as $grupo) {
$resultado[] = [
'grupo' => $grupo,
'objeto' => $objetos[$grupo][$itemPos]
];
$itemPos++;
}
}
print_r($resultado);
This way it even worked, but every time I put one more object on the table, I have to repeat the UNION?
– Bruno Ferreira
Yes, but if you are going to expand the amount of objects it is better to have a table for groups and another for objects and do Joins. I passed the example of UNION if there were really only 2 objects, otherwise, depending on the volume of data, SELECT will be impractical.
– AlfredBaudisch
I am creating an example with JOINS and I update it here.
– AlfredBaudisch
I edited my question with the example with JOINS. It is much more complex in PHP, but you will have robustness and infinite growth, without adding new columns to the database.
– AlfredBaudisch
QUERY SQL returns grouped by groups, but the PHP code sorts the way you asked the question.
– AlfredBaudisch
I’ll take a look, thanks
– Bruno Ferreira
The SQL query and fiddle were wrong, fixed.
– AlfredBaudisch
I liked your question, because I found the way of ordering curious. Thus, I arrived at an infinitely more simplified form, I edited the answer. It would be with this SQL:
SELECT g.grupo, o.objeto
FROM objetos o
LEFT JOIN grupos g ON g.id = o.grupoid
WHERE g.id IN (SELECT id FROM grupos ORDER BY grupo)
– AlfredBaudisch