簡(jiǎn)單鏈接MySQL類抛蚁，以及簡(jiǎn)單插入數(shù)據(jù)到數(shù)據(jù)庫
先上個(gè)動(dòng)圖
exercise05.gif
注冊(cè)頁面 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
創(chuàng)建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類構(gòu)造函數(shù)
*/
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;
}
}
?>
主要通過實(shí)例化DB類，調(diào)用 open()方法鏈接數(shù)據(jù)庫，定義添加用戶和查詢用戶的方法。
require_once("constant.php"); //include config file
定義常量在一個(gè)單獨(dú)文件未舟，用于存儲(chǔ)配置數(shù)據(jù)的鏈接信息
constant.php
<?php
/**
* 數(shù)據(jù)庫配置信息
*/
define('DB_HOST','192.168.9.46'); //服務(wù)器
define('DB_USER','demo'); //數(shù)據(jù)庫用戶名
define('DB_PASSWORD','donttrytoguess'); //數(shù)據(jù)庫密碼
define('DB_NAME','demo'); //默認(rèn)數(shù)據(jù)庫
define('DB_CHARSET','utf8'); //數(shù)據(jù)庫字符集
define('TIMEZONE',"PRC"); //時(shí)區(qū)設(shè)置
?>
處理用戶注冊(cè)提交的數(shù)據(jù)
上面index.html 的form 內(nèi)统台， action 是提交到 save.php
這里不涉及校驗(yàn)，僅僅為演示如何提交到后臺(tái)保存踊东。
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";
}
}
// 處理用戶提交的數(shù)據(jù)
if ($_POST) {
$image = uploadFile();
$_POST['image'] = $image;
$db = new DB();//實(shí)例化DB() 鏈接數(shù)據(jù)庫
$db->addUser();//調(diào)用 addUser() 插入一個(gè)用戶記錄
$db->close(); // 調(diào)用 close()關(guān)閉鏈接
$url = 'login.html';
//跳轉(zhuǎn)頁面
Header("Location: $url");
}else{
$url = 'index.html';
Header("Location: $url");
}
?>
save.php 實(shí)現(xiàn)圖片上傳以插入新用戶數(shù)據(jù)記錄。
用戶列表
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"); //改變偶數(shù)行背景色
$("table tr:even td").addClass("info"); //改變偶數(shù)行背景色
});
</script>
</html>
userlist.php 效果
userlist.png
如果用戶并未登陸而直接打開鏈接將會(huì)跳轉(zhuǎn)到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");
}
?>
首先啟動(dòng)了 session, 當(dāng)用戶從login.html 把數(shù)據(jù)POST到后臺(tái)刚操，后臺(tái)實(shí)例化DB(), 根據(jù)用戶POST的 賬號(hào)密碼闸翅，調(diào)用 getUser() 查詢數(shù)據(jù)庫，如果有數(shù)據(jù)則返回1 否則0菊霜，然后根據(jù)情況給session 設(shè)置值坚冀。
這里設(shè)置為
$_SESSION['login']='y';
回到前面提到的 直接打開userlist.php 會(huì)跳轉(zhuǎn)到login.html頁面
是因?yàn)樵O(shè)置 session 判斷 session 。
session_start();
if($_SESSION['login'] != 'y'){
$url = 'login.html';
Header("Location: $url");
}
數(shù)據(jù)庫 user表
sql-structure.png
整個(gè)過程比較簡(jiǎn)單明白鉴逞。
總結(jié)一下：
- PHP class
- 數(shù)據(jù)庫鏈接记某，SQL語句拼接
- session
- 文件上傳 （注意服務(wù)器上的存儲(chǔ)文件夾的權(quán)限要設(shè)置，否則可能無法上傳或者讀裙辜瘛）
upload-folder.png
