PHP DB::table方法代码示例

 public function mostrarPost($page = 1)
 {
     // traemos la cantidad de registros para el paginador
     $registros = DB::table('post')->join('temas', 'post_tema', '=', 'temas.tema_id')->where('post_tipo', 'ENTRADA')->orderBy('post_fec', 'desc')->get();
     $totalRegistros = count($registros);
     //die("totalRegistros ".$totalRegistros); //16
     $paginador = 15;
     // Parametrizarlo desde DB
     $cantPagina = $totalRegistros / $paginador;
     //die("cantPagina ".$cantPagina); //1.066
     $cantPagina = ceil($cantPagina);
     //die("cantidad de paginas ".$cantPagina); //2
     if ($page != 1) {
         $comienzo = $page * $paginador - $paginador + 1;
         // ((2*15)-15+1);
         // (30)-14
         // comenzara en la 16, pagina 2 y asi sucesivamente..
     } else {
         $comienzo = 1;
     }
     $final = $page * $paginador;
     $post = DB::table('post')->join('temas', 'post_tema', '=', 'temas.tema_id')->where('post_tipo', 'ENTRADA')->orderBy('post_fec', 'desc')->skip($comienzo - 1)->take($final)->get();
     //
     if (count($post) < 1) {
         $error = "No hay más resultados para mostrar.";
     } else {
         $error = "";
     }
     $tema = DB::table('post as p')->select('tm.tema_txt', 'tm.tema_img')->distinct()->join('usuarios as u', 'p.post_usu', '=', 'u.usuarios_id')->join('temas as tm', 'p.post_tema', '=', 'tm.tema_id')->orderBy('tm.tema_txt', 'asc')->get();
     // Retornamos todos los datos
     $entradas = DB::table('post as p')->select('p.*', 'u.usuarios_name', 'tm.tema_txt')->join('usuarios as u', 'p.post_usu', '=', 'u.usuarios_id')->join('temas as tm', 'p.post_tema', '=', 'tm.tema_id')->orderBy('p.post_fec', 'desc')->get();
     return View::make('index', array('post' => $post, 'temas' => $tema, 'cantidadPag' => $cantPagina, 'errores' => $error, 'entradas' => $entradas));
 }

 public function getLoggedinDashboard()
 {
     $role = Auth::user()->role;
     if ($role == 'hotel-staff') {
         $branch_code = $this->getStaffBranch();
         $data_room_booked_details = DB::table('hotel_rooms')->join('branch', 'hotel_rooms.branch_code', '=', 'branch.branch_code')->where('branch.branch_code', '=', $branch_code)->where('hotel_rooms.status', '=', 'booked')->count();
         $data_room_details = DB::table('hotel_rooms')->join('branch', 'hotel_rooms.branch_code', '=', 'branch.branch_code')->where('branch.branch_code', '=', $branch_code)->where('hotel_rooms.status', '=', 'available')->count();
         $data_total_sales = DB::table('sales')->where('branch_code', '=', $branch_code)->sum('sale_value');
         $total_client = DB::table('customer')->join('accommodation', 'customer.customer_id', '=', 'accommodation.customer_id')->select('customer.customer_id', 'accommodation.branch_code')->where('accommodation.branch_code', '=', $branch_code)->count();
         $data_room = DB::table('hotel_rooms')->join('branch', 'hotel_rooms.branch_code', '=', 'branch.branch_code')->select('hotel_rooms.room_code', 'hotel_rooms.branch_code', 'hotel_rooms.price_per_night', 'hotel_rooms.description', 'hotel_rooms.status', 'hotel_rooms.image', 'branch.branch_name', 'branch.address', 'branch.email', 'branch.city')->where('branch.branch_code', '=', $branch_code)->where('hotel_rooms.status', '=', 'available')->take(5)->get();
         $data_room_booked = DB::table('accommodation')->join('customer', 'accommodation.customer_id', '=', 'customer.customer_id')->select('customer.fullname', 'accommodation.branch_code', 'accommodation.room_code', 'accommodation.checkin_time', 'accommodation.checkout_time')->where('accommodation.branch_code', '=', $branch_code)->orderBy('checkout_time')->take(5)->get();
         $cancelled = DB::table('refund')->where('date', '=', date('y-m-d'))->where('branch_id', '=', $branch_code)->count('refund_id');
     } else {
         DB::setFetchMode(PDO::FETCH_ASSOC);
         $company_id_logged_user = Auth::user()->comp_id;
         $data_room_details = DB::table('hotel_rooms')->join('branch', 'hotel_rooms.branch_code', '=', 'branch.branch_code')->where('branch.company_id', '=', $company_id_logged_user)->where('hotel_rooms.status', '=', 'available')->count();
         $data_room_booked_details = DB::table('hotel_rooms')->join('branch', 'hotel_rooms.branch_code', '=', 'branch.branch_code')->where('branch.company_id', '=', $company_id_logged_user)->where('hotel_rooms.status', '=', 'booked')->count();
         $data_total_sales = DB::table('sales')->where('company_id', '=', Auth::user()->comp_id)->sum('sale_value');
         $total_client = DB::table('customer')->select('customer_id')->where('company_id', '=', Auth::user()->comp_id)->count();
         $company_id_logged_user = Auth::user()->comp_id;
         $data_room = DB::table('hotel_rooms')->join('branch', 'hotel_rooms.branch_code', '=', 'branch.branch_code')->select('hotel_rooms.room_code', 'hotel_rooms.branch_code', 'hotel_rooms.price_per_night', 'hotel_rooms.description', 'hotel_rooms.status', 'hotel_rooms.image', 'branch.branch_name', 'branch.address', 'branch.email', 'branch.city')->where('branch.company_id', '=', $company_id_logged_user)->where('hotel_rooms.status', '=', 'available')->take(5)->get();
         $data_room_booked = DB::table('accommodation')->join('customer', 'accommodation.customer_id', '=', 'customer.customer_id')->select('customer.fullname', 'accommodation.branch_code', 'accommodation.room_code', 'accommodation.checkin_time', 'accommodation.checkout_time')->where('accommodation.comp_id', '=', Auth::user()->comp_id)->orderBy('checkout_time')->take(5)->get();
         $cancelled = DB::table('refund')->join('branch', 'refund.branch_id', '=', 'branch.branch_code')->where('branch.company_id', '=', Auth::user()->comp_id)->count('refund_id');
     }
     return View::make('dashboard', array('data' => $data_room_details, 'booked' => $data_room_booked_details, 'sales' => $data_total_sales, 'clients' => $total_client, 'room_details' => $data_room, 'room_booked' => $data_room_booked, 'cancelled' => $cancelled));
 }

 /**
  * Removes given title from given list of user.
  *
  * @param  array $input 
  * @return  String/Redirect
  */
 public function remove(array $input)
 {
     if ($user = Sentry::getUser()) {
         $this->db->table('users_titles')->where('title_id', $input['title_id'])->where('user_id', $user->id)->where($input['list_name'], 1)->delete();
         //fire eloquent deleted event so cache is flushed
         Event::fire('eloquent.deleted: *', new Title());
     }
 }

    public function dologin()
    {
        $rules = array('username' => 'required', 'password' => 'required');
        $message = array('required' => 'Data :attribute harus diisi', 'min' => 'Data :attribute minimal diisi :min karakter');
        $validator = Validator::make(Input::all(), $rules, $message);
        if ($validator->fails()) {
            return Redirect::to('/')->withErrors($validator)->withInput(Input::except('password'));
        } else {
            $data = array('username' => Input::get('username'), 'password' => Input::get('password'));
            if (Auth::attempt($data)) {
                $data = DB::table('user')->select('user_id', 'level_user', 'username')->where('username', '=', Input::get('username'))->first();
                //print_r($data);
                //echo $data->id_users;
                Session::put('user_id', $data->user_id);
                Session::put('level', $data->level_user);
                Session::put('username', $data->username);
                //print_r(Session::all());
                return Redirect::to("/admin/beranda");
            } else {
                Session::flash('messages', '
					<div class="alert alert-danger alert-dismissable" >
                    		<button type="button" class="close" data-dismiss="alert" aria-hidden="true">×</button>
                    		<strong>Peringatan...</strong><br>
                    			Username dan password belum terdaftar pada sistem !
                    		</div>
				');
                return Redirect::to('/')->withInput(Input::except('password'));
            }
        }
    }

 public function run()
 {
     // Kommentera denna för att inte radera all data i tabellen
     DB::table('orders')->delete();
     $orders = array(['id' => 1, 'roomid' => 2, 'arrives' => '2015-08-03', 'departures' => '2015-08-10', 'name' => 'Förnamn Efternamn', 'phone' => '0000000000', 'created_at' => new DateTime(), 'updated_at' => new DateTime()], ['id' => 2, 'roomid' => 1, 'arrives' => '2015-08-08', 'departures' => '2015-08-12', 'name' => 'Förnamn Efternamn', 'phone' => '0000000000', 'created_at' => new DateTime(), 'updated_at' => new DateTime()]);
     DB::table('orders')->insert($orders);
 }

 /**
  * Run the database seeds.
  *
  * @return void
  */
 public function run()
 {
     $faker = Faker\Factory::create();
     foreach (range(1, 200) as $index) {
         DB::table('parts')->insert(['description' => $faker->realText(100), 'cost' => $faker->randomFloat(2, 1, 1000)]);
     }
 }

 public function setUserEntityTheme($file, $dataarr)
 {
     $getpersonalcount = DB::table('tbl_bankaccounts')->leftjoin('tbl_bankbranches', 'bankaccount_branch', '=', 'branch_id')->leftjoin('tbl_banks', 'branch_bankid', '=', 'bank_id')->where('bank_isproduct', '0')->where('bankaccount_userentity', 'Personal')->where('bankaccount_createdby', Auth::user()->id)->count();
     $getbusinesscount = DB::table('tbl_bankaccounts')->leftjoin('tbl_bankbranches', 'bankaccount_branch', '=', 'branch_id')->leftjoin('tbl_banks', 'branch_bankid', '=', 'bank_id')->where('bank_isproduct', '0')->where('bankaccount_userentity', 'Business')->where('bankaccount_createdby', Auth::user()->id)->count();
     $resultbViewAcctype = DB::table('tbl_bankaccounttypes')->get();
     $resultbViewAcctypearr = array();
     foreach ($resultbViewAcctype as $data) {
         $resultbViewAcctypearr[$data->accounttype_id] = $data->accounttype_name;
     }
     $resultbViewBanks = DB::table('tbl_banks')->where('bank_isproduct', 0)->where('bank_status', '1')->orderBy('bank_name', 'ASC')->get();
     $resultbViewBanksarr = array();
     foreach ($resultbViewBanks as $data) {
         $resultbViewBanksarr[$data->bank_id] = $data->bank_name;
     }
     $resultbViewBankbranchs = DB::table('tbl_bankbranches')->where("branch_bankid", key($resultbViewBanksarr))->where("branch_status", "1")->get();
     $resultbViewBankBrancharr = array();
     foreach ($resultbViewBankbranchs as $data) {
         $resultbViewBankBrancharr[$data->branch_id] = $data->branch_name;
     }
     if ($getpersonalcount <= 0 and $getbusinesscount <= 0) {
         $data = array('bankaccttype' => $resultbViewAcctypearr, 'bankname' => $resultbViewBanksarr, 'bankbranch' => $resultbViewBankBrancharr);
         $MyTheme = Theme::uses('fonebayad')->layout('ezibills_9_0');
         return $MyTheme->of('registration.firstloginaddbankacct', $data)->render();
     } else {
         //            $MyTheme = Theme::uses('fonebayad')->layout('ezibills_9_0');
         $MyTheme = Theme::uses('fonebayad')->layout('newDefault_myBills');
         return $MyTheme->of($file, $dataarr)->render();
     }
 }

 /**
  * Run the database seeds.
  *
  * @return void
  */
 public function run()
 {
     $tags = array(['name' => 'environment', 'created_at' => date('Y-m-d H:i:s'), 'updated_at' => date('Y-m-d H:i:s')], ['name' => 'government', 'created_at' => date('Y-m-d H:i:s'), 'updated_at' => date('Y-m-d H:i:s')]);
     // Uncomment the below to run the seeder
     DB::table('tags')->insert($tags);
     factory(App\Models\Tag::class, 10)->create();
 }

 /**
  * Run the database seeds.
  *
  * @return void
  */
 public function run()
 {
     Model::unguard();
     DB::statement('SET FOREIGN_KEY_CHECKS = 0');
     DB::table('cijfer')->truncate();
     $toetsen = Toets::all();
     //$cijfers = factory(Cijfer::class, count($toetsen)*count($leerlingen))->create();
     for ($i = 0; $i < count($toetsen); ++$i) {
         //dd($toetsen[$i]->toetsenlijst()->first()->lesopdracht()->first()->klas()->first()->leerlingen()->first());
         $klas = $toetsen[$i]->toetsenlijst()->first()->lesopdracht()->first()->klas()->first()->code;
         if (substr($klas, 0, 1) != '5') {
             continue;
         }
         $leerlingen = $toetsen[$i]->toetsenlijst()->first()->lesopdracht()->first()->klas()->first()->leerlingen()->get();
         for ($j = 0; $j < count($leerlingen); ++$j) {
             $cijfer = factory(Cijfer::class, 1)->create();
             $cijfer["waarde"] = rand(0, 10);
             $cijfer["toets_id"] = $toetsen[$i]["id"];
             $cijfer["leerling_id"] = $leerlingen[$j]["id"];
             $cijfer->save();
         }
     }
     DB::statement('SET FOREIGN_KEY_CHECKS = 1');
     Model::reguard();
 }

 public function run()
 {
     // DELETING TABLE ENTRIES
     DB::table($this->_table)->delete();
     // CONFERENCE PARTNERS
     DB::table($this->_table)->insert([['company' => 'TAGITM', 'slug' => 'tagitm', 'website' => 'http://www.tagitm.org', 'slogan' => '', 'tags' => '2015/austin', 'photo' => '/images/partners/tagitm.png', 'published' => Carbon::create(2015, 00, 28, 15, 05, 29)]]);
 }

 /**
  * Run the database seeds.
  *
  * @return void
  */
 public function run()
 {
     $activities = ['technology', 'service', 'agribusiness', 'industry', 'tourism', 'hobby', 'association', 'institution'];
     foreach ($activities as $activity) {
         DB::table('activities')->insert(['name' => $activity]);
     }
 }

 public function run()
 {
     $faker = Faker::create();
     for ($i = 0; $i < 50; $i++) {
         \DB::table('states')->insert(array('state' => $faker->unique()->state, 'code' => $faker->postcode));
     }
 }

 static function getCountries()
 {
     $countries = DB::table('countries')->lists('name', 'code');
     //$provinces   = DB::table('provinces')->lists('name','code','ID')->where('country','=','IN')->get();
     //$provinces   = Provinces::where('country','=',$countryCode)->lists('name','ID as id');
     return $countries;
 }

 /**
  * Run the database seeds.
  *
  * @return void
  */
 public function run()
 {
     DB::table('tbl_users')->truncate();
     $faker = \Faker\Factory::create();
     for ($i = 0; $i < 10; $i++) {
         switch (mt_rand(1, 4)) {
             case 1:
                 $insertion = 'van';
                 break;
             case 2:
                 $insertion = 'de';
                 break;
             case 3:
                 $insertion = 'van der';
                 break;
             default:
                 $insertion = '';
         }
         $city2 = "";
         $street2 = "";
         $house_nr2 = "";
         $postalcode2 = "";
         if (mt_rand(1, 2) == 1) {
             $street2 = $faker->streetName;
             $house_nr2 = $faker->numberBetween(0, 2000);
             $postalcode2 = $faker->postcode;
             $city2 = $faker->city;
         }
         \App\User::create(['username' => $faker->userName, 'password' => password_hash('password', PASSWORD_DEFAULT), 'email' => $faker->email, 'firstname' => $faker->firstName, 'lastname' => $faker->lastName, 'insertion' => $insertion, 'phone_nr' => $faker->phoneNumber, 'birthdate' => $faker->date($format = 'Y-m-d', $max = 'now') . " " . $faker->time($format = 'H:i:s', $max = 'now'), 'city' => $faker->city, 'street' => $faker->streetName, 'house_nr' => $faker->numberBetween(0, 2000), 'postalcode' => $faker->postcode, 'city2' => $city2, 'street2' => $street2, 'house_nr2' => $house_nr2, 'postalcode2' => $postalcode2]);
     }
 }

 public function run()
 {
     DB::table('roles')->delete();
     $role = new Role();
     $role->name = 'edit_site';
     $role->description = 'Editar Sitio';
     $role->save();
     $role = new Role();
     $role->name = 'crud_user';
     $role->description = 'Manejo Usuarios';
     $role->save();
     $role = new Role();
     $role->name = 'crud_organization';
     $role->description = 'Crear Muestras';
     $role->save();
     $role = new Role();
     $userRole = new UserRole();
     $userRole->user_id = 1;
     $userRole->role_id = 1;
     $userRole->save();
     $userRole = new UserRole();
     $userRole->user_id = 1;
     $userRole->role_id = 2;
     $userRole->save();
     $userRole = new UserRole();
     $userRole->user_id = 2;
     $userRole->role_id = 3;
     $userRole->save();
     $userRole = new UserRole();
     $userRole->user_id = 2;
     $userRole->role_id = 2;
     $userRole->save();
 }