PHP 学习之数据库基本操作

作者: 唐朝早晨 | 来源:发表于2018-08-19 23:08 被阅读0次

写在前面，本章通过Laravel框架实现一个简单的增删改查操作

数据库连接设置：

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=valet
DB_USERNAME=root
DB_PASSWORD=root

创建数据库迁移文件：

php artisan make:migration _create_users_table

编辑迁移生成文件：database\migrations\2018_08_19_112230_create_users_table.php

public function up()
    {   //创建users表
        Schema::create('users', function (Blueprint $table) {
            //increments创建一个integer类型自增id
            $table->increments('id');
            //string创建一个name字段用户保存用户名
            $table->string('name');
            //string创建一个email字段，unique指定该字段的唯一用于保存用户邮箱
            $table->string('email')->unique();
            //string创建一个password字段设定最大长度为16
            $table->string('password',16);
            //timestamps创建一个create_at和一个update_at字段分别用于保存用户的创建和更新时间
            $table->timestamps();
        });
    }
public function down()
    {
        //执行回滚时删除users表
        Schema::dropIfExists('users');
    }

执行迁移

php artisan migrate

数据库表：

users表

创建资源控制器：

php artisan make:controller UsersController --resource

路由：

Route::resource('users','UsersController');

基本页面：resources\views\layouts\default.blade.php

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <title>@yield('title','default')</title>
    <link rel="stylesheet" type="text/css" media="screen" href="" />
</head>
<body>
    <div>
    @yield('centent')
    </div>  
</body>
</html>

信息提示：resources\views\layouts_messages.blade.php

@foreach (['danger', 'warning', 'success', 'info'] as $msg)
  @if(session()->has($msg))
    <div class="flash-message">
      <p class="alert alert-{{ $msg }}">
        {{ session()->get($msg) }}
      </p>
    </div>
  @endif
@endforeach

错误信息提示：resources\views\layouts_errors.blade.php

@if ($errors->any())
    <div class="alert alert-danger">
        <ul>
            @foreach ($errors->all() as $error)
                <li>{{ $error }}</li>
            @endforeach
        </ul>
    </div>
@endif

用户登录界面：resources\views\users\login.blade.php

@extends('layouts.default')
@section('title','登陆')

@section('centent')
    <form action="{{ route('users.store')}}" method = "post">
    
    {{ csrf_field()}}
        <table align = "center">
        
            <tr>
                <td colspan=2 align = "center">
                @include('layouts._errors')
                @include('layouts._messages')
                登 陆</td>
            </tr>
            <tr>
                <td>邮 箱：</td>
                <td><input type ="text" name = "email" value = "{{old('email')}}"/></td>
            </tr>
            <tr>
                <td>密 码：</td>
                <td><input type ="password" name = "password"/></td>
            </tr>
            <tr>
                <td align = "center">
                    <input type = "submit" value = "登陆"/>
                </td>
                <td>
                    <p>没有用户？现在就<a href="{{ route('users.create')}}">注册</a>吧</p>
                </td>
            </tr>
        </table>
    </form>
@stop

用户注册界面：resources\views\users\sgin_up.blade.php

@extends('layouts.default')
@section('title','注册')

@section('centent')
    <form action="{{ route('users.sginup')}}" method = "post">
    
    {{ csrf_field()}}
        <table align = "center">
        
        
            <tr>
                <td colspan=2 align = "center">@include('layouts._messages')@include('layouts._errors')注 册</td>
            </tr>
            <tr>
                <td>邮 箱：</td>
                <td><input type ="text" name = "email" value = "{{old('email')}}"/></td>
            </tr>
            <tr>
                <td>用户名：</td>
                <td><input type ="text" name = "name" value = "{{old('name')}}"/></td>
            </tr>
            <tr>
                <td>密 码：</td>
                <td><input type ="password" name = "password"/></td>
            </tr>
            <tr>
                <td>确认密码：</td>
                <td><input type ="password" name = "password_confirmation"/></td>
            </tr>
            <tr>
                <td align = "center" colspan = 4>
                    <input type = "submit" value = "注册"/>
                </td>
            </tr>
        </table>
    </form>
@stop

用户修改页面：resources\views\users\modify.blade.php

@extends('layouts.default')
@section('title','修改')

@section('centent')
    <form action="{{ route('users.update',$id)}}" method = "post">
    {{ method_field('put')}}
    {{ csrf_field()}}
        <table align = "center">
        
            <tr>

                <td colspan=2 align = "center">@include('layouts._errors')修 改</td>
            </tr>
            <tr>
                <td>邮 箱：</td>
                <td>{{ $email}}</td>
            </tr>
            <tr>
                <td>用户名：</td>
                <td><input type ="text" name = "name" value = "{{$name}}"/></td>
            </tr>
            <tr>
                <td>密 码：</td>
                <td><input type ="password" name = "password"/></td>
            </tr>
            <tr>
                <td>确认密码：</td>
                <td><input type ="password" name = "password_confirmation"/></td>
            </tr>
            <tr>
                <td align = "center" colspan = 4>
                    <input type = "submit" value = "修改"/>
                </td>
            </tr>
        </table>
    </form>
