PHP Connect::prepare方法代码示例

 public static function usersReport()
 {
     $conn = new Connect();
     $query = 'SELECT * FROM ' . self::DB_TBL_USUARIOS;
     $consult = $conn->prepare($query);
     $consult->execute();
     if ($consult->rowCount() > 0) {
         header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
         header('Content-Disposition: attachment;filename="Reporte de usuarios.xlsx"');
         header('Cache-Control: max-age=0');
         $reportName = "Reporte de usuarios";
         $reportNameTitles = array("Id", "Cédula", "Nombres", "Apellidos", "Email", "Teléfono", "Extensión", "Usuario", "Contrasena", "Rol");
         $styleColumnsTitle = array('font' => array('name' => 'Arial', 'bold' => true));
         $generarReporteXLSX = new PHPExcel();
         $generarReporteXLSX->getProperties()->setCreator("VideoConferencias UTPL")->setLastModifiedBy("VideoConferencias UTPL")->setTitle("Reporte de usuarios")->setSubject("Reporte de usuarios")->setDescription("Reporte de usuarios")->setKeywords("Reporte de usuarios")->setCategory("Reportes");
         $generarReporteXLSX->setActiveSheetIndex(0)->mergeCells('A1:J1');
         $generarReporteXLSX->setActiveSheetIndex(0)->setCellValue('A1', $reportName)->setCellValue('A3', $reportNameTitles[0])->setCellValue('B3', $reportNameTitles[1])->setCellValue('C3', $reportNameTitles[2])->setCellValue('D3', $reportNameTitles[3])->setCellValue('E3', $reportNameTitles[4])->setCellValue('F3', $reportNameTitles[5])->setCellValue('G3', $reportNameTitles[6])->setCellValue('H3', $reportNameTitles[7])->setCellValue('I3', $reportNameTitles[8])->setCellValue('J3', $reportNameTitles[9]);
         $i = 4;
         while ($row = $consult->fetch()) {
             $generarReporteXLSX->setActiveSheetIndex(0)->setCellValue('A' . $i, $row['id'])->setCellValue('B' . $i, $row['cedula'])->setCellValue('C' . $i, $row['nombres'])->setCellValue('D' . $i, $row['apellidos'])->setCellValue('E' . $i, $row['email'])->setCellValue('F' . $i, $row['telefono'])->setCellValue('G' . $i, $row['telefono_ext'])->setCellValue('H' . $i, $row['usuario'])->setCellValue('I' . $i, $row['contrasena'])->setCellValue('J' . $i, $row['id_rol']);
             $i++;
         }
         $generarReporteXLSX->getActiveSheet()->getStyle('A3:J3')->applyFromArray($styleColumnsTitle);
         $generarReporteXLSX->getActiveSheet()->setTitle('Usuarios');
         $generarReporteXLSX->setActiveSheetIndex(0);
         $generarReporteXLSX->getActiveSheet(0)->freezePaneByColumnAndRow(0, 4);
         $objWriter = PHPExcel_IOFactory::createWriter($generarReporteXLSX, 'Excel2007');
         $objWriter->save('php://output');
         exit;
     }
 }

 public static function login($usu_usuario, $usu_contrasena)
 {
     $conn = new Connect();
     $u = new Utils();
     $query = 'SELECT * FROM ' . self::DB_TBL_USUARIO . ' WHERE usu_usuario = :usu_usuario';
     $consult = $conn->prepare($query);
     $consult->bindParam(':usu_usuario', $usu_usuario);
     $consult->execute();
     $row = $consult->fetch();
     if ($consult->rowCount() > 0) {
         $passValidate = $u->passValidate($usu_contrasena, $row['usu_contrasena']);
         if ($passValidate == true) {
             if ($row['usu_id_rol'] == 1) {
                 session_start();
                 $_SESSION['session'] = $row['usu_usuario'];
                 echo "<script>location.href='../../mod_admin/vista/administrador.php'</script>";
             } else {
                 session_start();
                 $_SESSION['session'] = $row['usu_usuario'];
                 echo "<script>location.href='../../mod_admin/vista/tecnico.php'</script>";
             }
         } else {
             echo "<script>alert('Error de autenticación. Por favor verifique sus credenciales de acceso.')</script>";
             echo "<script>location.href = '../vista/form_login.php' </script>";
             return false;
         }
     } else {
         echo "<script>alert('Error de autenticación.')</script>";
         echo "<script>location.href = '../vista/form_login.php' </script>";
         return false;
     }
 }

 public function __construct()
 {
     $db = new Connect();
     $act = $db->query("SELECT ultima_act FROM generales LIMIT 1;");
     $actualizacion = $db->recorrer($act);
     if (time() >= $actualizacion[0]) {
         $tops = $db->query("SELECT id,puntos FROM usuarios ORDER by puntos DESC;");
         $tope = 1;
         $psql = "UPDATE usuarios SET top = ? WHERE id = ? LIMIT 1;";
         $prepare_query = $db->prepare($psql);
         $prepare_query->bind_param('ii', $nuevo_top, $id_user);
         while ($top = $db->recorrer($tops)) {
             $nuevo_top = $tope++;
             $id_user = $top['id'];
             $prepare_query->execute();
         }
         $timer = time() + 30;
         $query = $db->query("UPDATE generales SET ultima_act='{$timer}' LIMIT 1;");
         $prepare_query->close();
         unset($actualizar, $tops, $timer, $tope, $query);
     } else {
         unset($actualizacion);
     }
     $db->liberar($act);
     $db->close();
 }

 public static function getAllConfirmReserves()
 {
     $conn = new Connect();
     $query = 'SELECT * ' . 'FROM reserva RE, res_fecha FE, confirmacion_reserva CON, con_imagen IMA, con_canal CAN ' . 'WHERE RE.res_estado = 1 ' . 'AND FE.fe_id_reserva = RE.res_id ' . 'AND con.con_id_reserva = RE.res_id ' . 'AND IMA.con_id = CON.con_id_imagen ' . 'AND CAN.can_id = CON.con_id_canal ' . 'ORDER BY FE.start ASC ' . 'LIMIT 4';
     $consult = $conn->prepare($query);
     $consult->execute();
     $row = $consult->fetchAll(PDO::FETCH_ASSOC);
     return json_encode($row, JSON_UNESCAPED_UNICODE);
 }

 public static function getAllUsersById($id)
 {
     $conn = new Connect();
     $query = 'SELECT * FROM usuarios WHERE id=:id';
     $consult = $conn->prepare($query);
     $consult->bindParam(":id", $id);
     $consult->execute();
     $row = $consult->fetchAll(PDO::FETCH_ASSOC);
     return json_encode($row, JSON_UNESCAPED_UNICODE);
 }

 public static function elimina($con_id)
 {
     $conexion = new Connect();
     $query = 'DELETE FROM ' . self::DB_TBL_CONFIRMACION . ' WHERE con_id = ' . $con_id;
     $consulta = $conexion->prepare($query);
     if ($consulta->execute()) {
         // $this->con_id = $conexion->lastInsertId();
         //echo "<script>alert('Datos actualizados correctamente')</script>";
         return 1;
     } else {
         //echo $consulta->errorCode();
         //echo "<script>alert('Error al actualizar datos')</script>";
         echo $consulta->errorCode();
         return 0;
     }
 }

 public static function borrarCentro($map_id)
 {
     $conn = new Connect();
     $query = "DELETE FROM " . self::DB_TBL_CENTROS_MAP . " WHERE map_id=" . $map_id;
     $consult = $conn->prepare($query);
     if ($consult->execute()) {
         echo "<script>alert('Usuario eliminado correctamente')</script>";
         echo "<script>location.href='../vista/ges_centros.php'</script>";
     } else {
         echo "<script>alert('Ha ocurrido un error')</script>";
     }
 }

 public static function presentar_Eventos()
 {
     $conn = new Connect();
     $query = "SELECT * FROM con_tipo_evento";
     $consult = $conn->prepare($query);
     $consult->execute();
     $row = $consult->fetchAll(PDO::FETCH_ASSOC);
     foreach ($row as $i) {
         echo "<tr>";
         echo "<td>" . $i['tip_nombre'] . "</td>";
         echo "<td><a href='../controlador/eliminar_reserva.php?id=" . $i['tip_id'] . "' onclick='return confirm(\"¿Realmente desea eliminar la reserva?\");'><span class='glyphicon glyphicon-remove' aria-hidden='true'></span></a></td>";
         echo "</tr>";
     }
 }

 private function __construct()
 {
     require 'core/models/class.User.php';
     require 'core/models/implement.Menu.php';
     require 'core/models/class.Planet.php';
     require 'core/models/implement.Topnav.php';
     global $id_user, $id_planet;
     $lng = new Lang();
     $menu = new Menu($id_user);
     $topnav = new Topnav($id_planet, $id_user);
     $user = new User($id_user);
     $db = new Connect();
     $sql = $db->query("SELECT sistema FROM planetas WHERE id_planeta='{$id_planet}' LIMIT 1;");
     $system = $db->recorrer($sql);
     $db->liberar($sql);
     if (isset($_GET['sistema']) and is_numeric($_GET['sistema']) and $_GET['sistema'] > 0 and $_GET['sistema'] <= 700) {
         $this->system_id = intval($_GET['sistema']);
     } else {
         $this->system_id = intval($system['sistema']);
     }
     unset($system, $sql);
     $x = 1;
     $psql = "SELECT SQL_BIG_RESULT DISTINCT id_planeta, pos, nombre, imagen, usuario, id_dueno \n            FROM planetas JOIN usuarios \n            ON planetas.id_dueno=usuarios.id WHERE sistema=? \n            AND pos=? ORDER BY pos ASC LIMIT 1;";
     $prepare_sql = $db->prepare($psql);
     $prepare_sql->bind_param('ii', $orbit, $system_id);
     while ($x < 16) {
         $orbit = $x;
         $system_id = $this->system_id;
         $prepare_sql->execute();
         $id_planeta = '';
         $pos = '';
         $nombre = '';
         $imagen = '';
         $usuario = '';
         $id_dueno = '';
         $prepare_sql->bind_result($id_planeta, $pos, $nombre, $imagen, $usuario, $id_dueno);
         $prepare_sql->fetch();
         $o_s = "&o={$x}&s={$this->system_id}";
         if ($x != $pos) {
             if ($x == 1 or $x == 5 or $x == 9 or $x == 13) {
                 $universe[] = array('posicion' => $x, 'nombre' => 'Brecha', 'imagen' => 'brecha_espacio', 'usuario' => 'Agujero de gusano', 'escombros' => 'no_in', 'luna' => 'no_in', 'habitado' => 'deshabitado', 'accion' => '<a href=\'?core=fleets&mision=saltar' . $o_s . '\'>' . $lng->x_saltar . '</a>');
             } else {
                 $universe[] = array('posicion' => $x, 'nombre' => 'Planeta Habitable', 'imagen' => 'planeta_desconocido', 'usuario' => 'La atmósfera es apta para colonizar', 'escombros' => 'no_in', 'luna' => 'no_in', 'habitado' => 'deshabitado', 'accion' => '<a href=\'?view=flotas&mision=colonizar' . $o_s . '\'>' . $lng->x_colonizar . '</a>');
             }
         } else {
             if ($id_dueno == $id_user and $id_planeta != $id_planet) {
                 $universe[] = array('posicion' => $pos, 'nombre' => $nombre, 'imagen' => $imagen, 'usuario' => $usuario, 'escombros' => 'no_es', 'luna' => 'no_es', 'habitado' => 'habitado', 'accion' => '<a href=\'?view=flotas&mision=transportar' . $o_s . '\'>' . $lng->x_transportar . '</a> |
                                 <a href=\'?view=flotas&mision=desplegar' . $o_s . '\'>' . $lng->x_desplegar . '</a>');
             } else {
                 if ($id_planeta == $id_planet and $id_dueno == $id_user) {
                     $universe[] = array('posicion' => $pos, 'nombre' => $nombre, 'imagen' => $imagen, 'usuario' => $usuario, 'escombros' => 'no_es', 'luna' => 'no_es', 'habitado' => 'habitado', 'accion' => '<br />' . $lng->x_no_hay . '');
                 } else {
                     $universe[] = array('posicion' => $pos, 'nombre' => $nombre, 'imagen' => $imagen, 'usuario' => $usuario, 'escombros' => 'no_es', 'luna' => 'no_es', 'habitado' => 'habitado', 'accion' => '<a href=\'?view=flotas&mision=transportar' . $o_s . '\'>' . $lng->x_transportar . '</a> |
                                 <a href=\'?view=flotas&mision=desplegar' . $o_s . '\'>' . $lng->x_desplegar . '</a> | <br /> 
                                 <a href=\'?view=flotas&mision=tomar_recursos' . $o_s . '\'>' . $lng->x_tomar_recursos . '</a> |  
                                 <a href=\'?view=flotas&mision=defender' . $o_s . '\'>' . $lng->x_defender . '</a> <br />
                                 <a href=\'?view=flotas&mision=espiar' . $o_s . '\'>' . $lng->x_espiar . '</a> |
                                 <a href=\'?view=flotas&mision=atacar' . $o_s . '\'>' . $lng->x_atacar . '</a> |
                                 <a href=\'?view=flotas&mision=sac' . $o_s . '\'>' . $lng->x_sac . '</a>');
                 }
             }
         }
         $x++;
         //bucle increment
     }
     $prepare_sql->close();
     $db->close();
     $template = new Smarty();
     $template->assign(array('sistema' => $this->system_id, 'x_ir' => $lng->x_ir, 'x_anterior' => $lng->x_anterior, 'x_siguiente' => $lng->x_siguiente, 'x_orbita' => $lng->x_orbita, 'x_planeta' => $lng->x_planeta, 'x_emperador' => $lng->x_emperador, 'x_accion' => $lng->x_accion, 'universe' => $universe));
     $template->display('universe/universe.xnv');
     unset($x, $template);
 }

 public static function mostrar_ReservasNoConfirmadas()
 {
     $conn = new Connect();
     $query = 'SELECT * FROM ' . self::DB_TBL_RESERVA . " WHERE res_estado='0'";
     $consult = $conn->prepare($query);
     $consult->execute();
     $row = $consult->fetchAll(PDO::FETCH_ASSOC);
     foreach ($row as $i) {
         echo "<tr>";
         echo "<td>" . $i['res_cedula'] . "</td>";
         echo "<td>" . $i['res_nombres'] . "</td>";
         echo "<td>" . $i['res_apellidos'] . "</td>";
         echo "<td>" . $i['res_email'] . "</td>";
         echo "<td>" . $i['res_telefono'] . "</td>";
         echo "<td>" . $i['res_pais'] . "</td>";
         echo "<td>" . $i['res_organizador'] . "</td>";
         echo "<td>" . $i['res_expositor'] . "</td>";
         echo "<td>" . $i['res_informacion'] . "</td>";
         echo "<td>" . $i['title'] . "</td>";
         echo "<td><a href='../../mod_confirmacion/vista/confirmar_reserva.php?id=" . $i['res_id'] . "'><span class='glyphicon glyphicon-ok' aria-hidden='true'></span></a></td>";
         echo "<td><a href='../controlador/eliminar_reserva.php?id=" . $i['res_id'] . "' onclick='return confirm(\"¿Realmente desea eliminar la reserva?\");'><span class='glyphicon glyphicon-remove' aria-hidden='true'></span></a></td>";
         echo "</tr>";
     }
 }