建站
咨詢
售后
建站咨詢
新聞中心
隨著互聯(lián)網(wǎng)的迅猛發(fā)展，數(shù)據(jù)庫技術(shù)已經(jīng)成為了關(guān)鍵的技術(shù)之一。在互聯(lián)網(wǎng)應(yīng)用的開發(fā)中，數(shù)據(jù)庫的操作是必不可少的一部分。在PHP開發(fā)中，MySQL是最常用的數(shù)據(jù)庫之一。不過，直接使用MySQL有時候會遇到些問題，因此我們可以通過mysqli數(shù)據(jù)庫的封裝來實(shí)現(xiàn)高效的MySQL操作。
站在用戶的角度思考問題，與客戶深入溝通，找到慶城網(wǎng)站設(shè)計(jì)與慶城網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗(yàn)，讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個性化、用戶體驗(yàn)好的作品，建站類型包括：成都網(wǎng)站建設(shè)、網(wǎng)站建設(shè)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、國際域名空間、虛擬主機(jī)、企業(yè)郵箱。業(yè)務(wù)覆蓋慶城地區(qū)。
本篇文章將會分享一下如何封裝mysqli數(shù)據(jù)庫來實(shí)現(xiàn)高效操作MySQL。
1. 面向?qū)ο蟮腗ySQLi數(shù)據(jù)庫
在PHP中，使用MySQLi擴(kuò)展可以很方便地操作MySQL數(shù)據(jù)庫。但是，為了在多個腳本中使用相同的數(shù)據(jù)庫配置，我們需要對它進(jìn)行封裝。采用面向?qū)ο缶幊棠茏尨a更加歸一化和易讀，并便于維護(hù)。
在封裝MySQLi數(shù)據(jù)庫時，我們會創(chuàng)建一個Database類，并包含以下屬性和方法：
屬性：
– host: 連接MySQL服務(wù)器的地址
– username: 連接MySQL服務(wù)器的用戶名
– password: 連接MySQL服務(wù)器的密碼
– database: 連接的MySQL數(shù)據(jù)庫名
– conn: MySQLi連接對象
方法：
– __construct(): 構(gòu)造函數(shù)，用于連接MySQL服務(wù)器并連接到指定的數(shù)據(jù)庫
– query($query): 執(zhí)行查詢SQL語句
– fetch($result): 獲取單行數(shù)據(jù)的關(guān)聯(lián)數(shù)組
– fetchAll($result): 獲取多行數(shù)據(jù)的關(guān)聯(lián)數(shù)組
– insert($table, $data): 插入數(shù)據(jù)
– update($table, $data, $where): 更新數(shù)據(jù)
– delete($table, $where): 刪除數(shù)據(jù)
– error(): 獲取MySQLi連接的最后一個錯誤信息
下面是一個示例Database類的代碼片段：
“`
class Database {
private $host = “l(fā)ocalhost”;
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數(shù)據(jù)庫
在使用封裝的MySQLi數(shù)據(jù)庫時，我們只需要實(shí)例化上面的Database類。然后，我們可以使用它的方法進(jìn)行需要的操作。
下面是一個示例代碼片段，演示了如何使用封裝的MySQLi數(shù)據(jù)庫：
“`
$database = new Database();
// 查詢數(shù)據(jù)
$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’];
}
// 插入數(shù)據(jù)
$database->insert(“users”, array(
“username” => “user1”,
“password” => “password1”
));
// 更新數(shù)據(jù)
$database->update(“users”, array(
“password” => “password2”
), “`id` = 1”);
// 刪除數(shù)據(jù)
$database->delete(“users”, “`id` = 1”);
“`
封裝MySQLi數(shù)據(jù)庫的好處：
使用封裝的MySQLi數(shù)據(jù)庫可以帶來一些好處，例如：簡化代碼，減少代碼的錯誤率，可以管理和重用代碼，提高代碼的可讀性和可維護(hù)性。
此外，封裝MySQLi數(shù)據(jù)庫可以使得程序員專注于數(shù)據(jù)庫操作邏輯而不是連接/斷開連接的代碼。
結(jié)論：
在封裝MySQLi數(shù)據(jù)庫中，我們構(gòu)造了一個Database類來連接MySQL服務(wù)器并連接到指定的數(shù)據(jù)庫，然后可以通過其方法來進(jìn)行相應(yīng)的操作(如查詢、插入、更新和刪除數(shù)據(jù))。使用封裝MySQLi數(shù)據(jù)庫可以帶來許多好處，例如簡化操作邏輯，減少錯誤范圍，以及提高代碼的可讀性和可維護(hù)性。
相關(guān)問題拓展閱讀：
- php表單寫入mysql數(shù)據(jù)庫的代碼
php表單寫入mysql數(shù)據(jù)庫的代碼
陸侍
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 = “l(fā)ocalhost”;
//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 = “l(fā)ocalhost”;
//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 = “l(fā)ocalhost”;
//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;
?>
–創(chuàng)建數(shù)據(jù)庫test, 將此文件存為test.sql 導(dǎo)入數(shù)據(jù)庫，或者手動創(chuàng)建表結(jié)構(gòu)
— 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’=>’不存在數(shù)據(jù)庫’,
‘a(chǎn)ge’=>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(“連接數(shù)據(jù)庫錯誤 %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 “執(zhí)行失敗”.$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();
//根據(jù)表結(jié)構(gòu)獲取字段類型列表
$fieldTypeArr = $this->fieldTypeArr($tableName);
$this->co($fieldTypeArr);
//之一種、循環(huán)Table存在字段
foreach($fieldTypeArr as $field=>$type){
$param = @$data?:’編程夜未眠’;//傳入?yún)?shù)存在該字段
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數(shù)據(jù)庫封裝的介紹就聊到這里吧，感謝你花時間閱讀本站內(nèi)容，更多關(guān)于mysqli數(shù)據(jù)庫封裝,PHP技術(shù)分享：如何封裝mysqli數(shù)據(jù)庫實(shí)現(xiàn)高效操作,php表單寫入mysql數(shù)據(jù)庫的代碼的信息別忘了在本站進(jìn)行查找喔。
創(chuàng)新互聯(lián)服務(wù)器托管擁有成都T3+級標(biāo)準(zhǔn)機(jī)房資源，具備完善的安防設(shè)施、三線及BGP網(wǎng)絡(luò)接入帶寬達(dá)10T，機(jī)柜接入千兆交換機(jī)，能夠有效保證服務(wù)器托管業(yè)務(wù)安全、可靠、穩(wěn)定、高效運(yùn)行；創(chuàng)新互聯(lián)專注于成都服務(wù)器托管租用十余年，得到成都等地區(qū)行業(yè)客戶的一致認(rèn)可。
文章標(biāo)題：PHP技術(shù)分享：如何封裝mysqli數(shù)據(jù)庫實(shí)現(xiàn)高效操作(mysqli數(shù)據(jù)庫封裝)
分享網(wǎng)址：http://www.dlmjj.cn/article/dhjcgsh.html
