How to take a variable and declare it in each function with php?

Navigation
0

I have a variable defined $sql which has as value a statement sql the problem is that I need the same statement in different functions, but I do not want in each function to have to put $sql = "SELECT * FROM....." but directly access it. ..

CODE (I have cut the sentence since it is too long) 

public function CountEmpleadosRendimientoBajo_Sede_Competencia($id_empleado)
{
    switch ($area_empleado)
    {
        case '1':
            $where = 'tabla.resultado_lider <= 60';             
        break;

        case '2':
            $where = 'tabla.resultado_lider <= 25';             
        break;                 
    }       

    $sql = "SELECT COUNT(tabla.emple_cedula) AS rendimientobajo 
    FROM(
            SELECT 
              public.emple_empleados.emple_id
            FROM
              public.emple_empleados
                INNER JOIN public.udn_unidadnegocio ON (public.emple_empleados.emple_udn_id = public.udn_unidadnegocio.udn_id)
                INNER JOIN public.detalle_evaluacion ON (public.emple_empleados.emple_id = public.detalle_evaluacion.deteva_emple_id)
                INNER JOIN public.detalle_perfil_empleado ON (public.emple_empleados.emple_id = public.detalle_perfil_empleado.detalle_per_emple_id)
                INNER JOIN public.evaluacion ON (public.detalle_evaluacion.deteva_id = public.evaluacion.eva_deteva_id)
                INNER JOIN public.pre_preguntas ON (public.evaluacion.eva_pregunta_id = public.pre_preguntas.pregunta_id)
                INNER JOIN public.detalle_competencia_area ON (public.pre_preguntas.pre_compe_id = public.detalle_competencia_area.detarea_compe_id)
                    AND (public.detalle_perfil_empleado.detalle_per_id_area_empleado = public.detalle_competencia_area.detarea_id_area_empleado)
                INNER JOIN public.compe_competencia ON (public.detalle_competencia_area.detarea_compe_id = public.compe_competencia.compe_id)
                    AND (public.pre_preguntas.pre_compe_id = public.compe_competencia.compe_id)
                INNER JOIN public.per_periodo ON (public.detalle_evaluacion.deteva_per_id = public.per_periodo.per_id)
            WHERE
                public.emple_empleados.emple_id = '$id_empleado'              
             DESC
        ) tabla
            WHERE
              $where";  

    $stmt = $this->db->conn_id->prepare($sql);
    $stmt->execute();
    $resultado = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $resultado;
}

public function CountEmpleadosRendimientoMedio_Sede_Competencia($id_empleado)
{
    switch ($area_empleado)
    {
        case '1':
            $where = 'tabla.resultado_lider >= 61 AND tabla.resultado_lider <= 89';             
        break;

        case '2':
            $where = 'tabla.resultado_lider >= 25 AND tabla.resultado_lider <= 45';             
        break;                 
    }       

    $sql = "SELECT COUNT(tabla.emple_cedula) AS rendimientobajo 
    FROM(
            SELECT 
              public.emple_empleados.emple_id
            FROM
              public.emple_empleados
                INNER JOIN public.udn_unidadnegocio ON (public.emple_empleados.emple_udn_id = public.udn_unidadnegocio.udn_id)
                INNER JOIN public.detalle_evaluacion ON (public.emple_empleados.emple_id = public.detalle_evaluacion.deteva_emple_id)
                INNER JOIN public.detalle_perfil_empleado ON (public.emple_empleados.emple_id = public.detalle_perfil_empleado.detalle_per_emple_id)
                INNER JOIN public.evaluacion ON (public.detalle_evaluacion.deteva_id = public.evaluacion.eva_deteva_id)
                INNER JOIN public.pre_preguntas ON (public.evaluacion.eva_pregunta_id = public.pre_preguntas.pregunta_id)
                INNER JOIN public.detalle_competencia_area ON (public.pre_preguntas.pre_compe_id = public.detalle_competencia_area.detarea_compe_id)
                    AND (public.detalle_perfil_empleado.detalle_per_id_area_empleado = public.detalle_competencia_area.detarea_id_area_empleado)
                INNER JOIN public.compe_competencia ON (public.detalle_competencia_area.detarea_compe_id = public.compe_competencia.compe_id)
                    AND (public.pre_preguntas.pre_compe_id = public.compe_competencia.compe_id)
                INNER JOIN public.per_periodo ON (public.detalle_evaluacion.deteva_per_id = public.per_periodo.per_id)
            WHERE
                public.emple_empleados.emple_id = '$id_empleado'              
             DESC
        ) tabla
            WHERE
              $where";  

    $stmt = $this->db->conn_id->prepare($sql);
    $stmt->execute();
    $resultado = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $resultado;
}
    
