在本教程中,我们将使用PDO创建向SQLite插入数据。使用PHP,您可以让您的用户直接与脚本进行交互,并轻松地学习其语法。SQLite是一个进程内库,它实现了一个独立的事务型SQL数据库引擎。SQLite通常为您提供的内存越多,运行速度就越快。这就是为什么大多数开发人员将SQLite用作其应用程序的数据库引擎的原因。所以让我们做编码...
入门:
首先,您必须下载并安装XAMPP或任何运行PHP脚本的本地服务器。这是XAMPP服务器https://www.apachefriends.org/index.html的链接。
这是我在本教程https://jquery.com/中使用的jquery的链接。
最后,这是我用于布局设计https://getbootstrap.com/的引导程序的链接。
安装SQLite浏览器
现在我们将安装SQLite数据查看器,这是SQLite数据库浏览器的链接http://sqlitebrowser.org/。
设置SQLite
首先,我们将在PHP中启用SQLite 3。
1.打开localhost服务器文件夹XAMPP等,然后找到php.ini。
2.打开php.ini,并通过删除行中的分号来启用sqlite3。
3.保存更改并重新启动服务器。
创建数据库连接
打开任何类型的文本编辑器(notepad ++等)。然后只需复制/粘贴下面的代码,然后将其命名为conn.php即可。
<?php
$conn = new PDO('sqlite:db/db_student.sqlite3');
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$query = "CREATE TABLE IF NOT EXISTS student (student_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, firstname TEXT, lastname TEXT, gender TEXT, address TEXT)";
$conn->exec($query);
?>
创建界面
在这里我们将为我们的应用程序创建一个简单的表单。要创建表单,只需复制并将其写入文本编辑器,然后将其另存为index.php即可。
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" name="viewport" content="width=device-width, initial-scale=1"/>
<link rel="stylesheet" type="text/css" href="css/bootstrap.css"/>
</head>
<body>
<nav class="navbar navbar-default">
<div class="container-fluid">
<a class="navbar-brand" href="css/bootstrap.css">Sourcecodester</a>
</div>
</nav>
<div class="col-md-3"></div>
<div class="col-md-6 well">
<h3 class="text-primary">PHP - Simple CRUD With SQLite Using PDO</h3>
<hr style="border-top:1px dotted #ccc;"/>
<button type="button" class="btn btn-success" data-toggle="modal" data-target="#form_modal"><span class="glyphicon glyphicon-plus"></span> Add Student</button>
<br /><br/ >
<table class="table table-bordered">
<thead class="alert-info">
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Gender</th>
<th>Address</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php
require 'conn.php';
$query = $conn->prepare("SELECT * FROM `student`");
$query->execute();
while($fetch = $query->fetch()){
?>
<tr>
<td><?php echo $fetch['firstname']?></td>
<td><?php echo $fetch['lastname']?></td>
<td><?php echo $fetch['gender']?></td>
<td><?php echo $fetch['address']?></td>
<td><button class="btn btn-warning" type="button" data-toggle="modal" data-target="#update_student<?php echo $fetch['student_id']?>"><span class="glyphicon glyphicon-edit"></span> Edit</button> <a href="delete.php?id=<?php echo $fetch['student_id']?>" class="btn btn-danger"><span class="glyphicon glyphicon-trash"></span> Delete</a></td>
</tr>
<div class="modal fade" id="update_student<?php echo $fetch['student_id']?>">
<div class="modal-dialog">
<div class="modal-content">
<form action="update_student.php" method="POST">
<div class="modal-header">
<h3 class="modal-title">Update Student</h3>
</div>
<div class="modal-body">
<div class="col-md-2"></div>
<div class="col-md-8">
<div class="form-group">
<label>Firstname</label>
<input type="text" class="form-control" value="<?php echo $fetch['firstname']?>" name="firstname"/>
<input type="hidden" class="form-control" value="<?php echo $fetch['student_id']?>" name="student_id"/>
</div>
<div class="form-group">
<label>Lastname</label>
<input type="text" class="form-control" value="<?php echo $fetch['lastname']?>" name="lastname"/>
</div>
<div class="form-group">
<label>Gender</label>
<div class="radio">
<label><input type="radio" name="gender" value="Male" required="required"/>Male</label>
<label><input type="radio" name="gender" value="Female"/>Female</label>
</div>
</div>
<div class="form-group">
<label>Address</label>
<input type="text" class="form-control" value="<?php echo $fetch['address']?>" name="address"/>
</div>
</div>
</div>
<div style="clear:both;"></div>
<div class="modal-footer">
<button class="btn btn-warning" name="update"><span class="glyphicon glyphicon-update"></span> Update</button>
<button type="button" class="btn btn-danger" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Close</button>
</div>
</form>
</div>
</div>
</div>
<?php
}
?>
</tbody>
</table>
</div>
<div class="modal fade" id="form_modal" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<form method="POST" action="save_student.php">
<div class="modal-header">
<h3 class="modal-title">Add Student</h3>
</div>
<div class="modal-body">
<div class="col-md-2"></div>
<div class="col-md-8">
<div class="form-group">
<label>Firstname</label>
<input type="text" class="form-control" name="firstname"/>
</div>
<div class="form-group">
<label>Lastname</label>
<input type="text" class="form-control" name="lastname"/>
</div>
<div class="form-group">
<label>Gender</label>
<div class="radio">
<label><input type="radio" name="gender" value="Male" required="required"/>Male</label>
<label><input type="radio" name="gender" value="Female"/>Female</label>
</div>
</div>
<div class="form-group">
<label>Address</label>
<input type="text" class="form-control" name="address"/>
</div>
</div>
</div>
<div style="clear:both;"></div>
<div class="modal-footer">
<button class="btn btn-primary" name="save">Save</button>
<button type="button" class="btn btn-danger" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Close</button>
</div>
</form>
</div>
</div>
</div>
<script src="js/jquery-3.2.1.min.js"></script>
<script src="js/bootstrap.js"></script>
</body>
</html>
创建主要功能
此代码包含应用程序的主要功能。该代码由不同的功能组成:它可以添加数据输入,可以更新数据,还可以删除数据库中的数据。为此,只需在文本编辑器中复制并编写这些代码块,然后将其保存,如下所示。
save_student.php
<?php
require_once 'conn.php';
if(ISSET($_POST['save'])){
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$gender = $_POST['gender'];
$address = $_POST['address'];
$query = "INSERT INTO `student` (firstname, lastname, gender, address) VALUES(:firstname, :lastname, :gender, :address)";
$stmt = $conn->prepare($query);
$stmt->bindParam(':firstname', $firstname);
$stmt->bindParam(':lastname', $lastname);
$stmt->bindParam(':gender', $gender);
$stmt->bindParam(':address', $address);
$stmt->execute();
$conn = null;
header('location: index.php');
}
?>
update_student.php
<?php
require_once 'conn.php';
if(ISSET($_POST['update'])){
$student_id = $_POST['student_id'];
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$gender = $_POST['gender'];
$address = $_POST['address'];
$query = "UPDATE `student` SET `firstname` = :firstname, `lastname` = :lastname, `gender` = :gender, `address` = :address WHERE `student_id` = :student_id";
$stmt = $conn->prepare($query);
$stmt->bindParam(':firstname', $firstname);
$stmt->bindParam(':lastname', $lastname);
$stmt->bindParam(':gender', $gender);
$stmt->bindParam(':address', $address);
$stmt->bindParam(':student_id', $student_id);
$stmt->execute();
$conn = null;
header('location: index.php');
}
?>
delete.php
<?php
require_once 'conn.php';
if(ISSET($_REQUEST['id'])){
$query = "DELETE FROM `student` WHERE student_id = '$_REQUEST[id]'";
$stmt = $conn->prepare($query);
$stmt->execute();
$conn = null;
header('location: index.php');
}
?>
至此我们成功使用PDO 使用SQLite成功创建了一个简单CRUD。