php編寫的mysqli增刪改查數(shù)據(jù)庫操作類示例
更新時間：2023年08月02日 11:15:34 作者：TANKING
這篇文章主要為大家介紹了php編寫的mysqli增刪改查數(shù)據(jù)庫操作類示例,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪
類文件
這是一個php深度封裝的MySQLi數(shù)據(jù)庫操作類，支持插入、刪除、查詢和更新操作，并且使用數(shù)組進(jìn)行參數(shù)傳遞，結(jié)合了預(yù)處理語句防止SQL注入。
Database.php
<?php
/**
* mySqli數(shù)據(jù)庫操作類
* 參數(shù)綁定防SQL注入
* 作者：TANKING
* 時間：2023-08-01
**/
class Database
{
private $host;
private $username;
private $password;
private $database;
private $conn;
// 構(gòu)造方法
public function __construct($host, $username, $password, $database)
{
$this->host = $host;
$this->username = $username;
$this->password = $password;
$this->database = $database;
$this->connect();
}
// 連接數(shù)據(jù)庫
public function connect()
{
$this->conn = new mysqli($this->host, $this->username, $this->password, $this->database);
if ($this->conn->connect_error) {
die("連接數(shù)據(jù)庫失?。? . $this->conn->connect_error);
}
}
// 斷開數(shù)據(jù)庫連接
public function disconnect()
{
$this->conn->close();
}
// Query方法
public function query($sql, $params = [])
{
$stmt = $this->conn->prepare($sql);
if ($stmt === false) {
throw new Exception("預(yù)處理失?。? . $this->conn->error);
}
// 綁定參數(shù)
if (!empty($params)) {
$paramTypes = '';
$bindParams = [];
foreach ($params as $param) {
if (is_int($param)) {
$paramTypes .= 'i'; // Integer
} elseif (is_float($param)) {
$paramTypes .= 'd'; // Double
} else {
$paramTypes .= 's'; // String
}
$bindParams[] = $param;
}
if (!empty($bindParams)) {
$stmt->bind_param($paramTypes, ...$bindParams);
}
}
$stmt->execute();
$result = $stmt->get_result();
if ($result === false) {
throw new Exception("執(zhí)行查詢失?。? . $stmt->error);
}
$data = [];
while ($row = $result->fetch_assoc()) {
$data[] = $row;
}
$stmt->close();
return $data;
}
// 查詢一條數(shù)據(jù)
public function selectOne($table, $conditions = [], $params = [], $fields = ['*'])
{
$limit = 1;
$result = $this->select($table, $conditions, $params, $limit, $fields);
if ($result && count($result) > 0) {
return $result[0];
}
return null;
}
// 查詢所有數(shù)據(jù)
public function selectAll($table, $conditions = [], $params = [], $fields = ['*'])
{
return $this->select($table, $conditions, $params, null, $fields);
}
// 高級查詢
public function select($table, $conditions = [], $params = [], $fields = ['*'], $limit = '', $orderBy = '')
{
$fields = implode(', ', $fields);
$whereClause = '';
if (!empty($conditions)) {
$whereClause = ' WHERE ' . implode(' AND ', $conditions);
}
$orderByClause = '';
if (!empty($orderBy)) {
$orderByClause = ' ORDER BY ' . $orderBy;
}
$limitClause = '';
if (!empty($limit)) {
$limitClause = ' LIMIT ' . $limit;
}
$sql = "SELECT $fields FROM $table $whereClause $orderByClause $limitClause";
$stmt = $this->conn->prepare($sql);
if ($stmt === false) {
die("預(yù)處理查詢失?。? . $this->conn->error);
}
$types = '';
$paramsToBind = [];
foreach ($params as $param) {
if (is_int($param)) {
$types .= 'i'; // Integer
} elseif (is_float($param)) {
$types .= 'd'; // Double
} else {
$types .= 's'; // String
}
$paramsToBind[] = $param;
}
array_unshift($paramsToBind, $types);
$bindResult = call_user_func_array([$stmt, 'bind_param'], $this->refValues($paramsToBind));
if ($bindResult === false) {
die("綁定參數(shù)失敗：" . $this->conn->error);
}
$stmt->execute();
$result = $stmt->get_result();
if ($result === false) {
die("執(zhí)行查詢失?。? . $stmt->error);
}
$data = [];
while ($row = $result->fetch_assoc()) {
$data[] = $row;
}
$stmt->close();
return $data;
}
// 插入數(shù)據(jù)
public function insert($table, $data = [])
{
if (empty($data)) {
die("插入數(shù)據(jù)失?。簲?shù)據(jù)為空");
}
$fields = implode(', ', array_keys($data));
$placeholders = implode(', ', array_fill(0, count($data), '?'));
$sql = "INSERT INTO $table ($fields) VALUES ($placeholders)";
$params = array_values($data);
$stmt = $this->conn->prepare($sql);
if ($stmt === false) {
die("預(yù)處理失敗：" . $this->conn->error);
}
$types = '';
$paramsToBind = [];
foreach ($params as $param) {
if (is_int($param)) {
$types .= 'i'; // Integer
} elseif (is_float($param)) {
$types .= 'd'; // Double
} else {
$types .= 's'; // String
}
$paramsToBind[] = $param;
}
array_unshift($paramsToBind, $types);
$bindResult = call_user_func_array([$stmt, 'bind_param'], $this->refValues($paramsToBind));
if ($bindResult === false) {
die("綁定參數(shù)失?。? . $this->conn->error);
}
// 插入結(jié)果
$result = $stmt->execute();
// 斷開數(shù)據(jù)庫連接
$stmt->close();
// 返回結(jié)果
return $result;
}
// 更新數(shù)據(jù)
public function update($table, $data = [], $conditions = [], $params = [])
{
if (empty($data)) {
die("更新數(shù)據(jù)失?。焊聰?shù)據(jù)為空");
}
$updateFields = implode(' = ?, ', array_keys($data)) . ' = ?';
$whereClause = '';
if (!empty($conditions)) {
$whereClause = ' WHERE ' . implode(' AND ', $conditions);
}
$sql = "UPDATE $table SET $updateFields $whereClause";
$updateParams = array_merge(array_values($data), $params);
$stmt = $this->conn->prepare($sql);
if ($stmt === false) {
die("預(yù)處理失?。? . $this->conn->error);
}
$types = '';
$paramsToBind = [];
foreach ($updateParams as $param) {
if (is_int($param)) {
$types .= 'i'; // Integer
} elseif (is_float($param)) {
$types .= 'd'; // Double
} else {
$types .= 's'; // String
}
$paramsToBind[] = $param;
}
array_unshift($paramsToBind, $types);
$bindResult = call_user_func_array([$stmt, 'bind_param'], $this->refValues($paramsToBind));
if ($bindResult === false) {
die("綁定參數(shù)失?。? . $this->conn->error);
}
$result = $stmt->execute();
$stmt->close();
return $result;
}
// 刪除數(shù)據(jù)
public function delete($table, $conditions = [], $params = [])
{
if (empty($conditions)) {
die("刪除數(shù)據(jù)失?。簞h除條件為空");
}
$whereClause = ' WHERE ' . implode(' AND ', $conditions);
$sql = "DELETE FROM $table $whereClause";
$stmt = $this->conn->prepare($sql);
if ($stmt === false) {
die("預(yù)處理查詢失?。? . $this->conn->error);
}
$types = '';
$paramsToBind = [];
foreach ($params as $param) {
if (is_int($param)) {
$types .= 'i'; // Integer
} elseif (is_float($param)) {
$types .= 'd'; // Double
} else {
$types .= 's'; // String
}
$paramsToBind[] = $param;
}
array_unshift($paramsToBind, $types);
$bindResult = call_user_func_array([$stmt, 'bind_param'], $this->refValues($paramsToBind));
if ($bindResult === false) {
die("綁定參數(shù)失敗：" . $this->conn->error);
}
$result = $stmt->execute();
$stmt->close();
return $result;
}
// 執(zhí)行原生語句
public function querySQL($sql)
{
$result = $this->conn->query($sql);
if ($result === false) {
die("執(zhí)行原生失?。? . $this->conn->error);
}
return $result;
}
// 數(shù)據(jù)綁定
private function refValues($arr)
{
if (strnatcmp(phpversion(), '5.3') >= 0) // Reference is required for PHP 5.3+
{
$refs = array();
foreach ($arr as $key => $value) {
$refs[$key] = &$arr[$key];
}
return $refs;
}
return $arr;
}
}
?>配置文件
Db.php
<?php
// 數(shù)據(jù)庫配置文件
$config = array(
'db_host' => 'xxx',
'db_user' => 'xxx',
'db_pass' => 'xxx',
'db_name' => 'xxx'
);
// 數(shù)據(jù)庫操作類
include 'Database.php';
?>使用示例
插入數(shù)據(jù)
insert.php
<?php
// 引入配置文件
require_once 'Db.php';
// 實(shí)例化Database類并連接數(shù)據(jù)庫
$db = new Database($config['db_host'], $config['db_user'], $config['db_pass'], $config['db_name']);
// 插入數(shù)據(jù)
$insertParams = array(
'stu_name' => '蔡徐坤',
'stu_sex' => '男',
'stu_from' => '廣州',
'stu_grade' => '一年級',
'stu_age' => 30,
);
// 執(zhí)行
$insertData = $db->insert('students', $insertParams);
// 執(zhí)行結(jié)果
if($insertData){
echo '插入成功！';
}else{
echo '插入失敗！'.$insertData;
}
// 關(guān)閉連接
$db->disconnect();
?>更新數(shù)據(jù)
update.php
<?php
// 引入配置文件
require_once 'Db.php';
// 實(shí)例化Database類并連接數(shù)據(jù)庫
$db = new Database($config['db_host'], $config['db_user'], $config['db_pass'], $config['db_name']);
// 被更新的數(shù)據(jù)
$updateData = array(
'stu_name' => '吳亦凡666',
'stu_age' => 35
);
// 綁定參數(shù)
$updateCondition = array('id = ?');
$updateParams = array(1);
// 執(zhí)行
$updateResult = $db->update('students', $updateData, $updateCondition, $updateParams);
// 執(zhí)行結(jié)果
if($updateResult){
echo '更新成功！';
}else{
echo '更新失??！'.$updateResult;
}
// 關(guān)閉連接
$db->disconnect();
?>刪除數(shù)據(jù)
delete.php
<?php
// 引入配置文件
require_once 'Db.php';
// 實(shí)例化Database類并連接數(shù)據(jù)庫
$db = new Database($config['db_host'], $config['db_user'], $config['db_pass'], $config['db_name']);
// 綁定參數(shù)
$conditions = array('id = ?');
$params = array(2);
// 執(zhí)行
$deleteResult = $db->delete('students', $conditions, $params);
if ($deleteResult) {
echo "刪除成功！";
} else {
echo "刪除失敗。";
}
// 關(guān)閉連接
$db->disconnect();
?>查詢一條數(shù)據(jù)
selectOne.php
<?php
// 引入配置文件
require_once 'Db.php';
// 實(shí)例化Database類并連接數(shù)據(jù)庫
$db = new Database($config['db_host'], $config['db_user'], $config['db_pass'], $config['db_name']);
// 準(zhǔn)備查詢的條件和字段
$conditions = array('id = ?');
$params = array(1);
$fields = array('id', 'stu_name', 'stu_age', 'stu_from');
// 執(zhí)行
$selectedData = $db->selectOne('students', $conditions, $params, $fields);
// 執(zhí)行結(jié)果
if ($selectedData) {
echo "查詢到一條數(shù)據(jù)：<br>";
echo "ID: " . $selectedData['id'] . "<br>";
echo "stu_name: " . $selectedData['stu_name'] . "<br>";
echo "stu_age: " . $selectedData['stu_age'] . "<br>";
echo "stu_from: " . $selectedData['stu_from'] . "<br>";
} else {
echo "未查詢到數(shù)據(jù)。";
}
// 關(guān)閉連接
$db->disconnect();
?>查詢所有數(shù)據(jù)
selectAll.php
<?php
// 引入配置文件
require_once 'Db.php';
// 實(shí)例化Database類并連接數(shù)據(jù)庫
$db = new Database($config['db_host'], $config['db_user'], $config['db_pass'], $config['db_name']);
// 準(zhǔn)備查詢的條件和字段
$conditions = array('stu_sex = ?');
$params = array('男');
$fields = array('id', 'stu_name', 'stu_age', 'stu_from');
// 執(zhí)行
$selectedData = $db->selectAll('students', $conditions, $params, $fields);
// 執(zhí)行結(jié)果
if ($selectedData) {
echo "查詢到的所有數(shù)據(jù)：<br>";
foreach ($selectedData as $data) {
echo "ID: " . $data['id'] . "<br>";
echo "stu_name: " . $data['stu_name'] . "<br>";
echo "stu_age: " . $data['stu_age'] . "<br>";
echo "stu_from: " . $data['stu_from'] . "<br>";
echo "<br>";
}
} else {
echo "未查詢到數(shù)據(jù)。";
}
// 關(guān)閉連接
$db->disconnect();
?>高級查詢
select.php
<?php
// 引入配置文件
require_once 'Db.php';
// 實(shí)例化Database類并連接數(shù)據(jù)庫
$db = new Database($config['db_host'], $config['db_user'], $config['db_pass'], $config['db_name']);
// 準(zhǔn)備查詢的條件和字段
$conditions = array('stu_age > ?');
$params = array(25);
$fields = array('id', 'stu_name', 'stu_age', 'stu_from');
$limit = 3; // 查詢限制條數(shù)
$orderBy = 'id DESC'; // 排序方式
// 執(zhí)行
$selectedData = $db->select('students', $conditions, $params, $fields, $limit, $orderBy);
// 執(zhí)行結(jié)果
if ($selectedData) {
echo "查詢到的數(shù)據(jù)：<br>";
foreach ($selectedData as $data) {
echo "ID: " . $data['id'] . "<br>";
echo "stu_name: " . $data['stu_name'] . "<br>";
echo "stu_age: " . $data['stu_age'] . "<br>";
echo "stu_from: " . $data['stu_from'] . "<br>";
echo "<br>";
}
} else {
echo "未查詢到數(shù)據(jù)。";
}
// 關(guān)閉連接
$db->disconnect();
?>執(zhí)行原生語句
querySQL.php
<?php
// 引入配置文件
require_once 'Db.php';
// 實(shí)例化Database類并連接數(shù)據(jù)庫
$db = new Database($config['db_host'], $config['db_user'], $config['db_pass'], $config['db_name']);
// 執(zhí)行
$sql = "SELECT * FROM students WHERE stu_age > 25";
$result = $db->querySQL($sql);
// 執(zhí)行結(jié)果
if ($result->num_rows > 0) {
echo "查詢到的數(shù)據(jù)：<br>";
while ($data = $result->fetch_assoc()) {
echo "ID: " . $data['id'] . "<br>";
echo "stu_name: " . $data['stu_name'] . "<br>";
echo "stu_age: " . $data['stu_age'] . "<br>";
echo "stu_from: " . $data['stu_from'] . "<br>";
echo "<br>";
}
} else {
echo "未查詢到數(shù)據(jù)。";
}
// 關(guān)閉連接
$db->disconnect();
?>以上就是php編寫的mysqli增刪改查數(shù)據(jù)庫操作類示例的詳細(xì)內(nèi)容，更多關(guān)于php mysqli增刪改查的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
php 安全過濾函數(shù)代碼，防止用戶惡意輸入內(nèi)容。2011-05-05
php實(shí)現(xiàn)的mysqldb讀寫分離操作類示例
這篇文章主要介紹了php實(shí)現(xiàn)的mysqldb讀寫分離操作類,結(jié)合實(shí)例形式分析了php針對數(shù)據(jù)庫的讀寫分離操作實(shí)現(xiàn)技巧,并給出了該封裝類的具體使用方法,需要的朋友可以參考下2017-02-02
這篇文章主要介紹了PHP中header用法,總結(jié)分析了header函數(shù)的基本功能與相應(yīng)的使用技巧,需要的朋友可以參考下2016-05-05
php 判斷頁面或圖片是否經(jīng)過gzip壓縮的方法
下面小編就為大家?guī)硪黄猵hp 判斷頁面或圖片是否經(jīng)過gzip壓縮的方法。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-04-04
這篇文章主要介紹了PHP編程獲取各個時間段具體時間的方法,結(jié)合實(shí)例形式分析了基于date與strtotime函數(shù)進(jìn)行日期時間運(yùn)算的相關(guān)操作技巧,需要的朋友可以參考下2017-05-05
六酷社區(qū)論壇HOME頁清新格調(diào)免費(fèi)版 下載
六酷社區(qū)論壇HOME頁清新格調(diào)免費(fèi)版 下載...2007-03-03
PHP高精確度運(yùn)算BC函數(shù)庫實(shí)例詳解
這篇文章主要介紹了PHP高精確度運(yùn)算BC函數(shù)庫,結(jié)合實(shí)例形式分析了BC函數(shù)庫中bccomp、bcadd、bcsub、bcmod、bcdiv、bcmul等函數(shù)的功能以及高精度數(shù)學(xué)運(yùn)算使用方法,需要的朋友可以參考下2017-08-08
php中數(shù)據(jù)庫連接方式pdo和mysqli對比分析
這篇文章主要介紹了php中數(shù)據(jù)庫連接方式pdo和mysqli從各個方面進(jìn)行了對比分析，十分全面，這里推薦給大家，有需要的小伙伴來參考下。2015-02-02
這篇文章主要介紹了PHP中引用類型和值類型功能與用法,簡單分析了php引用類型和值類型的概念、功能、使用方法及相關(guān)操作注意事項(xiàng),需要的朋友可以參考下2019-02-02
PHP把MSSQL數(shù)據(jù)導(dǎo)入到MYSQL的方法
這篇文章主要介紹了PHP把MSSQL數(shù)據(jù)導(dǎo)入到MYSQL的方法,分別列舉了兩個實(shí)例實(shí)現(xiàn)將MSSQL數(shù)據(jù)導(dǎo)入到MYSQL的功能,是非常實(shí)用的技巧,具有一定的參考借鑒價值,需要的朋友可以參考下2014-12-12