Community Learn Tools Library Leisure
search
English
Home > List of blog posts > 12月31日—laravel框架的使用查询构造器对数据库进行增删查改操作
Blogger Information
Blog 34
fans 0
comment 0
visits 22374
Special Recommendation
More>
Related recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
12月31日—laravel框架的使用查询构造器对数据库进行增删查改操作
曾龙宇
Original
642 people have browsed it

路由文件

  1. //用户添加路由
  2. Route::get('/user/add','UserController@add');
  3. Route::post('/user/store','UserController@store');
  4. Route::get('/user/index','UserController@index');
  5. Route::get('/user/edit/{id}','UserController@edit');
  6. Route::post('/user/save','UserController@save');
  7. Route::get('/user/delete/{id}','UserController@delete');

Controller文件

  1. <?php
  3. namespace App\Http\Controllers;
  5. use Illuminate\Http\Request;
  6. use Illuminate\Support\Facades\DB;
  8. class UserController extends Controller
  9. {
  10.     public function index(){
  11. //        $data = DB::table('person')->orderBy('id','desc')->get();
  12.         $data = DB::table('person')->select('id','name','age','email','phone')->get();
  13.         return view('person.list',['data'=>$data]);
  14.     }
  15.     public function add(){
  16.         return view('person.add');
  17.     }
  19.     public function store(Request $request){
  20.         $name = $request->input('name');
  21.         $age = $request->input('age');
  22.         $email = $request->input('email');
  23.         $phone = $request->input('phone');
  24.         $res = DB::table('person')->insert(['name'=>$name,'age'=>$age,'email'=>$email,'phone'=>$phone]);
  25.         if ($res){
  26.             echo json_encode(array('code'=>1,'msg'=>'添加成功'));
  27.         }else{
  28.             echo json_encode(array('code'=>0,'msg'=>'添加失败'));
  29.         }
  30.     }
  32.     public function edit($id){
  33.         $data = DB::table('person')->where('id',$id)->get();
  34.         return view('person.edit',['data'=>$data]);
  35.     }
  37.     public function save(Request $request){
  38.         $id = $request->input('id');
  39.         $name = $request->input('name');
  40.         $age = $request->input('age');
  41.         $email = $request->input('email');
  42.         $phone = $request->input('phone');
  43.         $res = DB::table('person')->where('id',$id)->update(['name'=>$name,'age'=>$age,'email'=>$email,'phone'=>$phone]);
  44.         if ($res){
  45.             echo json_encode(array('code'=>1,'msg'=>'编辑成功'));
  46.         }else{
  47.             echo json_encode(array('code'=>0,'msg'=>'编辑失败'));
  48.         }
  49.     }
  50.     public function delete($id){
  51.         $res = DB::table('person')->where('id',$id)->delete();
  52.         if ($res){
  53.             echo '<script>alert("删除成功");window.location.href="/user/index";</script>';
  54.         }else{
  55.             echo '<script>alert("删除失败");window.reload();</script>';
  56.         }
  57.     }
  58. }

list界面

  1. <!doctype html>
  2. <html lang="en">
  3. <head>
  4.     <meta charset="UTF-8">
  5.     <meta name="viewport"
  6.           content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
  7.     <meta http-equiv="X-UA-Compatible" content="ie=edge">
  8.     <title>展示界面</title>
  9.     <style>
  10.         a{text-decoration: none;}
  11.     </style>
  12. </head>
  13. <body>
  14. <button><a href="/user/add">添加</a></button>
  15. <table border="1" cellpadding="4" style="border-collapse: collapse;">
  16.     <caption>展示界面</caption>
  17.     <tr>
  18.         <th>ID</th>
  19.         <th>姓名</th>
  20.         <th>年龄</th>
  21.         <th>邮箱</th>
  22.         <th>电话</th>
  23.         <th>操作</th>
  24.     </tr>
  25.     @foreach($data as $item)
  26.     <tr>
  27.         <td>{{$item->id}}</td>
  28.         <td>{{$item->name}}</td>
  29.         <td>{{$item->age}}</td>
  30.         <td>{{$item->email}}</td>
  31.         <td>{{$item->phone}}</td>
  32.         <td><button><a href='/user/edit/{{$item->id}}'>编辑</a></button> 
  33.             <button><a href='/user/delete/{{$item->id}}' onclick="if(confirm('是否删除数据？')==false)return false;">删除</a></button>
  34.         </td>
  35.     </tr>
  36.     @endforeach
  37. </table>
  38. <script>
  40. </script>
  41. </body>
  42. </html>

