基于数据库的增删改查
查询数据
<?php
// 1. 建立连接
$conn = mysqli_connect ( 'localhost' , 'root' , '123456' , 'test' ) ;
mysqli_set_charset ( $conn , 'utf8' ) ;
if ( ! $conn ) {
exit ( '<h1>连接数据库失败</h1>' ) ;
}
// 2. 开始查询
$query = mysqli_query ( $conn , 'select * from users;' ) ;
if ( ! $query ) {
exit ( '<h1>查询数据失败</h1>' ) ;
}
// 3. 遍历结果集
// while ($item = mysqli_fetch_assoc($query)) {
// $data[] = $item;
// }
? >
< ! DOCTYPE html>
< html lang= "en" >
< head>
< meta charset= "UTF-8" >
< title> XXX 管理系统< / title>
< link rel= "stylesheet" href= "assets/css/bootstrap.css" >
< link rel= "stylesheet" href= "assets/css/style.css" >
< / head>
< body>
< nav class = "navbar navbar-expand navbar-dark bg-dark fixed-top" >
< a class = "navbar-brand" href= "#" > XXX 管理系统< / a>
< ul class = "navbar-nav mr-auto" >
< li class = "nav-item active" >
< a class = "nav-link" href= "index.html" > 用户管理< / a>
< / li>
< li class = "nav-item" >
< a class = "nav-link" href= "#" > 商品管理< / a>
< / li>
< / ul>
< / nav>
< main class = "container" >
< h1 class = "heading" > 用户管理 < a class = "btn btn-link btn-sm" href= "add.php" > 添加< / a> < / h1>
< table class = "table table-hover" >
< thead>
< tr>
< th> #</th>
< th> 头像< / th>
< th> 姓名< / th>
< th> 性别< / th>
< th> 年龄< / th>
< th class = "text-center" width= "140" > 操作< / th>
< / tr>
< / thead>
< tbody>
< ? php while ( $item = mysqli_fetch_assoc ( $query ) ) : ? >
< tr>
< th scope= "row" > < ? php echo $item [ 'id' ] ? > < / th>
< td> < img src= "<?php echo $item [ 'avatar' ] class = "rounded" alt= "<?php echo $item [ 'name' ] > < / td>
< td> < ? php echo $item [ 'name' ] ; ? > < / td>
< td> < ? php echo $item [ 'gender' ] == 0 ? '♀' : '♂' ; ? > < / td>
< td> < ? php echo $item [ 'birthday' ] ; ? > < / td>
< td class = "text-center" >
< a class = "btn btn-info btn-sm" href= "edit.php?id=<?php echo $item [ 'id' ] > 编辑< / a>
< a class = "btn btn-danger btn-sm" href= "delete.php?id=<?php echo $item [ 'id' ] > 删除< / a>
< / td>
< / tr>
< ? php endwhile ? >
< / tbody>
< / table>
< ul class = "pagination justify-content-center" >
< li class = "page-item" > < a class = "page-link" href= "#" > & laquo; < / a> < / li>
< li class = "page-item" > < a class = "page-link" href= "#" > 1 < / a> < / li>
< li class = "page-item" > < a class = "page-link" href= "#" > 2 < / a> < / li>
< li class = "page-item" > < a class = "page-link" href= "#" > 3 < / a> < / li>
< li class = "page-item" > < a class = "page-link" href= "#" > & raquo; < / a> < / li>
< / ul>
< / main>
< / body>
< / html>
增加数据
<?php
function add_user ( ) {
// 验证非空
if ( empty ( $_POST [ 'name' ] ) ) {
$GLOBALS [ 'error_message' ] = '请输入姓名' ;
return ;
}
if ( ! ( isset ( $_POST [ 'gender' ] ) && $_POST [ 'gender' ] !== '-1' ) ) {
$GLOBALS [ 'error_message' ] = '请选择性别' ;
return ;
}
if ( empty ( $_POST [ 'birthday' ] ) ) {
$GLOBALS [ 'error_message' ] = '请输入日期' ;
return ;
}
// 取值
$name = $_POST [ 'name' ] ;
$gender = $_POST [ 'gender' ] ;
$birthday = $_POST [ 'birthday' ] ;
// 接收文件并验证
if ( empty ( $_FILES [ 'avatar' ] ) ) {
$GLOBALS [ 'error_message' ] = '请上传头像' ;
return ;
}
$ext = pathinfo ( $_FILES [ 'avatar' ] [ 'name' ] , PATHINFO_EXTENSION ) ;
// => jpg
$target = '../uploads/avatar-' . uniqid ( ) . '.' . $ext ;
if ( ! move_uploaded_file ( $_FILES [ 'avatar' ] [ 'tmp_name' ] , $target ) ) {
$GLOBALS [ 'error_message' ] = '上传头像失败' ;
return ;
}
$avatar = substr ( $target , 2 ) ;
// var_dump($name);
// var_dump($gender);
// var_dump($birthday);
// var_dump($avatar);
// 保存
// 1. 建立连接
$conn = mysqli_connect ( 'localhost' , 'root' , '123456' , 'test' ) ;
if ( ! $conn ) {
$GLOBALS [ 'error_message' ] = '连接数据库失败' ;
return ;
}
// var_dump("insert into users values (null, '{$name}', {$gender}, '{$birthday}', '{$avatar}');");
// 2. 开始查询
$query = mysqli_query ( $conn , "insert into users values (null, '{ $name } { $gender } { $birthday } { $avatar } ) ;
if ( ! $query ) {
$GLOBALS [ 'error_message' ] = '查询过程失败' ;
return ;
}
$affected_rows = mysqli_affected_rows ( $conn ) ;
if ( $affected_rows !== 1 ) {
$GLOBALS [ 'error_message' ] = '添加数据失败' ;
return ;
}
// 响应
header ( 'Location: index.php' ) ;
}
if ( $_SERVER [ 'REQUEST_METHOD' ] === 'POST' ) {
add_user ( ) ;
}
? >
< ! DOCTYPE html>
< html lang= "en" >
< head>
< meta charset= "UTF-8" >
< title> XXX 管理系统< / title>
< link rel= "stylesheet" href= "assets/css/bootstrap.css" >
< link rel= "stylesheet" href= "assets/css/style.css" >
< / head>
< body>
< nav class = "navbar navbar-expand navbar-dark bg-dark fixed-top" >
< a class = "navbar-brand" href= "#" > XXX 管理系统< / a>
< ul class = "navbar-nav mr-auto" >
< li class = "nav-item active" >
< a class = "nav-link" href= "index.html" > 用户管理< / a>
< / li>
< li class = "nav-item" >
< a class = "nav-link" href= "#" > 商品管理< / a>
< / li>
< / ul>
< / nav>
< main class = "container" >
< h1 class = "heading" > 添加用户< / h1>
< ? php if ( isset ( $error_message ) ) : ? >
< div class = "alert alert-warning" >
< ? php echo $error_message ; ? >
< / div>
< ? php endif ? >
< form action= "<?php echo $_SERVER [ 'PHP_SELF' ] method= "post" enctype= "multipart/form-data" autocomplete= "off" >
< div class = "form-group" >
< label for = "avatar" > 头像< / label>
< input type= "file" class = "form-control" id= "avatar" name= "avatar" >
< / div>
< div class = "form-group" >
< label for = "name" > 姓名< / label>
< input type= "text" class = "form-control" id= "name" name= "name" >
< / div>
< div class = "form-group" >
< label for = "gender" > 性别< / label>
< select class = "form-control" id= "gender" name= "gender" >
< option value= "-1" > 请选择性别< / option>
< option value= "1" > 男< / option>
< option value= "0" > 女< / option>
< / select>
< / div>
< div class = "form-group" >
< label for = "birthday" > 生日< / label>
< input type= "date" class = "form-control" id= "birthday" name= "birthday" >
< / div>
< button class = "btn btn-primary" > 保存< / button>
< / form>
< / main>
< / body>
< / html>
删除数据
<?php
// 接收要删除的数据 ID
if ( empty ( $_GET [ 'id' ] ) ) {
exit ( '<h1>必须传入指定参数</h1>' ) ;
}
$id = $_GET [ 'id' ] ; // => 1,2,3
// 1. 建立连接
$conn = mysqli_connect ( 'localhost' , 'root' , '123456' , 'test' ) ;
if ( ! $conn ) {
exit ( '<h1>连接数据库失败</h1>' ) ;
}
// 2. 开始查询
$query = mysqli_query ( $conn , 'delete from users where id in (' . $id . ');' ) ;
if ( ! $query ) {
exit ( '<h1>查询数据失败</h1>' ) ;
}
$affected_rows = mysqli_affected_rows ( $conn ) ;
if ( $affected_rows <= 0 ) {
exit ( '<h1>删除失败</h1>' ) ;
}
header ( 'Location: index.php' ) ;
项目思路图
修改数据
<?php
// 接收要修改的数据 ID
if ( empty ( $_GET [ 'id' ] ) ) {
exit ( '<h1>必须传入指定参数</h1>' ) ;
}
$id = $_GET [ 'id' ] ;
// 1. 建立连接
$conn = mysqli_connect ( 'localhost' , 'root' , '123456' , 'test' ) ;
mysqli_set_charset ( $conn , 'utf8' ) ;
if ( ! $conn ) {
exit ( '<h1>连接数据库失败</h1>' ) ;
}
// 2. 开始查询
// 因为ID 是唯一的 那么找到第一个满足条件的就不用在继续了 查询最后 limit 1就可以了
$query = mysqli_query ( $conn , "select * from users where id = { $id } ) ;
if ( ! $query ) {
exit ( '<h1>查询数据失败</h1>' ) ;
}
// 已经查询到的当前数据
$user = mysqli_fetch_assoc ( $query ) ;
if ( ! $user ) {
exit ( '<h1>找不到你要编辑的数据</h1>' ) ;
}
function edit ( ) {
global $user ;
// 验证非空
if ( empty ( $_POST [ 'name' ] ) ) {
$GLOBALS [ 'error_message' ] = '请输入姓名' ;
return ;
}
if ( ! ( isset ( $_POST [ 'gender' ] ) && $_POST [ 'gender' ] !== '-1' ) ) {
$GLOBALS [ 'error_message' ] = '请选择性别' ;
return ;
}
if ( empty ( $_POST [ 'birthday' ] ) ) {
$GLOBALS [ 'error_message' ] = '请输入日期' ;
return ;
}
// 取值
$user [ 'name' ] = $_POST [ 'name' ] ;
$user [ 'gender' ] = $_POST [ 'gender' ] ;
$user [ 'birthday' ] = $_POST [ 'birthday' ] ;
// 有上传就修改
if ( isset ( $_FILES [ 'avatar' ] ) && $_FILES [ 'avatar' ] [ 'error' ] === UPLOAD_ERR_OK ) {
// 用户上传了新头像 -> 用户希望修改头像
$ext = pathinfo ( $_FILES [ 'avatar' ] [ 'name' ] , PATHINFO_EXTENSION ) ;
$target = '../uploads/avatar-' . uniqid ( ) . '.' . $ext ;
if ( ! move_uploaded_file ( $_FILES [ 'avatar' ] [ 'tmp_name' ] , $target ) ) {
$GLOBALS [ 'error_message' ] = '上传头像失败' ;
return ;
}
$user [ 'avatar' ] = substr ( $target , 2 ) ;
}
// $user => 修改过后的信息
// TODO: 将数据更新回数据库
//
$conn1 = mysqli_connect ( 'localhost' , 'root' , '123456' , 'test' ) ;
mysqli_set_charset ( $conn1 , 'utf8' ) ;
// var_dump("update users set name='{$user['name']}',gender={$user['gender']},birthday={$user['birthday']} where id={$_GET['id']};");
$query1 = mysqli_query ( $conn1 , "update users set name='{ $user [ 'name' ] } { $user [ 'gender' ] } { $user [ 'birthday' ] } { $_GET [ 'id' ] } ) ;
$affected_rows1 = mysqli_affected_rows ( $conn1 ) ;
var_dump ( "相应前面e行" ) ;
// var_dump($affected_rows1);
// if ($affected_rows1 !== 1) {
// $GLOBALS['error_message'] = '修改数据失败';
// return;
// }
var_dump ( "相应前面一行" ) ;
header ( 'Location: index.php' ) ;
}
if ( $_SERVER [ 'REQUEST_METHOD' ] === 'POST' ) {
edit ( ) ;
}
? >
< ! DOCTYPE html>
< html lang= "en" >
< head>
< meta charset= "UTF-8" >
< title> XXX 管理系统< / title>
< link rel= "stylesheet" href= "assets/css/bootstrap.css" >
< link rel= "stylesheet" href= "assets/css/style.css" >
< / head>
< body>
< nav class = "navbar navbar-expand navbar-dark bg-dark fixed-top" >
< a class = "navbar-brand" href= "#" > XXX 管理系统< / a>
< ul class = "navbar-nav mr-auto" >
< li class = "nav-item active" >
< a class = "nav-link" href= "index.html" > 用户管理< / a>
< / li>
< li class = "nav-item" >
< a class = "nav-link" href= "#" > 商品管理< / a>
< / li>
< / ul>
< / nav>
< main class = "container" >
< h1 class = "heading" > 编辑“< ? php echo $user [ 'name' ] ; ? > ”< / h1>
< form action= "<?php echo $_SERVER [ 'PHP_SELF' ] $user [ 'id' ] method= "post" enctype= "multipart/form-data" >
< ! -- < input type= "hidden" id= "id" value= "<?php echo $user [ 'id' ] > -- >
< img src= "<?php echo $user [ 'avatar' ] alt= "" >
< ? php if ( isset ( $error_message ) ) : ? >
< div class = "alert alert-warning" >
< ? php echo $error_message ; ? >
< / div>
< ? php endif ? >
< div class = "form-group" >
< label for = "avatar" > 头像< / label>
< ! -- 文件域不能设置默认值 -- >
< input type= "file" class = "form-control" id= "avatar" name= "avatar" >
< / div>
< div class = "form-group" >
< label for = "name" > 姓名< / label>
< input type= "text" class = "form-control" id= "name" name= "name" value= "<?php echo $user [ 'name' ] >
< / div>
< div class = "form-group" >
< label for = "gender" > 性别< / label>
< select class = "form-control" id= "gender" name= "gender" >
< option value= "-1" > 请选择性别< / option>
< option value= "1" < ? php echo $user [ 'gender' ] === '1' ? ' selected' : '' ; ? >> 男< / option>
< option value= "0" < ? php echo $user [ 'gender' ] === '0' ? ' selected' : '' ; ? >> 女< / option>
< / select>
< / div>
< div class = "form-group" >
< label for = "birthday" > 生日< / label>
< input type= "date" class = "form-control" id= "birthday" name= "birthday" value= "<?php echo $user [ 'birthday' ] >
< / div>
< button class = "btn btn-primary" > 保存< / button>
< / form>
< / main>
< / body>
< / html>
https://download.csdn.net/download/weixin_45525272/14922957
