PHP增删改查数据库（前端+后台）
要求：
-
首页导航栏中内置功能
-
查看数据库
-
点击Edit修改数据库内容
-
点击Delete后删除数据库内此记录，返回首页输出删除成功。
-
向数据库里增加数据
-
向搜索框输入查询的关键字
之后输出查询结果：
目录结构：
代码如下：
index.php:
<?php
include_once('includes/header.php');
if (isset($_GET['action'])) {
$action = $_GET['action'];
if ($action == 'viewcontacts') {
//read records code here
$conn = new PDO("mysql:host=localhost;dbname=contactsdb;charset=utf8", "root", "");
$statement = $conn->query("select * from contacts order by id");
$statement->setFetchMode(PDO::FETCH_ASSOC);
$records = [];
while ($row = $statement->fetch()) {
$records[] = $row;
}
//display records in bootstrap table
include_once("includes/viewContacts.php");
} else if ($action == 'editContacts') {
$id = $_GET['id'];
$firstname = $_GET['firstname'];
$lastname = $_GET['lastname'];
$Email = $_GET['email'];
$Mobile = $_GET['mobile'];
$photoname = $_GET['photoname'];
$first_name = '';
$last_name = '';
$email = '';
$mobile = '';
$filename = '';
$errors = [];
if (isset($_POST['submit'])) {
//validation
if (isset($_POST['first_name'])) {//设定first_name的输入规范
$first_name = $_POST['first_name'];
if (strlen($first_name) == 0) {
$errors['first_name'] = 'First Name is missing input';
} elseif (!ctype_alpha($first_name)) {
$errors['first_name'] = 'Enter a valid First Name';
}
}
if (isset($_POST['last_name'])) {//设定last_name的输入规范
$last_name = $_POST['last_name'];
if (strlen($last_name) == 0) {
$errors['last_name'] = 'Last Name is missing input';
} elseif (!ctype_alpha($last_name)) {
$errors['last_name'] = 'Enter a valid Last Name';
}
}
if (isset($_POST['email'])) {//设定email的输入规范
$email = trim($_POST['email']);
if (strlen($email) == 0) {
$errors['email'] = "Email address is missing input";
} else if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
$errors['email'] = "Enter a valid Email Address";
}
}
if (isset($_POST['mobile'])) {
$mobile = trim($_POST['mobile']);
if (strlen($mobile) == 0) {
$errors['mobile'] = "Missing input";
} elseif (strlen($mobile) < 10) {
$errors['mobile'] = "Mobile number must be 10 digits";
} else if (!ctype_digit($mobile)) {
$errors['mobile'] = "Enter a valid mobile number";
}
}
// image
$filename = $_FILES["image"]["name"];
$temp_file = $_FILES["image"]["tmp_name"];
$type = $_FILES["image"]["type"];
$size = $_FILES["image"]["size"];
$errorLevel = $_FILES["image"]["error"];
$error_messages = [
"Upload successful",
"File exceeds maximum upload size specified by default",
"File exceeds size specified by MAX_FILE_SIZE",
"File only partially uploaded",
"Form submitted with no file specified",
"",
"No temporary folder",
"Cannot write file to disk",
"File type is not permitted"
];
$destination = 'D:/xampp/htdocs/A1P4/img/';
$target_file = $destination . $filename;
$max = 3000000;
if ($errorLevel > 0) {
// Set the error message to the errors array
$errors["image"] = $error_messages[$errorLevel];
} else {
if (file_exists($temp_file)) {
$size = $_FILES["image"]["size"];
if ($size <= $max) {
$permitted = ["gif", "jpg", "jpeg", "png"];
$ext = pathinfo($filename, PATHINFO_EXTENSION);
if (in_array($ext, $permitted)) {
move_uploaded_file($temp_file, $target_file);
//$errors["image"] = "The file $filename has been uploaded.";
} else {
$errors["image"] = "$filename type is not permitted";
}
} else {
$errors["image"] = "$filename is too big – upload failed";
}
} else {
$errors["image"] = "File upload has failed";
}
}
if (count($errors) == 0) {
$values = [$first_name, $last_name, $email, $mobile, $filename];
$dsn = 'mysql:host=localhost;dbname=contactsdb';
$username = 'root';
$password = '';
$conn = new PDO($dsn, $username, $password);
$sql = "update contacts set first_name=? ,last_name=?,email=?,mobile=?,photo_filename=? where id = $id";
$statement = $conn->prepare($sql);
$success = $statement->execute($values);
if ($success) {
include_once 'index.php'; //如果无错误就返回主页
echo"Edit contact record successed!";
} else {
include_once 'index.php';
echo"Insert contact record failed!";
}
} else {
include_once 'includes/editContacts.php'; //有不符合规范的就返回form并提示
}
} else {
include_once 'includes/editContacts.php';
}
} else if ($action == 'deleteContacts') {
$id = $_GET['id'];
$dsn = 'mysql:host=localhost;dbname=contactsdb';
$username = 'root';
$password = '';
$conn = new PDO($dsn, $username, $password);
$sql = "delete from contacts where id = $id";
$statement = $conn->query($sql);
$success = $statement->execute();
if ($success) {
include_once 'index.php';
echo "Delete booking record successed!";
} else {
include_once 'index.php';
echo "Delete booking record failed!";
}
} else if ($action == 'addcontacts') {
$first_name = '';
$last_name = '';
$email = '';
$mobile = '';
$filename = '';
$errors = [];
if (isset($_POST['submit'])) {
//validation
if (isset($_POST['first_name'])) {//设定first_name的输入规范
$first_name = $_POST['first_name'];
if (strlen($first_name) == 0) {
$errors['first_name'] = 'First Name is missing input';
} elseif (!ctype_alpha($first_name)) {
$errors['first_name'] = 'Enter a valid First Name';
}
}
if (isset($_POST['last_name'])) {//设定last_name的输入规范
$last_name = $_POST['last_name'];
if (strlen($last_name) == 0) {
$errors['last_name'] = 'Last Name is missing input';
} elseif (!ctype_alpha($last_name)) {
$errors['last_name'] = 'Enter a valid Last Name';
}
}
if (isset($_POST['email'])) {//设定email的输入规范
$email = trim($_POST['email']);
if (strlen($email) == 0) {
$errors['email'] = "Email address is missing input";
} else if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
$errors['email'] = "Enter a valid Email Address";
}
}
if (isset($_POST['mobile'])) {
$mobile = trim($_POST['mobile']);
if (strlen($mobile) == 0) {
$errors['mobile'] = "Missing input";
} elseif (strlen($mobile) < 10) {
$errors['mobile'] = "Mobile number must be 10 digits";
} else if (!ctype_digit($mobile)) {
$errors['mobile'] = "Enter a valid mobile number";
}
}
// image
$filename = $_FILES["image"]["name"];
$temp_file = $_FILES["image"]["tmp_name"];
$type = $_FILES["image"]["type"];
$size = $_FILES["image"]["size"];
$errorLevel = $_FILES["image"]["error"];
$error_messages = [
"Upload successful",
"File exceeds maximum upload size specified by default",
"File exceeds size specified by MAX_FILE_SIZE",
"File only partially uploaded",
"Form submitted with no file specified",
"",
"No temporary folder",
"Cannot write file to disk",
"File type is not permitted"
];
$destination = 'D:/xampp/htdocs/A1P4/img/';
$target_file = $destination . $filename;
$max = 3000000;
if ($errorLevel > 0) {
// Set the error message to the errors array
$errors["image"] = $error_messages[$errorLevel];
} else {
if (file_exists($temp_file)) {
$size = $_FILES["image"]["size"];
if ($size <= $max) {
$permitted = ["gif", "jpg", "jpeg", "png"];
$ext = pathinfo($filename, PATHINFO_EXTENSION);
if (in_array($ext, $permitted)) {
move_uploaded_file($temp_file, $target_file);
//$errors["image"] = "The file $filename has been uploaded.";
} else {
$errors["image"] = "$filename type is not permitted";
}
} else {
$errors["image"] = "$filename is too big – upload failed";
}
} else {
$errors["image"] = "File upload has failed";
}
}
if (count($errors) == 0) {
$values = [$first_name, $last_name, $email, $mobile, $filename];
$dsn = 'mysql:host=localhost;dbname=contactsdb';
$username = 'root';
$password = '';
$conn = new PDO($dsn, $username, $password);
$sql = "insert into contacts (first_name,last_name,email,mobile,photo_filename)values(?,?,?,?,?)";
$statement = $conn->prepare($sql);
$success = $statement->execute($values);
if ($success) {
include_once 'index.php'; //如果无错误就返回主页
echo"Insert contact record successed!";
} else {
include_once 'index.php';
echo"Insert contact record failed!";
}
} else {
include_once 'includes/addContacts.php'; //有不符合规范的就返回form并提示
}
} else {
include_once 'includes/addContacts.php';
}
} else if ($action == 'seachercontacts') {
$keyword = '';
$errors = [];
if (isset($_POST['send'])) {
//validation
if (isset($_POST['keyword'])) {//设定keyword的输入规范
$keyword = $_POST['keyword'];
if (strlen($keyword) == 0) {
$errors['keyword'] = 'keyword is missing input';
}
if (count($errors) == 0) {
$conn = mysqli_connect('localhost', 'root', '', 'contactsdb');
$res = mysqli_query($conn, "select * from contacts where first_name like '%$keyword%' or last_name like '%$keyword%' or email like '%$keyword%' or mobile like '%$keyword%' or photo_filename like '%$keyword%'") or die(mysqli_error($conn));
$records = [];
while ($row = mysqli_fetch_assoc($res)) {
$records[] = $row;
}
include_once("includes/viewContacts.php");
}
} else {
include_once 'includes/seacherContacts.php'; //有不符合规范的就返回form并提示
}
} else {
include_once 'includes/seacherContacts.php';
}
}
} else {
include_once('includes/content.php');
}
include_once('includes/footer.php');
includes/addConacts.php:
<div class="container" >
<div class="row">
<div class="col-sm-3"></div>
<div class="col-sm-6 box text-center">
Add Contacts
</div>
<div class="col-sm-3"></div>
</div>
<div class="row">
<div class="col-sm-3"></div>
<div class="col-sm-6 jumbotron">
<form action="" method="post" novalidate="true" enctype="multipart/form-data">
<div class="form-group">
<label class="control-label">
First Name
<span class="error" style="color:red">
<?= isset($errors['first_name']) ? $errors['first_name'] : "" ?>
</span>
</label>
<input class="form-control" type="text" name="first_name"maxlength="30" value="<?= $first_name ?>" />
</div>
<div class="form-group">
<label class="control-label">
Last Name
</label>
<span class="error" style="color:red">
<?= isset($errors['last_name']) ? $errors['last_name'] : "" ?>
</span>
<input class="form-control" type="text" name="last_name"maxlength="30" value="<?= $last_name ?>" />
</div>
<div class="form-group">
<label class="control-label">
Email
</label>
<span class="error" style="color:red">
<?= isset($errors['email']) ? $errors['email'] : "" ?>
</span>
<input class="form-control" type="email" name="email" value="<?= $email ?>"/>
</div>
<div class="form-group">
<label>
Mobile:
<span class="error" style="color:red">
<?= isset($errors['mobile']) ? $errors['mobile'] : "" ?>
</span>
</label>
<input class="form-control" type="text" name="mobile" maxlength="10" value="<?= $mobile ?>"/>
</div>
<div class="form-group">
<label>
Portrait Photo:
<span class="error" style="color:red">
<?= isset($errors['image']) ? $errors['image'] : "" ?>
</span>
</label>
<input class="form-control" type="file" name="image" />
</div>
<input class="btn btn-primary btn-block" type="submit" name="submit" value="SUBMIT" />
</div>
</form>
</div>
<div class="col-sm-3"></div>
</div>
includes/viewConacts.php:
<br>
<br>
<div class="container">
<div class="row">
<div class="col-md-3 text-center"></div>
<div class="col-md-6 box text-center">View Contacts:<?= count($records) ?>results</div>
<div class="col-md-3 text-center"></div>
</div>
<div class="row">
<div class="col-md-3 text-center"></div>
<div class="col-md-6 jumbotron text-center">
<table class="table table-striped table-hover" >
<tr class="info">
<th>ID</th><th>First Name</th><th>Last Name</th><th>Email</th><th>Mobile</th><th>Photo</th><th>Manage</th>
</tr>
<?php foreach ($records as $row): ?>
<tr>
<td align="left"><?= $row['id'] ?></td>
<td align="left"><?= $row['first_name'] ?></td>
<td align="left"><?= $row['last_name'] ?></td>
<td align="left"><?= $row['email'] ?></td>
<td align="left"><?= $row['mobile'] ?></td>
<td align="left"><img src="img/<?=$row['photo_filename'] ?>"width="40" height="50"/></td>
<td align="left">
<a href="?action=editContacts&id=<?= $row['id'] ?>&firstname=<?= $row['first_name'] ?>&lastname=<?= $row['last_name'] ?>&email=<?= $row['email'] ?>&mobile=<?= $row['mobile'] ?>&photoname=<?= $row['photo_filename'] ?>">Edit</a>
<a href="?action=deleteContacts&id=<?= $row['id'] ?>" >Delete</a>
</td>
</tr>
<?php endforeach; ?>
</table>
</div>
<div class="col-md-3 text-center"></div>
</div>
</div>
includes/editConacts.php:
<div class="container" >
<div class="row">
<div class="col-sm-3"></div>
<div class="col-sm-6 box text-center">
Edit Contacts
</div>
<div class="col-sm-3"></div>
</div>
<div class="row">
<div class="col-sm-3"></div>
<div class="col-sm-6 jumbotron">
<form action="" method="post" novalidate="true" enctype="multipart/form-data">
<div class="form-group">
<label class="control-label">
First Name
<span class="error" style="color:red">
<?= isset($errors['first_name']) ? $errors['first_name'] : "" ?>
</span>
</label>
<input class="form-control" type="text" name="first_name"maxlength="30" value="<?= $firstname ?>" />
</div>
<div class="form-group">
<label class="control-label">
Last Name
</label>
<span class="error" style="color:red">
<?= isset($errors['last_name']) ? $errors['last_name'] : "" ?>
</span>
<input class="form-control" type="text" name="last_name"maxlength="30" value="<?= $lastname ?>" />
</div>
<div class="form-group">
<label class="control-label">
Email
</label>
<span class="error" style="color:red">
<?= isset($errors['email']) ? $errors['email'] : "" ?>
</span>
<input class="form-control" type="email" name="email" value="<?= $Email ?>"/>
</div>
<div class="form-group">
<label>
Mobile:
<span class="error" style="color:red">
<?= isset($errors['mobile']) ? $errors['mobile'] : "" ?>
</span>
</label>
<input class="form-control" type="text" name="mobile" maxlength="10" value="<?= $Mobile ?>"/>
</div>
<div class="form-group">
<label>
Portrait Photo:
<span class="error" style="color:red">
<?= isset($errors['image']) ? $errors['image'] : "" ?>
</span>
</label>
<input class="form-control" type="file" name="image" />
</div>
<input class="btn btn-primary btn-block" type="submit" name="submit" value="SUBMIT" />
</div>
</form>
</div>
<div class="col-sm-3"></div>
</div>
includes/seacherConacts.php:
<div class="container" >
<div class="row">
<div class="col-sm-3"></div>
<div class="col-sm-6 box text-center">
Seacher Contacts
</div>
<div class="col-sm-3"></div>
</div>
<div class="row">
<div class="col-sm-3"></div>
<div class="col-sm-6 jumbotron">
<form action="" method="post" >
<div class="form-group">
<label class="control-label">
Keyword
</label>
<span class="error" style="color:red">
<?= isset($errors['keyword']) ? $errors['keyword'] : "" ?>
</span>
<input class="form-control" type="text" name="keyword"maxlength="30" value="<?= $keyword ?>" />
</div>
<input class="btn btn-primary btn-block" type="submit" name="send" value="SUBMIT" />
</div>
</form>
</div>
<div class="col-sm-3"></div>
</div>
includes/header.php:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="description" content="">
<meta name="author" content="">
<title>E-Bookings Theme</title>
<!-- Bootstrap Core CSS -->
<link href="css/bootstrap.min.css" rel="stylesheet" type="text/css">
<!-- Fonts -->
<link href="font-awesome/css/font-awesome.min.css" rel="stylesheet" type="text/css">
<link href="css/animate.css" rel="stylesheet" />
<!-- Squad theme CSS -->
<link href="css/style.css" rel="stylesheet">
<link href="color/default.css" rel="stylesheet">
</head>
<body id="page-top" data-spy="scroll" data-target=".navbar-custom">
<nav class="navbar navbar-custom navbar-fixed-top" role="navigation">
<div class="container">
<div class="navbar-header page-scroll">
<button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-main-collapse">
<i class="fa fa-bars"></i>
</button>
<a class="navbar-brand" href="index.html">
<h1>E-CONTACTS DATABASE</h1>
</a>
</div>
<!-- Collect the nav links, forms, and other content for toggling -->
<div class="collapse navbar-collapse navbar-right navbar-main-collapse">
<ul class="nav navbar-nav">
<li class="active"><a href="#intro">Home</a></li>
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown">E-CONTACTS<b class="caret"></b></a>
<ul class="dropdown-menu">
<li><a href="?action=addcontacts">ADD CONTACTS</a></li>
<li><a href="?action=seachercontacts">SEACHER CONTACTS</a></li>
<li><a href="?action=viewcontacts">VIEW CONTACTS</a></li>
</ul>
</li>
</ul>
</div>
<!-- /.navbar-collapse -->
</div>
<!-- /.container -->
</nav>
<!-- Section: intro -->
<section id="intro" class="intro">
includes/footer.php:
</section>
<!-- /Section: intro -->
<footer>
<div class="container">
<div class="row">
<div class="col-md-12 col-lg-12">
<div class="wow shake" data-wow-delay="0.4s">
<div class="page-scroll marginbot-30">
</div>
<p>©SquadFREE. All rights reserved.</p>
<div class="credits">
Designed by <a href="https://bootstrapmade.com/">BootstrapMade</a>
</div>
</div>
</div>
</div>
</footer>
<!-- Core JavaScript Files -->
<script src="js/jquery.min.js"></script>
<script src="js/bootstrap.min.js"></script>
<script src="js/jquery.easing.min.js"></script>
<script src="js/jquery.scrollTo.js"></script>
<script src="js/wow.min.js"></script>
<!-- Custom Theme JavaScript -->
<script src="js/custom.js"></script>
</body>
</html>
includes/content.php:
<div class="slogan">
<h2>WELCOME TO <span class="text_color">E-CONTACTS THEME</span> </h2>
<h4>I AM A STUDENT IN THE WEB APPLICATION & SERVER MANAGEMENT UNIT</h4>
</div>
以上！