add界面

  1. <!doctype html>
  2. <html lang="en">
  3. <head>
  4.     <meta charset="UTF-8">
  5.     <meta name="viewport"
  6.           content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
  7.     <meta http-equiv="X-UA-Compatible" content="ie=edge">
  8.     <title>添加界面</title>
  9.     <script src="{{asset('js/jquery.min.js')}}"></script>
  10. </head>
  11. <body>
  12. <form action="" name="add_form">
  13.     <table border="1" cellpadding="4" style="border-collapse:collapse;">
  14.         <input type="hidden" name="_token" value="{{csrf_token()}}">
  15.         <caption>添加用户信息</caption>
  16.         <tr>
  17.             <td>姓名</td>
  18.             <td><input type="text" name="name" placeholder="请输入姓名"></td>
  19.         </tr>
  20.         <tr>
  21.             <td>年龄</td>
  22.             <td><input type="text" name="age" placeholder="请输入年龄"></td>
  23.         </tr>
  24.         <tr>
  25.             <td>邮箱</td>
  26.             <td><input type="text" name="email" placeholder="请输入邮箱"></td>
  27.         </tr>
  28.         <tr>
  29.             <td>电话</td>
  30.             <td><input type="text" name="phone" placeholder="请输入电话"></td>
  31.         </tr>
  33.         <tr>
  34.             <td colspan="2"><button type="button" onclick="doSave()">保存</button></td>
  35.         </tr>
  36.     </table>
  37. </form>
  38. <script>
  39.     function doSave() {
  40.         var name = $.trim($('input[name="name"]').val());
  41.         var age = $.trim($('input[name="age"]').val());
  42.         var email = $.trim($('input[name="email"]').val());
  43.         var phone = $.trim($('input[name="phone"]').val());
  44.         if (name==''){
  45.             alert('请输入姓名');
  46.             return;
  47.         }
  48.         if (age==''){
  49.             alert('请输入年龄');
  50.             return;
  51.         }
  52.         if (email==''){
  53.             alert('请输入邮箱');
  54.             return;
  55.         }
  56.         if (phone==''){
  57.             alert('请输入电话');
  58.             return;
  59.         }
  60.         $.post('/user/store',$('form[name="add_form"]').serialize(),function (res) {
  61.             if(res.code==0){
  62.                 alert(res.msg);
  63.                 return;
  64.             }else{
  65.                 alert(res.msg);
  66.                 window.location.href = '/user/index';
  67.             }
  68.         },'json');
  69.     }
  70. </script>
  71. </body>
  72. </html>

