PHP DB::select方法代码示例

 /**
  * Execute the job.
  *
  * @return void
  */
 public function handle()
 {
     $mail = new SendMailController();
     $cam = $this->campaign;
     //        $list_ids = $cam->subscribers_lists()->select('id')->get()->pluck('id')->toArray();
     $str = implode(',', $this->list_ids);
     $query = "select distinct email from subscribers where id in " . "(select subscriber_id from subscriber_subscribers_list where subscribers_list_id in ({$str})) limit " . $this->take . " offset " . $this->skip;
     $subscribers = DB::select($query);
     //        $subscribers = $l->subscribers()->take($this->take)->skip($this->skip)->get();
     foreach ($subscribers as $subscriber) {
         if (filter_var($subscriber->email, FILTER_VALIDATE_EMAIL)) {
             $url = 'http://colorme.vn/manage/email/open?cam_id=' . $cam->id . '&to=' . $subscriber->email;
             $content = $cam->template->content . '<img src="' . $url . '" width="1" height="1"/>';
             $result = $mail->sendAllEmail([$subscriber->email], $cam->subject, $content);
             $email_id = $result->get('MessageId');
             $email = Email::find($email_id);
             if ($email == null) {
                 $email = new Email();
                 $email->id = $email_id;
                 $email->status = 0;
             }
             $email->campaign_id = $cam->id;
             $email->to = $subscriber->email;
             $email->save();
         }
     }
 }

 /**
  * Store the job in the database.
  *
  * Returns the id of the job.
  *
  * @param string $job
  * @param mixed  $data
  * @param int    $delay
  *
  * @return int
  */
 public function storeJob($job, $data, $delay = 0)
 {
     $payload = $this->createPayload($job, $data);
     $database = Config::get('database');
     if ($database['default'] === 'odbc') {
         $row = DB::select(DB::raw("SELECT laq_async_queue_seq.NEXTVAL FROM DUAL"));
         $id = $row[0]->nextval;
         $job = new Job();
         $job->id = $id;
         $job->status = Job::STATUS_OPEN;
         $job->delay = $delay;
         $job->payload = $payload;
         $job->save();
     } else {
         if ($database['default'] === 'mysql') {
             $payload = $this->createPayload($job, $data);
             $job = new Job();
             $job->status = Job::STATUS_OPEN;
             $job->delay = $delay;
             $job->payload = $payload;
             $job->save();
             $id = $job->id;
         }
     }
     return $id;
 }

 function listele()
 {
     $data = DB::select('select y.id,k.adi,k.soyadi,u.baslik as urunBaslik,y.baslik as yorumBaslik,y.icerik,y.puan,y.tarih, y.durum as durum from yorumlar as y left join kullanicilar as k on y.kul_id = k.id left join urunler as u on y.urun_id = u.id order by y.id desc');
     $kullanici = DB::select('select * from kullanicilar order by id desc');
     $urunler = DB::select('select * from urunler order by id desc');
     return view('admin/urunYorumlar', array('data' => $data, 'kullanici' => $kullanici, 'urunler' => $urunler));
 }

 public function getIndex()
 {
     $this->pageSetting(['menu_group_id' => 20, 'menu_id' => 1, 'title' => 'แก้ไขข้อมูลส่วนตัว | MEA FUND']);
     $agent = new MeaAgent();
     //        var_dump($agent->device());
     $sqlinfo = "SELECT * FROM TBL_EMPLOYEE_INFO info\nINNER JOIN TBL_USER us ON us.EMP_ID = info.EMP_ID\nWHERe info.EMP_ID = '" . get_userID() . "'";
     $userinfo = DB::select(DB::raw($sqlinfo))[0];
     $sqlbenefit = "SELECT * FROM TBL_USER_BENEFICIARY  WHERe EMP_ID = '" . get_userID() . "' ORDER BY CREATE_DATE DESC";
     $userbenefit = DB::select(DB::raw($sqlbenefit));
     $sql44 = "SELECT TOP 1 * FROM TBL_INFORMATION_FROM_ASSET WHERE EMP_ID =  '" . get_userID() . "' ORDER BY CREATE_DATE DESC";
     $infoaset_db = DB::select(DB::raw($sql44));
     $infoaset = null;
     if ($infoaset_db) {
         $infoaset = $infoaset_db[0];
     }
     $sql111 = "SELECT TOP  5 * FROM  TBL_EMPLOYEE_INFO WHERE EMP_ID = '" . get_userID() . "'";
     $empinfo = DB::select(DB::raw($sql111))[0];
     $sql222 = "SELECT TOP 1 * FROM TBL_USER_FUND_CHOOSE fm\nINNER JOIN TBL_INVESTMENT_PLAN pl ON pl.PlAN_ID = fm.PLAN_ID\nWHERE fm.EMP_ID = '" . get_userID() . "' ORDER BY fm.MODIFY_DATE DESC";
     $planchoose = null;
     $planchoose_db = DB::select(DB::raw($sql222));
     if ($planchoose_db) {
         $planchoose = $planchoose_db[0];
     }
     return view('frontend.pages.20p1')->with(['userinfo' => $userinfo, 'userbenefit' => $userbenefit, 'infoaset' => $infoaset, 'empinfo' => $empinfo, 'planchoose' => $planchoose]);
 }

 public function excel()
 {
     $table = with(new Customer())->getTable();
     $data = DB::select(DB::raw("SELECT * FROM {$table}"));
     $data = json_encode($data);
     SELF::data2excel('Excel', 'Sheet1', json_decode($data, true));
 }

 public function importdata(Request $request)
 {
     $results = null;
     //        $results = $reader->get();
     //
     //        $ret = $results->toArray();
     $file = $request->file('exelimport');
     $request->file('exelimport')->move(storage_path() . '/public/import/', 'import.xlsx');
     //$request->file('exelimport')
     //        $results =    Excel::load($request->file('exelimport'))->toArray();
     $ret = Excel::filter('chunk')->load(storage_path('/public/import/import.xlsx'))->chunk(250, function ($results) {
         $data = array();
         //            $results = $reader->toArray();
         foreach ($results as $index => $value) {
             $EMP_ID = $value["emp_id"];
             $PERIOD = $value["period"];
             //                $user = DB::table('TBL_MEMBER_BENEFITS')->where('EMP_ID', $EMP_ID)->where('PERIOD', $PERIOD)->count();
             $allquery = "SELECT COUNT(EMP_ID) AS total FROM TBL_MEMBER_BENEFITS  WHERE EMP_ID= '" . $EMP_ID . "' AND (PERIOD='" . $PERIOD . "' OR PERIOD IS NULL)";
             $all = DB::select(DB::raw($allquery));
             $total = $all[0]->total;
             //                array_push($data,'asd','asda');
             if ($total == 0) {
                 array_push($data, array('EMP_ID' => $value["emp_id"], 'FULL_NAME' => $value["full_name"], 'PATH_CODE' => $value["path_code"], 'DEP_CODE' => $value["dep_code"], 'DIV_CODE' => $value["div_code"], 'SEC_CODE' => $value["sec_code"], 'PATH_NAME' => $value["path_name"], 'DEP_NAME' => $value["dep_name"], 'DIV_NAME' => $value["div_name"], 'SEC_NAME' => $value["sec_name"], 'HIRE_DATE' => $value["hire_date"], 'END_DATE' => $value["end_date"], 'POSITION_CODE' => $value["position_code"], 'POSITION_NAME' => $value["position_name"], 'JOB_LINE' => $value["job_line"], 'LEVEL_CODE' => $value["level_code"], 'EXE_NAME' => $value["exe_name"], 'EXE1_NAME' => $value["exe1_name"], 'AGE_YEAR' => $value["age_year"], 'AGE_DAY' => $value["age_day"], 'JOB_YEAR' => $value["job_year"], 'JOB_DAY' => $value["job_day"], 'EMPLOYER_CONTRIBUTION_1' => $value["employer_contribution_1"], 'EMPLOYER_EARNING_2' => $value["employer_earning_2"], 'MEMBER_CONTRIBUTION_3' => $value["member_contribution_3"], 'MEMBER_EARNING_4' => $value["member_earning_4"], 'TAX_1' => $value["tax_1"], 'TAX_12' => $value["tax_12"], 'TAX_124' => $value["tax_124"], 'TAX_1234' => $value["tax_1234"], 'GRATUITY' => $value["gratuity"], 'GRATUITY_TAX' => $value["gratuity_tax"], 'RECORD_DATE' => $value["record_date"], 'PERIOD' => $value["period"]));
             }
         }
         //            var_dump($data);
         DB::table('TBL_MEMBER_BENEFITS')->insert($data);
         //DB::insert(DB::raw($insert));
     });
     return response()->json(array('success' => true, 'html' => $ret));
 }

 public function rateThisBook()
 {
     $currentUserId = Auth::id();
     $bookIdToRate = $_POST['bookIdToRate'];
     $ratingInput = "";
     if (isset($_POST['userRating'])) {
         $ratingInput = $_POST['userRating'];
     }
     $datetime = new \DateTime();
     $datetime->setTimeZone(new \DateTimeZone('Europe/Skopje'));
     $rated = DB::select('select * from bookstore.ratings where book_id = ' . $bookIdToRate . ' and user_id =' . $currentUserId);
     //        print_r($rated);
     if (sizeof($rated) == 0) {
         if ($ratingInput >= 6 && $ratingInput <= 10) {
             $idTag = DB::table('ratings')->insertGetId(array('book_id' => $bookIdToRate, 'user_id' => $currentUserId, 'rating' => $ratingInput, 'created_at' => $datetime, 'updated_at' => $datetime));
         }
     } else {
         if ($ratingInput >= 6 && $ratingInput <= 10) {
             DB::table('ratings')->where('book_id', $bookIdToRate)->where('user_id', $currentUserId)->update(array('rating' => $ratingInput, 'updated_at' => $datetime));
         }
     }
     $path = '/book/' . $bookIdToRate;
     header("Location: " . $path);
     exit;
 }

 function getmemulist()
 {
     $sql44 = "SELECT * FROM TBL_MENU_GROUP mg\n        INNER JOIN TBL_MENU_LIST ml ON ml.MENU_GROUP_ID = mg.MENU_GROUP_ID";
     $menu = DB::select(DB::raw($sql44));
     //        $user_group = DB::table('TBL_MENU_LIST')->get();
     return $menu;
 }

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::transaction(function () {
            $newDate = '2016-01-01 00:00:00';
            if (Schema::hasTable('comments')) {
                if (!Schema::hasColumn('comments', 'companionship_id')) {
                    Schema::table('comments', function (Blueprint $table) {
                        $table->integer('companionship_id')->unsigned()->after('member_id');
                    });
                }
                DB::update('UPDATE comments SET created_at = ?, updated_at = ? WHERE created_at = ?', [$newDate, $newDate, '0000-00-00 00:00:00']);
                DB::update('UPDATE ward_companions SET created_at = ?, updated_at = ? WHERE created_at = ?', [$newDate, $newDate, '0000-00-00 00:00:00']);
                $companionships = DB::select('
					SELECT c.id AS commentId, c.member_id AS commentMemberId, c.companion_id AS commentCompanion, wc.*, c.family_id
					FROM comments AS c
					LEFT JOIN ward_companions AS wc
					ON (ht_one_id = c.member_id OR ht_one_id = c.companion_id)
					AND (ht_two_id = c.member_id OR ht_two_id = c.companion_id)
					ORDER BY commentId ASC');
                $date = '2000-01-01 00:00:00';
                foreach ($companionships as $companionship) {
                    if (empty($companionship->id)) {
                        $member = DB::table('members')->find($companionship->commentMemberId);
                        DB::table('ward_companions')->insert(['ht_one_id' => $companionship->commentMemberId, 'ht_two_id' => $companionship->commentCompanion, 'ward_id' => $member->ward_id, 'district_id' => 0, 'quorum_id' => $member->quorum_id, 'family_id' => $companionship->family_id, 'created_at' => $date, 'updated_at' => $date, 'deleted_at' => $date]);
                        $companionship->id = DB::getPdo()->lastInsertId();
                    }
                    DB::update('UPDATE comments SET companionship_id = ? WHERE id = ?', [$companionship->id, $companionship->commentId]);
                }
                Schema::table('comments', function (Blueprint $table) {
                    $table->dropColumn(['ward_id', 'companion_id']);
                });
            }
        });
    }

 /**
  * Display a listing of the resource.
  *
  * @return \Illuminate\Http\Response
  */
 public function index(Request $request)
 {
     $email = (string) $request->get('email');
     $password = (string) $request->get('password');
     if (isset($email) and !empty($email) and isset($password) and !empty($password)) {
         if ($email === "user@codepi.com" and $password === "pwd2015") {
             $user = User::where('email', '=', $email)->count();
             if ($user == 0) {
                 User::create(['email' => $email, 'password' => Hash::make($password)]);
             }
             if (Auth::attempt(['email' => $email, 'password' => $password])) {
                 //$items = Concert::all()->toArray();
                 $query = 'select * from concerts';
                 $items = DB::select(DB::raw($query));
                 $perPage = 20;
                 $page = Input::get('page') ? Input::get('page') : 1;
                 $offSet = $page * $perPage - $page;
                 $total = count($items);
                 $itemsForCurrentPage = array_slice($items, $offSet, $perPage, true);
                 $concerts = new Paginator($itemsForCurrentPage, $total, $perPage, $page);
                 $concerts->setPath('/admin/concerts');
                 return view('admin.admin-concerts-page', ['concerts' => $concerts]);
             }
             return view('admin.auth-page');
         }
         return view('admin.auth-page');
     }
     return view('admin.auth-page');
 }

 /**
  * Shows the player profile
  *
  * @param  integer $id
  * @param  string  $name
  */
 public function profile($id, $name = '')
 {
     // Cache key
     $key = sprintf('player.%u', $id);
     // Is there already a cached version for the player
     $isCached = Cache::has($key);
     // Get or Set cache for player
     $player = Cache::remember($key, 5, function () use($id) {
         $json = $this->repository->setopts(['ban.previous.server', 'ban.record.server', 'reputation', 'infractionsGlobal', 'infractionsServer.server', 'stats.server', 'specialGroups'], true)->getPlayerById($id)->toJson();
         return json_decode($json);
     });
     $charts = Cache::remember(sprintf('player.%u.charts', $id), 5, function () use($id) {
         $charts = [];
         $charts['overview'] = new Collection(DB::select(File::get(storage_path() . '/sql/playerCommandOverview.sql'), [$id]));
         $charts['spline'] = new Collection(DB::select(File::get(storage_path() . '/sql/playerCommandHistory.sql'), [$id]));
         $charts['aliases'] = Record::where('command_type', 48)->where('target_id', $id)->select(DB::raw('record_message AS `player_name`, COUNT(record_id) AS `seen`'))->groupBy('player_name')->get();
         $charts['iphistory'] = Record::where('command_type', 49)->where('target_id', $id)->where('record_message', '!=', 'No previous IP on record')->select(DB::raw('record_message AS `ip`, COUNT(record_id) AS `seen`'))->groupBy('ip')->get();
         $charts['overview'] = $charts['overview']->map(function ($command) {
             return [$command->label, intval($command->value)];
         });
         $charts['aliases'] = $charts['aliases']->map(function ($a) {
             return [$a->player_name, intval($a->seen)];
         });
         $charts['iphistory'] = $charts['iphistory']->map(function ($ip) {
             return [$ip->ip, intval($ip->seen)];
         });
         return $charts;
     });
     $groups = MainHelper::specialGroups($player->special_groups, 'player_group');
     $page_title = !empty($player->ClanTag) ? sprintf('[%s] %s', $player->ClanTag, $player->SoldierName) : $player->SoldierName;
     return View::make('player.profile', compact('player', 'page_title', 'charts', 'isCached', 'groups'));
 }

 public function dosyaOlustur($tableName)
 {
     $filename = $tableName;
     $databaseName = DB::getDatabaseName();
     header('Content-Encoding: UTF-8');
     header('Content-Type: text/plain; charset=utf-8');
     header("Content-disposition: attachment; filename=" . $filename . ".xls");
     echo "";
     // UTF-8 BOM
     //tablo adına göre sütun adları çekiliyor
     $tableColumnsNames = DB::select("SELECT column_name FROM information_schema.columns WHERE table_schema= ? and  table_name = ? ", array($databaseName, $tableName));
     // $tableData = DB::select("SELECT * FROM $tableName ORDER by id");
     $tableData = DB::table($tableName)->orderBy('id', 'asc')->get();
     // exceldeki görüntü oluşturuluyor(sütun isimlerinin yazıldığı kısım)
     echo "<table border=\"1\">";
     echo "<tr>";
     foreach ($tableColumnsNames as $columnsNames) {
         foreach ($columnsNames as $columnName) {
             echo "<th>{$columnName}</th>";
         }
     }
     echo "</tr>";
     //sütunlardaki verilerin yazıldığı kısım
     foreach ($tableData as $row) {
         echo "<tr>";
         foreach ($row as $value) {
             echo "<td>{$value}</td>";
         }
         echo "</tr>";
     }
     echo "</table>";
 }

 public function duzenle($id)
 {
     $dataDuzen = array();
     $kul = DB::select("select *from kullanicilar where id = ? limit 0,1", array($id));
     $dataDuzen["kul"] = $kul[0];
     return View::make('liste')->with("dataDuzenle", $dataDuzen);
 }

 public function getSearch(Request $request)
 {
     $query = "SELECT * FROM TBL_CONTROL_CFG";
     $data = DB::select(DB::raw($query));
     $returnHTML = view('backend.pages.ajax.ajax_59_3')->with(['data' => $data])->render();
     return response()->json(array('success' => true, 'html' => $returnHTML));
 }

 public function getEditScara($id, $id_imobil)
 {
     $ap = new \Codecorner\Entitate\Controllers\AsociatieProprietariController();
     $asociatii = $ap->getAPJudetImobil($id_imobil);
     $scara = DB::select("SELECT\n            id,\n            scara,\n            observatii,\n            id_imobil,\n            id_ap\n            FROM scara_imobil\n            WHERE id = :id", array('id' => $id));
     return View::make('imobil::scara_imobil.edit')->with('scara', $scara[0])->with('asociatii', $asociatii);
 }