PHP技术分享：如何封装mysqli数据库实现高效操作 (mysqli数据库封装)
随着互联网的迅猛发展，数据库技术已经成为了关键的技术之一。在互联网应用的开发中，数据库的操作是必不可少的一部分。在PHP开发中，MySQL是最常用的数据库之一。不过，直接使用MySQL有时候会遇到些问题，因此我们可以通过mysqli数据库的封装来实现高效的MySQL操作。
本篇文章将会分享一下如何封装mysqli数据库来实现高效操作MySQL。
1. 面向对象的MySQLi数据库
在PHP中，使用MySQLi扩展可以很方便地操作MySQL数据库。但是，为了在多个脚本中使用相同的数据库配置，我们需要对它进行封装。采用面向对象编程能让代码更加归一化和易读，并便于维护。
在封装MySQLi数据库时，我们会创建一个Database类，并包含以下属性和方法：
属性：
– host: 连接MySQL服务器的地址
– username: 连接MySQL服务器的用户名
– password: 连接MySQL服务器的密码
– database: 连接的MySQL数据库名
– conn: MySQLi连接对象
方法：
– __construct(): 构造函数，用于连接MySQL服务器并连接到指定的数据库
– query($query): 执行查询SQL语句
– fetch($result): 获取单行数据的关联数组
– fetchAll($result): 获取多行数据的关联数组
– insert($table, $data): 插入数据
– update($table, $data, $where): 更新数据
– delete($table, $where): 删除数据
– error(): 获取MySQLi连接的最后一个错误信息
下面是一个示例Database类的代码片段：
“`
class Database {
private $host = “localhost”;
private $username = “root”;
private $password = “password”;
private $database = “test”;
private $conn;
public function __construct() {
$this->conn = new mysqli($this->host, $this->username, $this->password, $this->database);
if ($this->conn->connect_error) {
die(“Connect fled: ” . $this->conn->connect_error);
}
$this->conn->set_charset(“utf8”);
}
public function query($query) {
$result = $this->conn->query($query);
if (!$result) {
return false;
}
return $result;
}
public function fetch($result) {
return $result->fetch_assoc();
}
public function fetchAll($result) {
$rows = array();
while ($row = $result->fetch_assoc()) {
$rows[] = $row;
}
return $rows;
}
public function insert($table, $data) {
$fields = array();
$values = array();
foreach ($data as $field => $value) {
$fields[] = “`” . $field . “`”;
$values[] = “‘” . $this->conn->real_escape_string($value) . “‘”;
}
$query = “INSERT INTO ” . $table . ” (” . implode(“, “, $fields) . “) VALUES (” . implode(“, “, $values) . “)”;
return $this->query($query);
}
public function update($table, $data, $where) {
$assignments = array();
foreach ($data as $field => $value) {
$assignments[] = “`” . $field . “` = ‘” . $this->conn->real_escape_string($value) . “‘”;
}
$query = “UPDATE ” . $table . ” SET ” . implode(“, “, $assignments) . ” WHERE ” . $where;
return $this->query($query);
}
public function delete($table, $where) {
$query = “DELETE FROM ” . $table . ” WHERE ” . $where;
return $this->query($query);
}
public function error() {
return $this->conn->error;
}
}
“`
2. 使用封装的MySQLi数据库
在使用封装的MySQLi数据库时，我们只需要实例化上面的Database类。然后，我们可以使用它的方法进行需要的操作。
下面是一个示例代码片段，演示了如何使用封装的MySQLi数据库：
“`
$database = new Database();
// 查询数据
$result = $database->query(“SELECT * FROM `users` WHERE `id` = 1”);
if ($result === false) {
echo “Query error: ” . $database->error();
} else {
$user = $database->fetch($result);
echo $user[‘username’];
}
// 插入数据
$database->insert(“users”, array(
“username” => “user1”,
“password” => “password1”
));
// 更新数据
$database->update(“users”, array(
“password” => “password2”
), “`id` = 1”);
// 删除数据
$database->delete(“users”, “`id` = 1”);
“`
封装MySQLi数据库的好处：
使用封装的MySQLi数据库可以带来一些好处，例如：简化代码，减少代码的错误率，可以管理和重用代码，提高代码的可读性和可维护性。
此外，封装MySQLi数据库可以使得程序员专注于数据库操作逻辑而不是连接/断开连接的代码。
结论：
在封装MySQLi数据库中，我们构造了一个Database类来连接MySQL服务器并连接到指定的数据库，然后可以通过其方法来进行相应的操作(如查询、插入、更新和删除数据)。使用封装MySQLi数据库可以带来许多好处，例如简化操作逻辑，减少错误范围，以及提高代码的可读性和可维护性。
相关问题拓展阅读：
php表单写入mysql数据库的代码
陆侍
label{display:inline-block;width:100px;margin-bottom:10px;}
Add students
–>
–>
First Name
Last Name
department
—
‘;
echo $last_name;
echo ”;
echo $department;
echo ”;
echo $email;
echo ”;
$servername = “localhost”;
//Your database username 轿迹and password
//$username = “username”;
//$password = “password”;
$username = “tester”;
$password = “testerPassword”;
//your database name
$dbname = “test”;
$tablename =”student”;
// Create connection
$connect = mysqli_connect($servername, $username, $password, $dbname);
if (!$connect) {
die(“Connection failed: ” . mysqli_connect_error());
}
//Execute the query
$sql=”INSERT INTO $tablename (first_name,last_name,department,email)
VALUES(‘$first_name’,’$last_name’,’$department’,’$email’)”;
if (mysqli_query($connect, $sql)) {
echo “Hooray! New record is inserted to database successfully. Please check database.”;
} else {
echo “Error: ” . $sql . “” . mysqli_error($connect);
}
mysqli_close($connect);
?>
‘;
echo $last_name;
echo ”;
echo $department;
echo ”;
echo $email;
echo ”;
$servername = “localhost”;
//Your database username and password
//$username = “username”;
//$password = “password”;
$username = “tester”;
$password = “testerPassword”;
//database name
$dbname = “test”;
$tablename =”student”;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}
$sql=”INSERT INTO $tablename (first_name,last_name,department,email)
VALUES(‘$first_name’,’$last_name’,’$department’,’$email’)”;
if ($conn->query($sql) === TRUE) {
echo “New record created successfully”;
} else {
echo “Error: ” . $sql . “
” . $conn->error;
}
$conn->close();
?>
‘;
echo $last_name;
echo ”;
echo $department;
echo ”;
echo $email;
echo ”;
$servername = “localhost”;
//Your database username and password
//$username = “username”;
//$password = “password”;
$username = “tester”;
$password = “testerPassword”;
//your database name
$dbname = “test”;
$tablename =”student”;
// Create connection
try {
$conn = new PDO(“mysql:host=$servername;dbname=$dbname”, $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql=”INSERT INTO $tablename (first_name,last_name,department,email)
VALUES(‘$first_name’,’$last_name’,’$department’,’$email’)”;
// use exec()
$conn->exec($sql);
echo “New record created successfully”;
}
catch(PDOException $e)
{
echo $sql . “
” . $e->getMessage();
}
$conn = null;
?>
–创建数据库test, 将此文件存为test.sql 导入数据库，或者手动创建表结构
— phpMyAdmin SQL Dump
— version 4.7.4
—
—
— Host: 127.0.0.1:3306
— Generation Time: Mar 12, 2023 at 04:04 AM
— Server version: 5.7.19
— PHP Version: 7.1.9
SET SQL_MODE = “NO_AUTO_VALUE_ON_ZERO”;
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = “+00:00”;
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
—
— Database: `test`
—
—
—
— Table structure for table `student`
—
DROP TABLE IF EXISTS `student`;
CREATE TABLE IF NOT EXISTS `student` (
`id` tinyint(3) UNSIGNED NOT NULL AUTO_INCREMENT,
`first_name` varchar(20) NOT NULL,
`last_name` varchar(20) NOT NULL,
`department` varchar(50) NOT NULL,
`email` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
—
— Dumping data for table `student`
—
INSERT INTO `student` (`id`, `first_name`, `last_name`, `department`, `email`) VALUES
(1, ‘first1’, ‘last1’, ‘cs’, ‘);
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
‘白如意’,
‘grade’=>’新手村一级’,
‘girlfriend’=>’不存在数据库’,
‘age’=>18
);
$Student->add($data);
//myDB.class.php
class myDB{
public $tableName = ”;
public function __construct($tableName){
$this->tableName = $tableName;
}
public function db(){
$config = array(
‘host’=>’127.0.0.1’,
‘user’=>’root’,
‘password’=>’root’,
‘database’=>’test’,
‘port’=>3306,
);
$set = array_values($config);
$mysqli = new mysqli(…$set);
$query = ‘SET NAMES UTF8’;
$mysqli->query($query);
if($mysqli->errno){
printf(“连接数据库错误 %s”,$mysqli->郑带error);
exit;
}
return $mysqli;
}
public function add($data){
$mysqli = $this->db();
$tableName = $this->tableName;
$dataArr = $this->filterFromTable($tableName,$data);
$this->co($dataArr);
//获得$sql ,$typeList,$needData
extract($dataArr);
$stmt = $mysqli->stmt_init();
$stmt->prepare($sql);
$stmt->bind_param($typeList,…$needData);
if ( $result = $stmt->execute() ){
$insert_id = $stmt->insert_id;
echo “成功插入ID”.$insert_id;
}else {
echo “执行失败”.$stmt->errno;
echo ”;
echo $stmt->error;
$insert_id = 0;
}
return $insert_id;
}
public function filterFromTable($tableName,$data){
$sqlField = ”;//字段: a,b,c
$sqlQ = ”;//问号: ?,?,?
$typeList = ”;//字段的类型: ssdib
$needData = array();
//根据表结构获取字段类型列表
$fieldTypeArr = $this->fieldTypeArr($tableName);
$this->co($fieldTypeArr);
//之一种、循环Table存在字段
foreach($fieldTypeArr as $field=>$type){
$param = @$data?:’编程夜未眠’;//传入参数存在该字段
if($param != ‘编程夜未眠’){
$sqlField .= $field.’,’;
$sqlQ .= ‘?,’;
$typeList .= $type;
$needData = $param;
}
含判 }
$sqlField = substr( $sqlField,0,strlen($sqlField)-1 );
$sqlQ = substr( $sqlQ,0,strlen($sqlQ)-1 );
$sql = “INSERT INTO {$tableName}({$sqlField})
VALUES({$sqlQ})”;
$dataArr = array(
‘sql’=>$sql,
‘typeList’=>$typeList,
‘needData’=>$needData,
);
return $dataArr;
}
public function fieldTypeArr($tableName){
$arr= array();
$mysqli = $this->db();
$sql = “DESC {$tableName}”;
$result_obj = $mysqli->query($sql);
while($row = $result_obj->fetch_object() ){
$type = $row->Type;
$field= $row->Field;
$str = $this->oneFieldType($type);
$arr = $str;
}
$mysqli->close();
return $arr;
}
public function oneFieldType($type){
$str = ”;
if(strstr($type,’int’)){
$str = ‘i’;
}else if( strstr($type,’float’) || strstr($type,’decimal’) || strstr($type,’double’) ){
$str = ‘d’;
}else if( strstr($type,’blob’) ){
$str = ‘b’;
}else{
$str = ‘s’;
}
return $str;
}
public function co($value){
echo ”;
print_r($value);
echo ”;
}
}//End Class
mysqli数据库封装的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于mysqli数据库封装,PHP技术分享：如何封装mysqli数据库实现高效操作,php表单写入mysql数据库的代码的信息别忘了在本站进行查找喔。
