Select sum of records by id with PHP, MySQL [closed]

Question

Select sum of records by id with PHP, MySQL [closed]

Navigation

#1 by (1 votes)

-3

I want to use a query to bring the sum of some records, but I want the records to be X element only.

What I want is to use a form with ajax to select a vehicle, which I have identified by id_vehicle, so that the query will return the sum of the services of that vehicle in specific.

This is the code:

include_once('conexion.php');
class Procesar extends Model{

    public function __construct(){ 
        parent::__construct(); 
    }

    public function build_report($year){
        $total = array();
        for($i=0; $i<12; $i++){
            $month = $i+1;
            $sql = $this->db->query("SELECT SUM(utilidad_servicio) AS total FROM servicios_vehiculos WHERE MONTH(fecha_servicio) = '$month' AND YEAR(fecha_servicio) = '$year' LIMIT 1");   
            $total[$i] = 0;
            foreach ($sql as $key){ $total[$i] = ($key['total'] == null)? 0 : $key['total']; }
        }            
        return $total;
    }

}

if($_POST['year']){
    $class = new Procesar;
    $run = $class->build_report($_POST['year']);
    exit(json_encode($run));
}

mysql php

asked by Juan Bautista 30.08.2018 в 03:11

source

1 answer

Error PHP line quotes [closed] My program does not work [closed]

score 1 · Answer 1

You should change your build_report function. Do not just add to WHERE the id_vehicle you want to search, if not removing the query from the loop and doing the prepared queries :

public function build_report($year){
    $total = array();
    $prepare = $this->db->prepare("SELECT
                                        SUM(utilidad_servicio) AS total
                                        , MONTH(fecha_servicio) AS mes
                                    FROM
                                        servicios_vehiculos
                                    WHERE
                                        YEAR(fecha_servicio) = ?
                                        AND id_vehiculo = ?
                                    GROUP BY
                                        MONTH(fecha_servicio)");  
    $prepare->bind_baram("ii", $year, $id_vehiculo); // Contando que el id_vehículo sea un int
    $prepare->execute();
    $prepare->bind_result($total, $mes);
    while ($prepare->fetch()){
        $total[$mes] = is_null($total) ? 0 : $total;
        // $total[$mes] = $total ?? 0; // en el caso de usar PHP 7+
    }
    return $total;
}

I have not tried the code but it should be almost like that.