@stop

显示所有用户：resources\views\users\show_all.blade.php

@extends('layouts.default')
@section('title','所有用户')

@section('centent')
        <table align = "center">
            <tr>
                <td colspan=4 align = "center"> @include('layouts._messages')所有用户</td>
            </tr>
            <tr>
                <th>id</th>
                <th>用户名</th>
                <th>邮箱</th>
                <th>操作</th>
            </tr>
            @foreach($users as $user)
            <tr>
                <td>{{$user -> id}}</td>
                <td>{{$user -> name}}</td>
                <td>{{$user -> email}}</td>
                <td>
                    
                    <form method="POST" action = "{{ route('users.destroy',$user -> id)}}">
                        {{csrf_field()}}
                        {{ method_field('delete')}}
                        <a href = "{{ route('users.edit',$user -> id)}}">修改</a>
                        <input type = "submit" value = "删除"/>
                    </form>
                </td>
            </tr>
            @endforeach
        </table>  
@stop

路由：routes\web.php

Route::resource('users','UsersController');
Route::post('sginup','UsersController@sginUp') -> name('users.sginup');
Route::get('showAll','UsersController@showAll') -> name('user.showAll');

控制器：app\Http\Controllers\UsersController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class UsersController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
 
    public function index()
    {
        //返回用户登录界面
        return view('users.login');
    }
    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        //返回用户登录界面
        return view('users.sgin_up');
    }
    /**
     * 
     * 用户注册
     */
    public function sginUp(Request $request){
        //验证规则
        $validatedData = $request->validate([
            //姓名不能为空
            'name' => 'required',
            //邮箱格式为Email不能为空最大255
            'email' => 'required|email|max:255',
            //两次密码一样不能为空最大16
            'password' => 'required|confirmed|max:16',
        ]);
        //从请求中获取email
        $email = $request -> input('email');
        //查询该email在数据库中是否存在
        $email = DB::select("select * from users where email = ?",[$email]);
        //如果为空则进行注册
        if(empty($email)){
            DB::insert("insert into users ( name,email,password) values (?,?,?)",[$request -> input('name'),$request -> input('email'),$request -> input('password')]);
            session() -> flash('success','注册成功');
            return redirect() -> action("UsersController@index");
        }else{
            session() -> flash('warning','邮箱已经存在');
            return back();
        }
    }
    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        //验证规则
        $validatedData = $request->validate([
            //邮箱格式为Email不能为空最大255
            'email' => 'required|email|max:255',
            //密码不能为空最大16
            'password' => 'required|max:16',
        ]);
        //从请求中获取email
        $email = $request -> input('email');
        //查询该email在数据库中是否存在
        $email1 = DB::select("select * from users where email = ?",[$email]);
        //从请求中获取password
        $password = $request -> input('password');
        //通过Email查询密码
        $password1 = DB::select("select password from users where password = ?",[$email]);
        //取出查询到的密码
        foreach($password1 as $key){
            $password1 =  $key -> password;
        }
        //Email存在并且密码匹配则登陆成功
        if((!empty($email1))&&($password == $password1)){
            session() -> flash('success','登陆成功');
            return redirect() ->route('user.showAll');
        }else{
            session() -> flash('warning','用户名或密码输入错误');
            return back();
        }

    }
    /**
     * 显示所有用户
     */
    public function showAll(){
        $users  = DB::select("select * from users");
        return view('users.show_all',['users'=>$users]);
    }

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
        //
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function edit($id)
    {
        //通过id查相应用户
        $user = DB::select("select * from users where id = ?",[$id]);
        //去出该用户的信息
        foreach($user as $u){
           $email = $u -> email;
           $name = $u -> name;
        }
        //带参传入修改页面
        return view("users.modify",compact('email','name','id'));
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
        //验证规则
        $validatedData = $request->validate([
            'name' => 'required',
            'password' => 'required|confirmed|max:16',
        ]);
        //更新用户操作
        DB::update("update users set name = ?,password =? where id = ?",[$request -> input('name'),$request -> input('password'),$id]);
            session() -> flash('success','修改成功');
            return redirect() ->route('user.showAll');
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function destroy($id)
    {
        //删除用户操作
        DB::delete('delete from users where id = ?',[$id]);
        session() -> flash('success','删除成功');
        return redirect() ->route('user.showAll');
    }
}

浏览器访问：http://localhost:8000/users
由于不知道怎么上传演示视频所以暂不上传了~~

PHP 学习之数据库基本操作

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读