PHP增删改查数据库（前端+后台）phpDeamswangoose的博客-

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> &nbsp; <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>&copy;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> 

以上！