简单链接MySQL类，以及简单插入数据到数据库
先上个动图
exercise05.gif
注册页面 index.html
<section>
<div class="col-lg-8 col-lg-offset-2">
<div class="page-header">
<h2>Sign up</h2>
</div>
<form class="form-horizontal" action="save.php" method="post" enctype="multipart/form-data">
<div class="form-group">
<label class="col-lg-3 control-label">Username</label>
<div class="col-lg-5">
<input type="text" class="form-control" name="username" id="username"/>
</div>
</div>
<div class="form-group">
<label class="col-lg-3 control-label">Email address</label>
<div class="col-lg-5">
<input class="form-control" name="email" type="email" id="email"/>
</div>
</div>
<div class="form-group">
<label class="col-lg-3 control-label">Password</label>
<div class="col-lg-5">
<input type="password" class="form-control" name="password" id="password" required />
</div>
</div>
<div class="form-group">
<label class="col-lg-3 control-label">Retype password</label>
<div class="col-lg-5">
<input type="password" class="form-control" name="confirmPassword" id="confirmPassword"
required />
</div>
</div>
<div class="form-group">
<label class="col-lg-3 control-label">Gender</label>
<div class="col-lg-5">
<div class="radio">
<label>
<input type="radio" name="gender" value="M" required /> Male
</label>
</div>
<div class="radio">
<label>
<input type="radio" name="gender" value="F" /> Female
</label>
</div>
</div>
</div>
<div class="form-group">
<label class="col-lg-3 control-label">Image</label>
<div class="col-lg-5">
<div class="file">
<label>
<input type="file" name="file" required />
</label>
</div>
</div>
</div>
<div class="form-group">
<div class="col-lg-9 col-lg-offset-3">
<button class="btn btn-primary" type="submit">Sign up</button>
</div>
</div>
</form>
</div>
</section>
index.html 效果
index.png
创建MySQL DB类 db.class.php
<?php
require_once("constant.php"); //include config file
date_default_timezone_set(TIMEZONE);
class DB{
public $host;
public $username;
public $password;
public $dbname;
public $conn;
/**
* DB类构造函数
*/
public function DB($host=DB_HOST ,$username=DB_USER,$password=DB_PASSWORD,$dbname=DB_NAME){
$this->host = $host;
$this->username = $username;
$this->password = $password;
$this->dbname = $dbname;
$this->open();
}
/**
* open db
*/
public function open(){
// echo $this->host ."--". $this->username ."--" .$this->password ;
$this->conn = mysql_connect($this->host,$this->username,$this->password);
mysql_select_db($this->dbname);
mysql_query("SET CHARACTER SET utf8");
}
/**
* close db
*/
public function close(){
mysql_close($this->conn);
}
// add user
public function addUser(){
//$sql = "INSERT INTO user (name, pass, email, gender, image)
//VALUES ('Glenn', '123', 'hello@uic.edu.hk','M','upload/a.jpg')";
$sql = "INSERT INTO user (name, pass, email, gender, image)
VALUES ('".$_POST['username']."', '".$_POST['password']."', '".$_POST['email']."','".$_POST['gender']."','".$_POST['image']."')";
mysql_query($sql,$this->conn);
$id = mysql_insert_id($this->conn);
return $id;
}
// get all users
public function getUsers(){
$sql = "SELECT * FROM user";
$result = mysql_query($sql,$this->conn);
return $result;
}
// get one user
public function getUser($name,$pass){
$sql = "SELECT * FROM user where name = '$name' and pass = '$pass' limit 1";
$result = mysql_query($sql,$this->conn);
if (mysql_fetch_row($result)) {
return 1;
}
return 0;
}
}
?>
主要通过实例化DB类，调用 open()方法链接数据库，定义添加用户和查询用户的方法。
require_once("constant.php"); //include config file
定义常量在一个单独文件，用于存储配置数据的链接信息
constant.php
<?php
/**
* 数据库配置信息
*/
define('DB_HOST','192.168.9.46'); //服务器
define('DB_USER','demo'); //数据库用户名
define('DB_PASSWORD','donttrytoguess'); //数据库密码
define('DB_NAME','demo'); //默认数据库
define('DB_CHARSET','utf8'); //数据库字符集
define('TIMEZONE',"PRC"); //时区设置
?>
处理用户注册提交的数据
上面index.html 的form 内， action 是提交到 save.php
这里不涉及校验，仅仅为演示如何提交到后台保存。
save.php
<?php
include('db.class.php');
// upload file
function uploadFile(){
print_r($_FILES);
if ((($_FILES["file"]["type"] == "image/gif") || ($_FILES["file"]["type"] == "image/jpeg")|| ($_FILES["file"]["type"] == "image/jpg"))&& ($_FILES["file"]["size"] < 200000)){
if ($_FILES["file"]["error"] > 0){
return "upload/default.jpg";
}else{
// 文件上传到指定目录
move_uploaded_file($_FILES["file"]["tmp_name"],"upload/" . $_FILES["file"]["name"]);
return "upload/" . $_FILES["file"]["name"];
}
}else{
return "upload/default.jpg";
}
}
// 处理用户提交的数据
if ($_POST) {
$image = uploadFile();
$_POST['image'] = $image;
$db = new DB();//实例化DB() 链接数据库
$db->addUser();//调用 addUser() 插入一个用户记录
$db->close(); // 调用 close()关闭链接
$url = 'login.html';
//跳转页面
Header("Location: $url");
}else{
$url = 'index.html';
Header("Location: $url");
}
?>
save.php 实现图片上传以插入新用户数据记录。
用户列表
userlist.php
<?php
session_start();
if($_SESSION['login'] != 'y'){
$url = 'login.html';
Header("Location: $url");
}
include('db.class.php');
$db = new DB();
$result = $db->getUsers();
?>
<!DOCTYPE html>
<html>
<head>
<title>Form </title>
<link rel="stylesheet" href="../vendor/bootstrap/css/bootstrap.css"/>
<script type="text/javascript" src="../vendor/jquery/jquery.min.js"></script>
</head>
<body>
<div class="container">
<div class="row">
<h1>User List</h1>
<table class="table table-hover ">
<tr>
<th> Id</th>
<th> Name</th>
<th> Email</th>
<th> Gender</th>
<th> Image</th>
</tr>
<?php
while($row = mysql_fetch_array($result)){
?>
<tr>
<td> <?=$row['id']?></td>
<td> <?=$row['name']?></td>
<td> <?=$row['email']?></td>
<td> <?=$row['gender']?></td>
<td> <img src="<?=$row['image']?>" class="img-rounded" height="100px" width="100px"></td>
</tr>
<?php
}
?>
</table>
</div>
</div>
</body>
<script type="text/javascript">
$(document).ready(function () {
// $("table tr:odd td").addClass("success"); //改变偶数行背景色
$("table tr:even td").addClass("info"); //改变偶数行背景色
});
</script>
</html>
userlist.php 效果
userlist.png
如果用户并未登陆而直接打开链接将会跳转到login.html页面
用户登陆页面
login.html
<section>
<div class="col-lg-8 col-lg-offset-2">
<div class="page-header">
<h2>Sign in</h2>
</div>
<form class="form-horizontal" action="login.php" method="post" enctype="multipart/form-data">
<div class="form-group">
<label class="col-lg-3 control-label">Username</label>
<div class="col-lg-5">
<input type="text" class="form-control" name="username" id="username"/>
</div>
</div>
<div class="form-group">
<label class="col-lg-3 control-label">Password</label>
<div class="col-lg-5">
<input type="password" class="form-control" name="password" id="password" required />
</div>
</div>
<div class="form-group">
<div class="col-lg-9 col-lg-offset-3">
<button class="btn btn-primary" type="submit">Sign in</button>
</div>
</div>
</form>
</div>
</section>
login.html 效果
login.png
用户登陆处理
login.php
<?php
session_start();
include('db.class.php');
if ($_POST) {
$db = new DB();
$result = $db->getUser($_POST['username'],$_POST['password']);
$db->close();
// echo $result;
if ($result == 1) {
$url = 'userlist.php';
// store session data
$_SESSION['login']='y';
}else{
$url = "error.html";
}
Header("Location: $url");
}else{
$url = 'login.html';
Header("Location: $url");
}
?>
首先启动了 session, 当用户从login.html 把数据POST到后台，后台实例化DB(), 根据用户POST的 账号密码，调用 getUser() 查询数据库，如果有数据则返回1 否则0，然后根据情况给session 设置值。
这里设置为
$_SESSION['login']='y';
回到前面提到的 直接打开userlist.php 会跳转到login.html页面
是因为设置 session 判断 session 。
session_start();
if($_SESSION['login'] != 'y'){
$url = 'login.html';
Header("Location: $url");
}
数据库 user表
sql-structure.png
整个过程比较简单明白。
总结一下：
- PHP class
- 数据库链接，SQL语句拼接
- session
- 文件上传 （注意服务器上的存储文件夹的权限要设置，否则可能无法上传或者读取）
upload-folder.png