asked by JDavid 26.04.2017 в 15:12
source

3 answers

2

And if you take it to a private function? You could pass as an argument the where defined above 

 private function getConsulta($where){
        $sql = "SElECT ... WHERE $where";
        return $sql;
    }

And you make the call in each function: 

$sql = $this->getConsulta($where);
    
answered by 26.04.2017 / 16:14
source
3

If the sql statement does not change, I propose the following solution.

Create in your functions php another parameter that receives the sentence sql ... 

public function CountEmpleadosRendimientoMedio_Sede_Competencia($id_empleado, $sql)
    {

// resto código...

}

At the beginning of the functions in your PHP page you declare a variable that contains the $ sql instruction, for example: 

$sentencia = "SELECT COUNT(tabla.emple_cedula) AS rendimientobajo 
    FROM(
            SELECT 
              public.emple_empleados.emple_id
            FROM
              public.emple_empleados
                INNER JOIN public.udn_unidadnegocio ON (public.emple_empleados.emple_udn_id = public.udn_unidadnegocio.udn_id)
                INNER JOIN public.detalle_evaluacion ON (public.emple_empleados.emple_id = public.detalle_evaluacion.deteva_emple_id)
                INNER JOIN public.detalle_perfil_empleado ON (public.emple_empleados.emple_id = public.detalle_perfil_empleado.detalle_per_emple_id)
                INNER JOIN public.evaluacion ON (public.detalle_evaluacion.deteva_id = public.evaluacion.eva_deteva_id)
                INNER JOIN public.pre_preguntas ON (public.evaluacion.eva_pregunta_id = public.pre_preguntas.pregunta_id)
                INNER JOIN public.detalle_competencia_area ON (public.pre_preguntas.pre_compe_id = public.detalle_competencia_area.detarea_compe_id)
                    AND (public.detalle_perfil_empleado.detalle_per_id_area_empleado = public.detalle_competencia_area.detarea_id_area_empleado)
                INNER JOIN public.compe_competencia ON (public.detalle_competencia_area.detarea_compe_id = public.compe_competencia.compe_id)
                    AND (public.pre_preguntas.pre_compe_id = public.compe_competencia.compe_id)
                INNER JOIN public.per_periodo ON (public.detalle_evaluacion.deteva_per_id = public.per_periodo.per_id)
            WHERE
                public.emple_empleados.emple_id = '$id_empleado'              
             DESC
        ) tabla
            WHERE
              $where";

Then in your PHP functions you should refer to the second parameter of said sql statement.

For example, it would be the same for the function CountEmpleadosRendimientoBajo_Sede_Competencia : 

public function CountEmpleadosRendimientoBajo_Sede_Competencia($id_empleado, $sql)
{
    switch ($area_empleado)
    {
        case '1':
            $where = 'tabla.resultado_lider <= 60';             
        break;

        case '2':
            $where = 'tabla.resultado_lider <= 25';             
        break;                 
    }

    $stmt = $this->db->conn_id->prepare($sql);
    $stmt->execute();
    $resultado = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $resultado;
}

And so for the other function CountEmpleadosRendimientoMedio_Sede_Competencia 

public function CountEmpleadosRendimientoMedio_Sede_Competencia($id_empleado, $sql)
{
    switch ($area_empleado)
    {
        case '1':
            $where = 'tabla.resultado_lider >= 61 AND tabla.resultado_lider <= 89';             
        break;

        case '2':
            $where = 'tabla.resultado_lider >= 25 AND tabla.resultado_lider <= 45';             
        break;                 
    }

    $stmt = $this->db->conn_id->prepare($sql);
    $stmt->execute();
    $resultado = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $resultado;
}

EXAMPLE 

$sentencia = "SELECT ...";
CountEmpleadosRendimientoMedio_Sede_Competencia("el_valor_que_sea", $sentencia);
    
answered by 26.04.2017 в 15:30
0

Let it be clear: it is bad practice to do the following:

You can use a global variable ; you declare it outside of your functions, and you access it like this: 

$sql = "...";

function lalala( ) {
  global $sql;

  ...
}

You could also define it in some common file that you use in all others; this solution is better than the previous one; Defines are read-only : can not be modified . 

define( 'SENTENCIA_SQL', "..." );
...

function lalala( ) {
  SENTENCIA_SQL ... // SIN las comillas.
    
answered by 26.04.2017 в 16:33
Hide download link path in PHP or javascript dynamic buttons - get the ID of the button pressed from javascript