Query to database

Question

Query to database

Navigation

#1 by (4 votes)

3

I'm working with someone else's code, it's a mix of structured programming, POO, MVC.

Inside a file task_model.php I have two methods, the first getTareasPendientes applies filters and makes the query with the database to throw the array of the The employee's pending tasks as shown in the following image:

The second method cancelTareasP (in which I am working), will cancel the pending tasks of a specific project and record the changes in a history.

What I want is to get only the id of the pending tasks in the getTareasPendientes method and use it in the cancelTareasP method, since to register the changes in the history I must make the query and Update of each task separately.

I hope I have managed to explain and even though I can not put the whole code to be understood.

task_model.php

<?php    

    /**
     * Devuelve arreglo de tareas PENDIENTES asociadas al Empleado.
     * @param  SafeMySQL $db    Conex. a BD
     * @param  integer    $idEmp ID del Empleado (opcional)
     * @param  array    $aFiltro Arreglo para aplicar filtros personalizados a la consulta:
     *                           ftroLimit: valore entero como limite para reg a devolver
     *                           proyecto_id: ID de proyecto
     * @return array           [description]
     */
    public static function getTareasPendientes(SafeMySQL $db, $idEmp = 0, $aFiltro=array()){
        $ftroLimit = $proyecto_id = '';
        if(!empty($aFiltro) && is_array($aFiltro)){//condiciones esperadas
            if(isset($aFiltro['ftroLimit']) && $aFiltro['ftroLimit'] > 0){//se envia un valor entero como limite
                $ftroLimit = ' LIMIT '.$aFiltro['ftroLimit'];
            }
            if(isset($aFiltro['proyecto_id'])) $proyecto_id = $aFiltro['proyecto_id'];//se espera ID de proyecto
        }
        $sql = "SELECT t1.id, t1.solicitud, t2.responsable FROM Tareas t1
        INNER JOIN tareas_emp t2 ON t1.id = t2.tareas_id
        LEFT JOIN Proyectos t3 ON t1.proyecto_id = t3.id
        WHERE t1.estatus = 'Pendiente' ";
        if($idEmp > 0){
            $sql.= "AND t2.empleado_id = $idEmp ";
        }
        if($proyecto_id > 0){
            $sql.= "AND t3.id = $proyecto_id ";
        }
        $sql.= $ftroLimit;
        $data = $db->getAll($sql);
        return $data;
    }    

    public static function cancelTareasP(Usuario $_Usuario, $proyecto_id) {    

        $conex = $_Usuario->getConexBD();    

        if ($_Usuario instanceof Usuario) {
            $tipoUsuario = $_Usuario->getTipo();
            $nivelAcceso = $_Usuario->getPerfil()->get_nivelacc();
        }    

        $tareasPend = extract(Tarea::getTareasPendientes($conex));
        $i = 0;    

        while ($i <= $tareasPend) {
            return $conex->query("UPDATE Tareas SET estatus = 'Cancelada' WHERE proyecto_id = '$proyecto_id' AND id = '$tareasPend'");
            $i++;
        }    

        return regHistorico($conex, 4, 'Tareas', $tareasPend, $proyecto_id);    

    }    

    ?>

sql php mysqli

asked by Paloma Alvarado 01.08.2017 в 18:23

source

1 answer

Error in MySQL stored procedure - Python Error: uninitialized local variable 'x' used

score 4 · Answer 1

Problem

In your comment you specify that this is what the getTareasPendientes method returns:

[0] => Array (
    [id] => 1
    [solicitud] => Cancelar todas las tareas a la vez
    [responsable] => 1
    )
[1] => Array (
    [id] => 2
    [solicitud] => Tesis
    [responsable] => 1
    )
[2] => Array (
    [id] => 3
    [solicitud] => Tarea 3
    )

You are using extract() , which, according to the PHP documentation:

Returns the number of variables successfully imported into the table of symbols.

In addition, extract() only accepts an associative array as an input parameter, in your case you are passing an indexed array.

For this to work for you, you would have to add a parameter like EXTR_PREFIX_ALL and add a prefix like myarray . If 0, 1 and 2 the indexed array keys, you would be assigning their values to the variables $ myarray_0, $ myarray_1 and $ myarray_2. This does not help us for what we want to do in this case.

That is, in your code you are assigning to $tareasPend the count() of that array, or in this case, 0 will be the assigned value, since the input of the method extract() is not correct, so that it is not importing any variable.

Therefore, taking into account your current while , you would always be using condition id = 0 not the real id of each of the tasks. That's why it's not that I'm just taking the first id as you say, it's always assigned the value 0 that it takes out of extract()

Also, you are doing a return within the loop, which causes the function to be abandoned after the first iteration. Therefore, you will never be able to reach the id of the second element of the array , because we left in the first one.

Solution

A foreach serves you perfectly to solve the problem and you should not use extract() .

To do this, assign the Array to $tareasPend :

$tareasPend = Tarea::getTareasPendientes($conex);

And then iterate on them in the foreach:

foreach ($tareasPend as $tarea) {
    $id_tarea = $tarea['id'];
    $conex->query("UPDATE Tareas SET estatus = 'Cancelada' WHERE proyecto_id = '$proyecto_id' AND id = '$id_tarea'");
}

Edit

I forgot to tell you that, now, in your return :

return regHistorico($conex, 4, 'Tareas', $tareasPend, $proyecto_id);

$tareasPend is an Array. If you need to pass the number of tasks, you will have to use count($tareasPend)

Issue 2

By doing return within foreach , you were leaving the function and that's why you did not iterate through all the tasks. Do not use return there. I have edited the answer above.