Group a set of measures in PHP

Question

I’m having a problem uniting a set of values.

I have a wood marking system. I feed the database with the length x width of the wood

Id, id_request, length, width, date

Registros:

1, 3, 320, 20, 05-25-2017

2, 3, 320, 25, 05-25-2017

3, 3, 310, 10, 05-25-2017

4, 3, 310, 20, 05-25-2017

5, 3, 190, 15, 05-25-2017

Now I need a routine in PHP or Mysql that joins the results not to appear twice the same length and this way it joins the measures to be shown next to this length.

Example of how it would look:

320-20 x 25

310 - 10 x 20

190 - 15

How could I do that?

Answer 1

Use the group_concat that concatenates a sequence of fields according to your defined query and group. group_concat is an aggregation function such as Count(), sum(), etc.

Mysqli

$servidor = "localhost";
$usuario = "USUARIO";
$senha = "SENHA";
$dbname = "NOME_DB";

$conn = mysqli_connect($servidor, $usuario, $senha, $dbname);
    $query = ('SELECT comprimento, group_concat(largura SEPARATOR " * ") AS lista, SUM(largura) as largura_sum FROM arquivos GROUP BY comprimento');
$result = mysqli_query($conn, $query);
while ($row = mysqli_fetch_assoc($result)){
    echo $row['comprimento']." - ";
    echo $row['lista']." Total: ";
    echo $row['largura_sum']."<br>";
}

PDO

$hostname="localhost";  
$username="USUARIO";  
$password="SENHA";  
$db = "NOME_DB";  
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);  
foreach($dbh->query('SELECT comprimento, group_concat(largura SEPARATOR " * ") AS lista, SUM(largura) as largura_sum FROM arquivos GROUP BY comprimento') as $row) {  

echo $row['comprimento'] . " - ";  
echo $row['lista']." Total: ";  
echo $row['largura_sum']."<br>"; 

} 

Generally, GROUP_CONCAT separates items with comma:

item1,item2,item3,item4

It is possible to define the separator to be used in this way:

GROUP_CONCAT(suacoluna SEPARATOR ' ')

Edited response to answer author’s comment asking for the sum of widths

Answer 2

You can concatenate the columns representing the dimensions and group the data:

Table:

create table test.pedidos
(
    id_pedido int auto_increment primary key,
    comprimento float null,
    largura float null,
    data date null
);

Some data:

INSERT INTO pedidos
  (comprimento, largura, data)
VALUES
  (320, 20, '2017-05-05'),
  (320, 25, '2017-05-05'),
  (310, 10, '2017-05-06'),
  (320, 25, '2017-05-06'),
  (320, 20, '2017-05-07'),
  (320, 20, '2017-05-08'),
  (300, 100, '2017-05-10')
;

And the consultation with the information you need:

SELECT
  concat(comprimento, '-', largura),
  count(1)
FROM pedidos
GROUP BY concat(comprimento, '-', largura);