Run several prepared queries

0

I have a query in which in the while of that query I make another subquery but it does not bring anything.

Could you see what's wrong with the code that does not bring anything? It does not actually go through the first while.

 function datos(){

 $opcion=$this->opcion;
 $fila="";
 $dias="";
 if ($opcion=="todo") {
  echo "entre al if";
   $sql="SELECT count(e.RUT_PARTICIPANTE) as cantidad,cg.NOMBRE_CURSO,cg.COSTO,cu.ID_CURSO_UNICO,cu.DURACION_H,cu.JORNADA_H,
          cu.CANTIDAD_PARTICIPANTES,cu.fecha_inicio,cu.fecha_termino,cu.HORA_DESDE,cu.HORA_HASTA,c.nombre_comuna,cu.RUT_RELATOR,nombre_estado
          from  participanes p, enrolamiento e,curso_unico cu,curso_generico cg,comunas c,estado_curso ec
          where p.RUT_PARTICIPANTE=e.RUT_PARTICIPANTE
          AND   c.id_comuna=cu.id_comuna
          AND   cg.ID_CURSO=cu.ID_CURSO
          AND   cu.ID_CURSO_UNICO=e.ID_CURSO_UNICO
          AND ec.id_estado_curso=cu.id_estado_curso";
          $stmt=$this->conexion->prepare($sql);
          if (!$stmt) {
            echo $this->conexion->error;
          }
          $stmt->bind_result($cantidad,$n_curso,$costo,$id_curso,$duracion,$jornada,$can_part,$f_inicio,$f_termino,$h_desde,$h_hasta,$comuna,$relator,$estado);
          $stmt->execute();
          $stmt->store_result(); 
          $stmt->free_result();



          while($stmt->fetch()){

            $stmt1=$this->conexion->prepare("SELECT d.nombre FROM curso_dias cd, dias d where cd.num_dia=d.num_dia and cd.ID_CURSO_UNICO=?");
            if (!$stmt1) {
              echo $this->conexion->error;
            }
            $stmt1->bind_param('i',$id_curso);
            $stmt1->bind_result($nombre);
            $stmt1->execute();
             $stmt1->store_result();
            while ($stmt1->execute()) {
            $dias="$nombre,";

            }
            $stmt1->close();
            $horario=$dias."<br>desde:$h_desde hasta:$h_hasta";
           $fila.="<tr>
                   <td>$id_curso</td>
                   <td>$relator</td>
                   <td>$duracion</td>
                   <td>$jornada</td>
                   <td>$$cant_part</td>
                   <td>$f_inicio</td>
                   <td>$$f_termino</td>
                   <td>$horario</td>
                    <td>$estado</td>
                  </tr>";
            $dias="";
            echo $id_curso;   
          }
          $stmt->close();

 }

 return $fila;
}
    
asked by jose miguel jara 04.05.2017 в 08:13
source

1 answer

1

The problem you suffer is because you are releasing the resources and memory associated with storing the result with $stmt->free_result() before making use of said data:

  

Note:

     

You should always release the result with mysqli_free_result (), when   the object of the result is no longer necessary.

Also, you should do the bind_result() after execute() and the store_result() before% % co:

  

Note:

     

Notice that all columns must be linked after   call mysqli_stmt_execute () and before calling   mysqli_stmt_fetch (). Depending on the column types, the variables   they can silently change to the corresponding PHP type.

The correct way to use bind_result() would be at the end of the work with the obtained records, which could be after obtaining all with free_result() or at the end of the fetch_all loop.

Here is an example of how to use the first query, use it and release it later:

 function datos(){

 $opcion=$this->opcion;
 $fila="";
 $dias="";
 if ($opcion=="todo") {
  echo "entre al if";
   $sql="SELECT count(e.RUT_PARTICIPANTE) as cantidad,cg.NOMBRE_CURSO,cg.COSTO,cu.ID_CURSO_UNICO,cu.DURACION_H,cu.JORNADA_H,
          cu.CANTIDAD_PARTICIPANTES,cu.fecha_inicio,cu.fecha_termino,cu.HORA_DESDE,cu.HORA_HASTA,c.nombre_comuna,cu.RUT_RELATOR,nombre_estado
          from  participanes p, enrolamiento e,curso_unico cu,curso_generico cg,comunas c,estado_curso ec
          where p.RUT_PARTICIPANTE=e.RUT_PARTICIPANTE
          AND   c.id_comuna=cu.id_comuna
          AND   cg.ID_CURSO=cu.ID_CURSO
          AND   cu.ID_CURSO_UNICO=e.ID_CURSO_UNICO
          AND ec.id_estado_curso=cu.id_estado_curso";
          $stmt=$this->conexion->prepare($sql);
          if (!$stmt) {
            echo $this->conexion->error;
          }
          $stmt->execute();
          $stmt->store_result(); 
          $stmt->bind_result($cantidad, $n_curso, $costo, $id_curso,
               $duracion, $jornada, $can_part, $f_inicio, $f_termino,
               $h_desde, $h_hasta, $comuna, $relator, $estado);



          while($stmt->fetch()){

            $stmt1=$this->conexion->prepare("SELECT d.nombre FROM curso_dias cd, dias d where cd.num_dia=d.num_dia and cd.ID_CURSO_UNICO=?");
            if (!$stmt1) {
              echo $this->conexion->error;
            }
            $stmt1->bind_param('i',$id_curso);
            $stmt1->execute();
            /* Una forma tosca de hacerlo ...
            $stmt1->store_result();
            $stmt1->bind_result($nombre);
            $dias = []; // o $dias = array(); si usas una versión antigua de PHP
            while ($stmt1->fetch()) {
                 $dias[] = $nombre;
            }
            $dias = implode(',', $dias); */
            /* La forma que te propongo: */
            $res = $stmt1->get_result();
            $dias = implode(',', $res->fetch_all());
            $stmt1->close();
            $horario=$dias."<br>desde:$h_desde hasta:$h_hasta";
           $fila.="<tr>
                   <td>$id_curso</td>
                   <td>$relator</td>
                   <td>$duracion</td>
                   <td>$jornada</td>
                   <td>$$cant_part</td>
                   <td>$f_inicio</td>
                   <td>$$f_termino</td>
                   <td>$horario</td>
                    <td>$estado</td>
                  </tr>";
            $dias="";
            echo $id_curso;   
          }
          $stmt->free_result();
          /* Esto es un alias del anterior: $stmt->close(); */

 }

 return $fila;
}

I, personally, prefer to get row by row or all results to a variable and work with that data, I do not like to let something modify variables and risk causing side effects.

Note: in the while interior you are doing while . Would not it be while ($stmt1->execute()) ? I've made some changes for you to review, maybe it's the best way to do what you try.

    
answered by 04.05.2017 / 09:04
source