PHP 学习之数据库基本操作
2018-08-19 本文已影响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
由于不知道怎么上传演示视频所以暂不上传了~~
