Group records in a table

Question

Group records in a table

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

I have the following query:

SELECT * FROM tabla1;

So it shows this:

Empresa | Empleado
-------- ---------
pepsi   | jose
-------- ---------
pepsi   | pedro
-------- ---------
pepsi   | juan

What I want you to show is this:

Empresa | Empleado
-------- ---------
pepsi   | jose

        | pedro

        | juan
-------- ---------

I tried this query:

SELECT * FROM tabla1 GROUP BY Empresa;

But only the first employee shows:

Empresa | Empleado
-------- ---------
pepsi   | jose

mysql sql phpmyadmin

asked by goku venz 12.06.2017 в 15:02

source

2 answers

Are there Skins, Themes, Templates, Templates, ... For controls in C #? [closed] Assign a value to an ASP Text Box using JavaScript

score 2 · Answer 1

SELECT Empresa, GROUP_CONCAT(Empleado)
FROM NOMBRE_TABLA
WHERE ID_EMPRESA = EMPRESA_NECESITAS 'Opcional,Solo si ocupas una empresa 
                                      especifica'
GROUP BY Empresa

When you have your new table or result, you separate it with a split in your code, using the character as a separator ( , ).

I hope and help you.

score 0 · Answer 2

In the following example, books are grouped using a field called ean . Use GROUP_CONCAT combined with GROUP BY , to get the data concatenated, but separated by any character, in this case use | ... Then, in PHP, by explode , I can read separately the different books belonging to each ean . In your case, ean would equal the company column.

The rest, it would be a matter of adapting the code.

I hope it serves you.

VIEW DEMO

Code:

<?php
require "util/public_db_info.php";
$pdo = new PDO($dsn, $user_name, $pass_word);
echo "<pre>";

/**
 *  Verificar si hay datos  
 *  Y  sacar el  contenido  usando foreach
 *  Nótese el uso de  count($arrDatos)  para contar el total de registros
*/



$sql = "SELECT id, ean, GROUP_CONCAT(title SEPARATOR '|') as libros FROM books WHERE ean=:ean1 OR ean=:ean2 GROUP BY ean;"; 
$stmt = $pdo->prepare($sql);
$stmt->bindValue(":ean1",4);
$stmt->bindValue(":ean2",1);


$stmt ->execute();
$arrDatos = $stmt->fetchAll(PDO::FETCH_ASSOC);

if ($arrDatos)
{
    echo "SE ENCONTRARON  ".count($arrDatos). " REGISTROS AGRUPADOS\n\n";
    print_r($arrDatos);    

    foreach ($arrDatos as $row)
    {
        echo "\n\nLIBROS CON  EL  EAN : ".$row["ean"]."\n\n"; 

        //Aquí  dividimos los datos usando el separador
        $arrLibros=explode("|",$row["libros"]);
        foreach ($arrLibros as $libro){
            echo $libro."\n";
        }
    }
}
else
{
    echo "No hay datos";
}

echo "</pre>";

$pdo = null;


?>

Result

SE ENCONTRARON  2 REGISTROS AGRUPADOS

Array
(
    [0] => Array
        (
            [id] => 400
            [ean] => 1
            [libros] => Tú eres el Camino|Cruzando el umbral de la esperanza
        )

    [1] => Array
        (
            [id] => 38053
            [ean] => 4
            [libros] => Hamlet|Romeo y Julieta|Lo que el viento se llevó|No hay amor más grande|El Principito|El Quijote
        )

)


LIBROS CON  EL  EAN : 1

Tú eres el Camino
Cruzando el umbral de la esperanza


LIBROS CON  EL  EAN : 4

Hamlet
Romeo y Julieta
Lo que el viento se llevó
No hay amor más grande
El Principito
El Quijote