search

博客列表 >12月31日—laravel框架的使用查询构造器对数据库进行增删查改操作

12月31日—laravel框架的使用查询构造器对数据库进行增删查改操作

曾龙宇
曾龙宇原创
2020年01月02日 10:48:06641浏览

路由文件

  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()
上一条：二手商品交易和评论与回复的组件（1226前端开发学习）下一条：使用flex完成PHP中文网（移动端）的布局--PHP线上培训第十期1227
声明：本文内容转载自脚本之家，由网友自发贡献，版权归原作者所有，如您发现涉嫌抄袭侵权，请联系admin@php.cn 核实处理。
全部评论
文明上网理性发言，请遵守新闻评论服务协议

相关文章

查看更多