Display different objects with equal groups

Question

I tried it in various ways, got different results some good, others not so much. I would like to do something like store group/objects in Mysql and display on screen using PHP

I have this table

I would like to display a result similar to this(done in photoshop):

So far I have this code:

<?php
   include("connect.php");
    $consulta = "SELECT * FROM Grupos";
    $con = $mysqli->query($consulta) or die($mysqli->error);

    while($dado = $con->fetch_array()){
        
for($Objeto =1; $Objeto <= 3; $Objeto++){
 echo $dado["$Objeto"] . "<br>";
   }     

}
?>

But he displays it this way:

group 1 10

20

Grupo2 30

54

group 3 2039

4433

Answer 1

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);

Answer 2

Try this

<?php
include("connect.php");
$consulta = "SELECT * FROM Grupos";
$con = $mysqli->query($consulta) or die($mysqli->error);

foreach($con->fetch_array() as $linha) { 
    $grupo[] = $linha['Grupo'];
    $obj1[] = $linha['Objeto1'];
    $obj2[] = $linha['Objeto2'];
}

for ($i=0; $i<count($grupo);$i++) {
    echo $grupo[$i];
    echo $obj1[$i];
    echo $obj2[$i];
    echo "<br/>";
}
?>