后台:php,数据库:mysql,前端:html,css,js;
主要页面介绍:
1.php连接数据库后台,读取数据并将其以表格的形式显示,并且有添加,编辑,删除,分页等功能;
2.php用于添加用户的表单页面,1.php中的添加用户按钮跳转到这里;
3-add.php用于接收2.php POST过来的表单数据,加以简单sql语言添加用户操作;
4-del.php用于sql删除数据操作;
5-edit.php类似2.php,用于编辑用户信息;
6-save.php用于接收5-edit.php POST过来的表单数据,加以简单sql语言编辑用户信息;
page.php用于处理分页功能;
FIRST(主要1.php页面)
首先每个php页面为了中文显示不乱码使用代码
header('Content-type:text/html;charset=utf-8');
mysql数据库设计细节
id设置为主键,并且设置为自动递增,birth设置为日期date
pdo连接数据库,获取所有数据
$dsn = 'mysql:dbname=user_messages;host=localhost';
$user = 'root';
$password = 'root';
try{
$pdo = new PDO($dsn,$user,$password);
}catch(PDOException $e){
echo '出错了,错误信息为',$e->getMessage();
}
$sql = "SET NAMES UTF8";
$pdo -> exec($sql);
$sql = "SELECT * FROM `users`";
$res = $pdo -> query($sql);
$data = $res ->fetchAll(PDO::FETCH_ASSOC); //获取的所有数据数组
使用foreach遍历显示数据
<?php foreach ($data as $key => $value):?>
<tr>
<td><?php echo $value['id'] ?></td>
<td><?php echo $value['name'] ?></td>
<td><?php echo $value['age'] ?></td>
<td><?php echo $value['sex'] == 1 ? '男' : '女' ?></td>
<td><?php echo $value['birth'] ?></td>
<td><?php echo $value['phone'] ?></td>
<td><?php echo $value['vita'] ?></td>
</tr>
<?php endforeach ?>
SECOND(添加用户3-add.php页面)
先判断2.php页面有没有POST数据过来
if(!empty($_POST)){
$insert_data = $_POST; //获取2.php传过来的数据
}
提取$insert_data数组中的字段来填充成sql语言进行操作
$keys = array_keys($insert_data); //获取数据的key
$filed = '(`' .implode('`,`',keys). '`)';
$values = "'".implode("','",$insert_data)."'";
$sql = 'INSERT INTO `users` .$filed.'VALUES('.$values.');';
$affect_row = $pdo -> exec($sql);
if($affect_row>0){
echo '<script>alert("添加成功");location="1.php"</script>';
}else{
echo '<script>alert("添加失败");history.back();</script>';
}
THIRD(删除操作4-del.php)
删除操作主要获取该数据的id,使用$_GET获取
在1.php页面设置一个删除按钮,并将id设置在href里面、
<a name="detel" href="4-del.php?id=<?php echo $value['id']?">删除></a>
然后先判断能不能GET得到id
if(isset($_GET['id'])){
$id = $_GET['id']+0; //为了防止用人直接在地址栏输入非数字来访问破坏数据库,强行转换为数字
$dsn = 'mysql:dbname=user_messages;host=localhost';
$user = 'root';
$password = 'root';
try{
$pdo = new PDO($dsn,$user,$password);
}catch(PDOException $e){
echo "出错了:". $e->getMessage();
}
$sql = "SET NAMES UTF8";
$T = $pdo ->exec($sql);
$sql = "DELETE FROM `users` WHERE `id` = $id";
$affect_row = $pdo->exec($sql);
if($affect_row>0){
echo '<script>alert("删除成功");location="1.php"</script>';
}else{
echo '<script>alert("删除失败");history.back();</script>';
}
}
FOUTH(编辑操作5-edit.php和6-save.php)
在1.php页面设置一个编辑按钮,并将id设置在href里面
<a href="5-edit.php?id=<?php echo $value['id'] ?>">编辑</a>
获取该id所对应的数据,将其预显示在5-edit.php的表单input里面
if(isset($_GET['id'])){
$id = $_GET['id']+0;
$dsn = 'mysql:dbname=user_messages;host=localhost';
$user = 'root';
$password = 'root';
try{
$pdo = new PDO($dsn,$user,$password);
}catch(PDOException $e){
echo "出错了:". $e->getMessage();
}
$sql = "SET NAMES UTF8";
$T = $pdo ->exec($sql);
$sql = "SELECT `id`,`name`,`age`,`sex`,`phone`,`birth`,`person` FROM `users` WHERE `id` = $id";
$res = $pdo->query($sql);
$one_user = $res->fetch(PDO::FETCH_ASSOC);
}
6-save.php用于处理5-edit.php POST过来的新数据
if (!empty($_POST))
{
$dsn = 'mysql:dbname=user_messages;host=localhost';
$user = 'root';
$password = 'root';
// 连接数据库
try {
$pdo = new PDO($dsn , $user , $password);
} catch (PDOException $e) {
// print_r($e);
echo '出错啦。错误信息是', $e->getMessage() , '<br> 看不懂就联系网站管理员';
// echo '网站升级中';
}
// 设置编码
$sql = "SET NAMES UTF8";
$pdo -> exec ( $sql );
$where = $_POST['id'];
unset($_POST['id']);
$save_data = $_POST;
$sql = "UPDATE `users` SET";
foreach ($save_data as $key => $value) {
$sql.="`".$key."`='".$value."',";
}
$sql = rtrim($sql,',');
$sql.=" WHERE `id`=$where";
$affect_rows = $pdo ->exec($sql);
if ($affect_rows > 0)
{
echo '<script>alert("修改成功");location="1.php"</script>';
}
else
{
echo '<script>alert("修改失败");history.back();</script>';
}
}
分页功能
在1-php中设置好参数,并调用一个写好的函数
// 分页
$current = isset($_GET['page'])?abs($_GET['page']):1;
$current = $current>0?$current:1;
$limit = 5;
$offect = $limit * ($current-1);
$sql = "SELECT COUNT(*) FROM `users`";
$res = $pdo->query($sql);
$count = $res->fetch(PDO::FETCH_NUM);
$count = $count[0];
$size = 5;
$sql = "SELECT * FROM `users` LIMIT $offect,$limit";
//调用函数
<?php page($current,$limit,$count,$size);?>
函数
function page($current,$limit,$count,$size){
$count_page = ceil($count/$limit);
if($current>1){
echo '<a href="?page=1">首页</a>';
echo '<a href="?page='.($current-1).'">上一页</a>';
}else{
echo'<span>首页</span>';
echo'<span>上一页</span>';
}
if($current<$size/2+1){
$star = 1;
$end = $count_page>=$size?$size:$count_page;
}elseif($current>($count_page-($size/2)+1)){
$star =($count_page-$size+1)>=1?($count_page-$size + 1):1;
$end = $count_page;
}else{
$star = $current - floor($size/2);
$end = $current + floor($size/2);
}
for($i = $star;$i<=$end;$i++){
if ($i == $current) {
echo ' <span>'.$i.'</span> ';
}else{
echo ' <a href="?page='.$i.'">'.$i.'</a> ';
}
}
if($current<$count_page){
echo '<a href="?page='.($current+1).'">下一页</a>';
echo '<a href="?page='.$count_page.'">尾页</a>';
}else{
echo'<span>下一页</span>';
echo'<span>尾页</span>';
}
}