Group result by column in response of AJAX PHP

Question

Group result by column in response of AJAX PHP

Navigation

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

1

I have the following Table in the database, I want to retrieve it to generate a report:

I'm recovering it with the following function statement in php:

#BUSCAR CAJA Y BOLSAS
     public function todoReporteModel($datos, $tabla){
        $stmt = Conexion::conectar() -> prepare("SELECT bolsa, caja FROM $tabla WHERE lote=:lotes");
        $stmt -> bindParam(":lotes", $datos["loteAll"], PDO::PARAM_INT);
        $stmt->execute();
        return  $stmt -> fetchAll();
        $stmt->close();
   }

The answer I get is the following:

My query is:

How can I order when I receive the answer, so that the values of the bags are grouped in each box, depending on the number of boxes? For example:

caja1=[1,2,3,40];
caja2=[89,78,968,125,963];
caja0=[10];

javascript jquery html php ajax

asked by Baker1562 22.02.2018 в 21:18

source

2 answers

1

If what you need is to add the total that each bag has to its box number, you can apply a GROUP BY .

SELECT SUM(bolsa), caja FROM info GROUP BY caja;

What it does is add up what each bag has grouped by its cash ID.

Here is an example: link

answered by 22.02.2018 в 22:26

error recorer string with for in r Selector this + option

score 1 · Accepted Answer

You can try the following to get arrays where the key will be the first column PDO::FETCH_GROUP of your SELECT the rest will be values as you normally use associative FETCH_ASSOC.

if ($stmt = $mysqli->prepare("SELECT caja , bolsa FROM cajas WHERE lote= :lotes")){
    $stmt->bindParam(":lotes", $datos["loteAll"], PDO::PARAM_INT);
    $stmt->execute();
    $total = $stmt->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC);
    print_r($total[0]); // obtenemos los de la caja 0
}

Another way would be to create an array and add the elements according to your password

$data = array();
if ($stmt = $mysqli->prepare("SELECT caja , bolsa FROM cajas WHERE lote= :lotes")){
    $stmt->bindParam(":lotes", $datos["loteAll"], PDO::PARAM_INT);
    $stmt->execute();
    while($row = $stmt->fetch(PDO::FETCH_ASSOC )){ 
         //Insertamos nuevo array en el Indice según la columna caja
         $data[$row['caja']][] = $row;
    }
    echo json_encode($data);

}

In your method Ajax in the success or done you can recover the data in the following way making use of Object.entries

$.ajax({

})
.done(function(respuesta ) {
    let result = Object.entries(respuesta );
    console.log(result[1]);//caja1
    console.log(result[0][1]);//Elementos de caja 1
})