PHP DB::select方法代码示例

 /**
  * checks whether new email is already in the database
  * old and new email can be similler for the selected admin only.
  *
  * @param string        $someString
  *
  * @return string
  */
 public function update(Request $request)
 {
     $email = $request->email;
     $id = $request->id;
     $job = $request->job;
     $firstname = $request->firstname;
     $lastname = $request->lastname;
     $admin = \DB::select('SELECT email FROM users WHERE email = "' . $email . '" not in
                                         (select email from users where id!=' . $id . ')');
     if ($this->CheckInternet()) {
         if ($admin == null) {
             //update
             \DB::table('users')->where('id', $id)->update(['firstname' => $firstname, 'lastname' => $lastname, 'job' => $job, 'email' => $email]);
             $sendMail = new EmailController();
             $content = 'Dear ' . $firstname . ', Your profile been Updated.
                 Your Email is ' . $email . ' Please visit
                 WWW.COUPLEY.COM/cp-admin#/AdminLogin';
             $subject = 'COUPLEY Administrator Update';
             $sendMail->SendMail($email, $firstname, $subject, $content);
             return response()->json(['you can use this email' => $admin, 'status' => 200], 200);
         } else {
             return response()->json(['email' => 'email already exists', 'status' => 201], 201);
         }
     } else {
         return response()->json(['status' => 203], 203);
     }
 }

 public function index()
 {
     if (\App\ValidacoesAcesso::PodeAcessarPagina(\Config::get('app.' . $this->rota)) == false) {
         return redirect('home');
     }
     $publicos = \App\Models\publicos::where('clientes_cloud_id', $this->dados_login->empresas_clientes_cloud_id)->get();
     $faixas = \App\Models\faixas::where('clientes_cloud_id', $this->dados_login->empresas_clientes_cloud_id)->get();
     /*Busca Lideres*/
     $lideres = \DB::select('select * from view_lideres where empresas_id = ? and empresas_clientes_cloud_id = ? ', [$this->dados_login->empresas_id, $this->dados_login->empresas_clientes_cloud_id]);
     /*Busca vice - Lideres*/
     $vice_lider = \DB::select('select * from view_vicelideres where empresas_id = ? and empresas_clientes_cloud_id = ? ', [$this->dados_login->empresas_id, $this->dados_login->empresas_clientes_cloud_id]);
     $var_download = "";
     $var_mensagem = "";
     /*Busca Niveis*/
     $view1 = \DB::select('select * from view_celulas_nivel1 v1 where v1.empresas_id = ? and v1.empresas_clientes_cloud_id = ? ', [$this->dados_login->empresas_id, $this->dados_login->empresas_clientes_cloud_id]);
     $view2 = \DB::select('select * from view_celulas_nivel2 v2 where v2.empresas_id = ? and v2.empresas_clientes_cloud_id = ? ', [$this->dados_login->empresas_id, $this->dados_login->empresas_clientes_cloud_id]);
     $view3 = \DB::select('select * from view_celulas_nivel3 v3 where v3.empresas_id = ? and v3.empresas_clientes_cloud_id = ? ', [$this->dados_login->empresas_id, $this->dados_login->empresas_clientes_cloud_id]);
     $view4 = \DB::select('select * from view_celulas_nivel4 v4 where v4.empresas_id = ? and v4.empresas_clientes_cloud_id = ? ', [$this->dados_login->empresas_id, $this->dados_login->empresas_clientes_cloud_id]);
     $view5 = \DB::select('select * from view_celulas_nivel5 v5 where v5.empresas_id = ? and v5.empresas_clientes_cloud_id = ? ', [$this->dados_login->empresas_id, $this->dados_login->empresas_clientes_cloud_id]);
     $celulas_faixas = \DB::select('select * from view_total_celulas_faixa_etaria vw where vw.empresas_id = ? and vw.empresas_clientes_cloud_id = ? ', [$this->dados_login->empresas_id, $this->dados_login->empresas_clientes_cloud_id]);
     $celulas_publicos = \DB::select('select * from view_total_celulas_publico_alvo vw where vw.empresas_id = ? and vw.empresas_clientes_cloud_id = ? ', [$this->dados_login->empresas_id, $this->dados_login->empresas_clientes_cloud_id]);
     //Busca ID do cliente cloud e ID da empresa
     $this->dados_login = \App\Models\usuario::find(Auth::user()->id);
     return view($this->rota . '.index', ['dados' => '', 'celulas_faixas' => $celulas_faixas, 'celulas_publicos' => $celulas_publicos, 'vice_lider' => $vice_lider, 'nivel1' => $view1, 'nivel2' => $view2, 'nivel3' => $view3, 'nivel4' => $view4, 'nivel5' => $view5, 'publicos' => $publicos, 'faixas' => $faixas, 'lideres' => $lideres, 'var_download' => '', 'var_mensagem' => '']);
 }

 /**
  * reset uses to check,
  * whether user is registered,
  *  if it is, then update new password.
  *
  * @return string
  */
 public function reset(Request $request)
 {
     $credentials = $request->only('email', 'password');
     $newpassword = $request->newpassword;
     $mail = $request->email;
     try {
         // verify the credentials and create a token for the user
         if (!($token = JWTAuth::attempt($credentials))) {
             return response()->json(['error' => 'invalid_credentials', 'status' => 201], 201);
         }
     } catch (JWTException $e) {
         // something went wrong
         return response()->json(['error' => 'could_not_create_token', 'status' => 500], 500);
     }
     if ($this->CheckInternet()) {
         $adminName = \DB::select('SELECT firstname FROM users WHERE email = "' . $mail . '"');
         $sendMail = new EmailController();
         $content = 'Dear Administrator, your updated password is ' . $newpassword;
         $subject = 'COUPLEY Password Update';
         $sendMail->SendMail($mail, $adminName[0]->firstname, $subject, $content);
         $hashed = \Hash::make($newpassword);
         \DB::table('users')->where('email', $mail)->update(['password' => $hashed]);
         return response()->json(['password' => 'uptodate', 'status' => 200], 200);
     } else {
         return response()->json(['error' => 'No_network', 'status' => 203], 203);
     }
 }

 /**
  * Show the form for editing the specified resource.
  *
  * @param  int  $id
  * @return Response
  */
 public function edit($id)
 {
     $level = \DB::select('select *from levels order by id desc');
     $data = \DB::table('levels')->where('id', '=', $id)->first();
     //untuk mengambil data berdasarkan id
     return view('level/edit')->with('data', $data, $level);
 }

 public function level()
 {
     $results = DB::select('select * from modul where id = ?', [1]);
     $sboleh = "select * from modul where script='" . $page . "'";
     $rboleh = $conn->Execute($sboleh);
     $ktm = -1;
     $RecordCount = $rboleh->RecordCount();
     if ($RecordCount > 0) {
         while ($wboleh = $rboleh->FetchRow()) {
             @($pos = strpos($wboleh['leveluser'], $_SESSION['level']));
             //$this->alert($wboleh['leveluser']." ".$_SESSION['level']);
             if ($pos === false) {
             } else {
                 $ktm = 1;
             }
         }
         if ($ktm <= 0) {
             echo "Anda Tidak Berhak Anda tidak berhak mengakses modul ini.<br />\n\t\t\t            Hubungi Sistem Administrator untuk memperoleh informasi lebih lanjut.\n\t\t\t            <hr size=1>\n\t\t\t            Pilihan: <a href='logout.php'>Logout</a>";
         } else {
             include_once $file . '.php';
         }
     } else {
         include_once $file . '.php';
     }
 }

 public function login_auth()
 {
     $user_id = Request::get('user_id');
     $user_password = Request::get('user_password');
     $result_list = \DB::select(\DB::raw("SELECT * FROM user_table"));
     foreach ($result_list as $result) {
         if ($result->user_id == $user_id) {
             if ($result->user_password == $user_password) {
                 if ($result->user_type == 'admin') {
                     $_SESSION['TICKET_USER_ID'] = $user_id;
                     $_SESSION['TICKET_USER_TYPE'] = $user_type;
                     return view('ticket_views.home');
                 }
                 if ($result->user_type == 'user') {
                     $client_id = $result->client_id;
                     if ($client_id == 'c_robi') {
                         $_SESSION['TICKET_USER_ID'] = $user_id;
                         $_SESSION['TICKET_USER_TYPE'] = $result->user_type;
                         $_SESSION['TICKET_USER_CLIENT_ID'] = $client_id;
                         $_SESSION['TICKET_USER_CLIENT_TYPE'] = 'telco';
                         return view('ticket_views.create_ticket_client');
                     }
                 }
             }
         }
     }
 }

 public function inbox()
 {
     $name = \Auth::user()->name;
     $results = \DB::select('select * from notification.inbox_' . $name . ' order by created_at desc limit 70 offset 0');
     \DB::update('update notification.inbox_' . $name . ' set n_read = true');
     return view('profile.inbox')->with('results', $results);
 }

 public function getbox()
 {
     $userid = \Auth::user()->id;
     $cart = \DB::select("SELECT COUNT(*) as numitems, SUM(totalprice) as totalprice, SUM(selqty) as quantity FROM currentcart WHERE userid = " . $userid);
     $currency = \DB::select("SELECT currency FROM users WHERE id = " . $userid);
     return view('cart.cartbox', array('cart' => $cart, 'currency' => $currency));
 }

 public function show($referen)
 {
     $empresa = Session::get('empresa');
     $pedimento = \DB::connection('users')->table('optr01')->select('ref_transport1', 'ref_tipo')->where('pk_referencia', $referen)->first();
     $result = \DB::select('select chk.id as id,chk_document,doc_nombre,chk_required from auditoria.aud_checklist chk INNER JOIN secenetc_master_mdb.mdb_tipodocum td ON chk.chk_document = td.doc_clave WHERE chk_opera = ? AND  chk_transport = ?', [$pedimento->ref_tipo, $pedimento->ref_transport1]);
     return view('administration.list')->with(['document' => $result, 'referen' => $referen]);
 }

 public function divideTeam($team, $min, $max, $boolit, $compID)
 {
     $len = count($team);
     $numTeam = $len / $min;
     $totalTeam = array();
     //$team = array_flip($team);
     //return $team;
     if ($boolit) {
         $a = array_keys($team);
     } else {
         $a = $team;
     }
     //print_r($a);
     while (count($a) >= $min) {
         $t = array();
         while (count($t) < $min) {
             array_push($t, array_pop($a));
             $a = array_reverse($a);
         }
         array_push($totalTeam, $t);
     }
     if (count($a) > 0 && count($totalTeam) > 0) {
         $i = 0;
         foreach ($a as $b) {
             array_push($totalTeam[$i % count($totalTeam)], $b);
             $i = $i + 1;
         }
     }
     $unsorted = array();
     $teamteam = array();
     //return $totalTeam;
     foreach ($totalTeam as $c) {
         if (count($c) > $max) {
             while (count($c) > $max) {
                 print_r($c);
                 array_push($unsorted, array_pop($c));
             }
             array_push($teamteam, $c);
         } else {
             array_push($teamteam, $c);
         }
     }
     if (count($teamteam) > 0) {
         foreach ($teamteam as $t) {
             team::create(['competition' => $compID, 'teamName' => 'Unnamed Team']);
             $val = \DB::select(\DB::raw('(SELECT max(teamID) as i FROM team)'));
             $teamID = $val[0]->i;
             foreach ($t as $s) {
                 //print_r($s);
                 //print_r(',');
                 team_contents::create(['teamID' => $teamID, 'studentID' => $s]);
                 \DB::table('student_competition')->where('studentID', '=', $s)->delete();
             }
         }
     }
     if (count($totalTeam == 0)) {
         return $a;
     }
     return $unsorted;
 }

 public function debug()
 {
     echo '<pre>';
     echo '<h1>Environment</h1>';
     echo \App::environment() . '</h1>';
     echo '<h1>Debugging?</h1>';
     if (config('app.debug')) {
         echo "Yes";
     } else {
         echo "No";
     }
     echo '<h1>Database Config</h1>';
     /*
     The following line will output your MySQL credentials.
     Uncomment it only if you're having a hard time connecting to the database and you
     need to confirm your credentials.
     When you're done debugging, comment it back out so you don't accidentally leave it
     running on your live server, making your credentials public.
     */
     //print_r(config('database.connections.mysql'));
     echo '<h1>Test Database Connection</h1>';
     try {
         $results = \DB::select('SHOW DATABASES;');
         echo '<strong style="background-color:green; padding:5px;">Connection confirmed</strong>';
         echo "<br><br>Your Databases:<br><br>";
         print_r($results);
     } catch (Exception $e) {
         echo '<strong style="background-color:crimson; padding:5px;">Caught exception: ', $e->getMessage(), "</strong>\n";
     }
     echo '</pre>';
 }

 public function postIndex()
 {
     $user_id = \Auth::user()->id;
     $reservedPersonalHours = \DB::select('SELECT reservedhours.id,categories.name,reservedhours.start FROM `reservedhours`,`categories`,`services` where DATE(reservedhours.start) = CURRENT_DATE and reservedhours.service_id = services.id AND services.category_id = categories.id and reservedhours.user_id =' . $user_id);
     $reservedPersonalHours = json_encode($reservedPersonalHours);
     return \Response::json(\View::make('partials.tasks', compact('reservedPersonalHours'))->render(), 200);
 }

 public function getSensor()
 {
     $id = $_SERVER['REMOTE_ADDR'];
     if (\DB::select('select 1 from module_vanguard where id = ?', [$id])) {
         if (\DB::select('select 1 from app_status where validity = true and sensor_register = true')) {
             $results = \DB::select('select * from module_vanguard where id = ?', [$id]);
             foreach ($results as $result) {
                 $alias = $result->alias;
                 $status = $result->status;
             }
             $timestamp = new \DateTime();
             if ($status == 'active') {
                 $must_report = true;
             }
             if ($status == 'passive') {
                 $must_report = false;
             }
             $is_reported = false;
             \DB::insert('insert into vanguard_log values (?, ?, ?, ?, ?)', [$id, $alias, $must_report, $is_reported, $timestamp]);
             //open to adjustment.
             exec("nodejs /var/www/topsus/nodejs/telegram_check.js");
             return 'OK';
         } else {
             return 'NOT OK - APPLICATION IS NOT LISTENING';
         }
     } else {
         return 'NOT OK - SENSOR IS INVALID';
     }
 }

 public function listaRaza()
 {
     $raza = DB::select('select * from Raza WHERE Activado = 1');
     for ($i = 0; $i < count($raza); $i++) {
         $Razas[$i] = new Raza($raza[i]->idRaza, $raza->Nombre, $raza->EstiloDePelo, $raza->Especie_idEspecie);
     }
     return $Razas;
 }

 public function testRedis()
 {
     // \Redis::set('feiyi', 'liuyan');
     // echo \Redis::get('feiyi');exit;
     $result = \DB::select('select * from articles');
     print_r($result);
     exit;
 }