edit界面

  1. <!doctype html>
  2. <html lang="en">
  3. <head>
  4.     <meta charset="UTF-8">
  5.     <meta name="viewport"
  6.           content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
  7.     <meta http-equiv="X-UA-Compatible" content="ie=edge">
  8.     <title>编辑界面</title>
  9.     <script src="{{asset('js/jquery.min.js')}}"></script>
  10. </head>
  11. <body>
  12. <form action="" name="add_form">
  13.     <table border="1" cellpadding="4" style="border-collapse:collapse;">
  14.         <input type="hidden" name="_token" value="{{csrf_token()}}">
  15.         @foreach ($data as $item)
  17.         <input type="hidden" name="id" value="{{$item->id}}">
  18.         <caption>编辑用户信息</caption>
  19.             <tr>
  20.                 <td>姓名</td>
  21.                 <td><input type="text" name="name" value="{{$item->name}}"></td>
  22.             </tr>
  23.             <tr>
  24.                 <td>年龄</td>
  25.                 <td><input type="text" name="age" value="{{$item->age}}"></td>
  26.             </tr>
  27.             <tr>
  28.                 <td>邮箱</td>
  29.                 <td><input type="text" name="email" value="{{$item->email}}"></td>
  30.             </tr>
  31.             <tr>
  32.                 <td>电话</td>
  33.                 <td><input type="text" name="phone" value="{{$item->phone}}"></td>
  34.             </tr>
  36.             <tr>
  37.             <td colspan="2"><button type="button" onclick="doSave()">保存</button></td>
  38.         </tr>
  39.         @endforeach
  40.     </table>
  41. </form>
  42. <script>
  43.     function doSave() {
  44.         var name = $.trim($('input[name="name"]').val());
  45.         var age = $.trim($('input[name="age"]').val());
  46.         var email = $.trim($('input[name="email"]').val());
  47.         var phone = $.trim($('input[name="phone"]').val());
  48.         if (name==''){
  49.             alert('请输入姓名');
  50.             return;
  51.         }
  52.         if (age==''){
  53.             alert('请输入年龄');
  54.             return;
  55.         }
  56.         if (email==''){
  57.             alert('请输入邮箱');
  58.             return;
  59.         }
  60.         if (phone==''){
  61.             alert('请输入电话');
  62.             return;
  63.         }
  64.         $.post('/user/save',$('form[name="add_form"]').serialize(),function (res) {
  65.             if(res.code==0){
  66.                 alert(res.msg);
  67.                 return;
  68.             }else{
  69.                 alert(res.msg);
  70.                 window.location.href = '/user/index';
  71.             }
  72.         },'json');
  73.     }
  74. </script>
  75. </body>
  76. </html>

where方法

传递三个参数，第一个参数是列名，第二个参数是任意一个数据库系统支持的运算符，第三个是该列要比较的值。例如：where(‘id’,’=’,’1’)

  1. $data = DB::table('person')->where('id','=','3')->get();

whereIn方法

验证字段的值必须存在指定的数组里,两个参数，第一个参数是列名，第二个参数是数组，例如：whereIn(‘id’,[1,3,5])

  1. $data = Db::table('person')->whereIn('id',[1,3,4])->get();

insert方法，返回是否插入成功，布尔值

插入记录到数据库中，接收数组形式的字段名和字段值进行插入操作。insert中可以接收一个数组，也可以接收多个数组
例如：insert([‘name’=>’张三’,’age’=>’24’])
insert([‘name’=>’张三’,’age’=>’24’],[‘name’=>’李四’,’age’=>’45’],[‘name’=>’王五’,’age’=>’68’])

  1. $res = DB::table('person')->insert(['name'=>'小明','age'=>'26','email'=>'qwe@qq.com','phone'=>'10086']);
  3. $res = DB::table('person')->insert(
  4. ['name'=>'小明','age'=>'26','email'=>'qwe@qq.com','phone'=>'10086'],['name'=>'小军','age'=>'32','email'=>'jun@qq.com','phone'=>'10086'],['name'=>'小红','age'=>'45','email'=>'hon@qq.com','phone'=>'10086']);

insertGetId，插入记录并返回id值

  1. $res = DB::table('person')->insertGetId(['name'=>'小明','age'=>'26','email'=>'qwe@qq.com','phone'=>'10086']);

update更新方法

通过where增加更新条件，update里面用数组形式传值

  1. $res = DB::table('person')->where('id',$id)->update(['name'=>$name,'age'=>$age,'email'=>$email,'phone'=>$phone]);

increment自增

第一个参数：需要修改的列。第二个参数是可选的，用于控制列递增的量
通常用在计算登录次数，点赞数等
$res = Db::table(‘person’)->increment(‘login_num’);

delete删除方法

  1. $res = DB::table('person')->where('id',$id)->delete();

truncate清空表方法

删除所有行，并重置自增 ID 为零

  1. $res = DB::table('person')->truncate()
Correcting teacher：天蓬老师天蓬老师

Correction status：qualified

Teacher's comments：laravel 的数据库操作有三种方式, 在开发过程中, 根据情况, 任选 一个都可以的
Statement of this Website
The copyright of this blog article belongs to the blogger. Please specify the address when reprinting! If there is any infringement or violation of the law, please contact admin@php.cn Report processing!
All comments Speak rationally on civilized internet, please comply with News Comment Service Agreement
0 comments
Author's latest blog post