php sqlite3 增加删除修改写成函数

在PHP中，使用SQLite3扩展进行数据库操作（包括增加、删除和修改数据）时，可以将这些操作封装成函数。这样可以提高代码的复用性和可维护性。以下是一些基本的示例，展示如何将这些操作封装成函数。

1. 连接到SQLite数据库
1. 增加数据
1. 删除数据

首先，创建一个函数来连接SQLite数据库。

function connectSQLite($dbFile = 'database.db') {
    try {
        $db = new SQLite3($dbFile);
        return $db;
    } catch (Exception $e) {
        die('无法打开数据库: ' . $e->getMessage());
    }
}

1. 连接到SQLite数据库
1. 增加数据
1. 删除数据

创建一个函数来向数据库中插入数据。

function insertData($db, $table, $data) {
    $keys = implode(', ', array_keys($data));
    $values = ':' . implode(', :', array_keys($data));
    $sql = "INSERT INTO $table ($keys) VALUES ($values)";
    $stmt = $db->prepare($sql);
    foreach ($data as $key => $value) {
        $stmt->bindValue(":$key", $value);
    }
    $result = $stmt->execute();
    return $result;
}

1. 连接到SQLite数据库
1. 增加数据
1. 删除数据

创建一个函数来从数据库中删除数据。

function deleteData($db, $table, $condition) {
    $where = '';
    foreach ($condition as $key => $value) {
        $where .= "$key = :$key AND ";
        $stmt->bindValue(":$key", $value);
    }
    $where = rtrim($where, ' AND '); // 移除最后的 'AND'和空格
    $sql = "DELETE FROM $table WHERE $where";
    $stmt = $db->prepare($sql);
    foreach ($condition as $key => $value) {
        $stmt->bindValue(":$key", $value);
    }
    $result = $stmt->execute();
    return $result;
}

修改数据
创建一个函数来更新数据库中的数据。

function updateData($db, $table, $data, $condition) {
    $set = '';
    foreach ($data as $key => $value) {
        $set .= "$key = :$key, ";
        $stmt->bindValue(":$key", $value);
    }
    $set = rtrim($set, ', '); // 移除最后的逗号和空格
    $where = '';
    foreach ($condition as $key => $value) {
        $where .= "$key = :$cond_$key AND ";
        $stmt->bindValue(":cond_$key", $value); // 使用不同的占位符前缀以区分绑定值，避免冲突
    }
    $where = rtrim($where, ' AND '); // 移除最后的 'AND'和空格
    $sql = "UPDATE $table SET $set WHERE $where";
    $stmt = $db->prepare($sql);
    foreach ($data as $key => $value) { // 重新绑定数据部分的值，因为我们已经重新设置了SQL语句的占位符部分
        $stmt->bindValue(":$key", $value);
    }
    foreach ($condition as $key => $value) { // 重新绑定条件部分的值，以防冲突问题（理论上这部分不需要重新绑定，因为上面的已经绑定了）
        $stmt->bindValue(":cond_$key", $value); // 但为了清晰和可能的冲突预防，再次绑定一次是安全的做法。
    }
    $result = $stmt->execute();
    return $result;
}

数据插入函数

使用预处理语句安全地插入数据

function insertData($db, $table, $data) {
    $columns = implode(', ', array_keys($data));
    $placeholders = ':' . implode(', :', array_keys($data));
    $sql = "INSERT INTO $table ($columns) VALUES ($placeholders)";
    $stmt = $db->prepare($sql);
    foreach ($data as $key => $value) {
        $stmt->bindValue(":$key", $value);
    }
    $result = $stmt->execute();
    return $result ? $db->lastInsertRowID() : false;
}

数据查询函数

支持多种查询方式

// 查询单条记录
function querySingle($db, $table, $conditions = []) {
    $sql = "SELECT * FROM $table";
    if (!empty($conditions)) {
        $where = [];
        foreach ($conditions as $key => $value) {
            $where[] = "$key = :$key";
        }
        $sql .= " WHERE " . implode(' AND ', $where);
    }
    $stmt = $db->prepare($sql);
    foreach ($conditions as $key => $value) {
        $stmt->bindValue(":$key", $value);
    }
    $result = $stmt->execute();
    return $result->fetchArray(SQLITE3_ASSOC);
}
// 查询多条记录
function queryAll($db, $table, $conditions = []) {
    $sql = "SELECT * FROM $table";
    if (!empty($conditions)) {
        $where = [];
        foreach ($conditions as $key => $value) {
            $where[] = "$key = :$key";
        }
        $sql .= " WHERE " . implode(' AND ', $where);
    }
    $stmt = $db->prepare($sql);
    foreach ($conditions as $key => $value) {
        $stmt->bindValue(":$key", $value);
    }
    $result = $stmt->execute();
    $rows = [];
    while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
        $rows[] = $row;
    }
    return $rows;
}

数据更新函数

安全地更新数据记录

function updateData($db, $table, $data, $conditions) {
    $setParts = [];
    foreach ($data as $key => $value) {
        $setParts[] = "$key = :set_$key";
    }
    $whereParts = [];
    foreach ($conditions as $key => $value) {
        $whereParts[] = "$key = :where_$key";
    }
    $sql = "UPDATE $table SET " . implode(', ', $setParts) . 
            " WHERE " . implode(' AND ', $whereParts);
    $stmt = $db->prepare($sql);
    // 绑定更新值
    foreach ($data as $key => $value) {
        $stmt->bindValue(":set_$key", $value);
    }
    // 绑定条件值
    foreach ($conditions as $key => $value) {
        $stmt->bindValue(":where_$key", $value);
    }
    $result = $stmt->execute();
    return $result ? $db->changes() : false;
}

数据删除函数

删除指定条件的记录

function deleteData($db, $table, $conditions) {
    $whereParts = [];
    foreach ($conditions as $key => $value) {
        $whereParts[] = "$key = :$key";
    }
    $sql = "DELETE FROM $table WHERE " . implode(' AND ', $whereParts);
    $stmt = $db->prepare($sql);
    foreach ($conditions as $key => $value) {
        $stmt->bindValue(":$key", $value);
    }
    $result = $stmt->execute();
    return $result ? $db->changes() : false;
}

完整使用示例

// 使用示例
$db = connectDB('mydatabase.db');
// 创建表（如果需要）
$db->exec("CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    age INTEGER
)");
// 插入数据
$newUser = [
    'name' => '张三',
    'email' => 'zhangsan@example.com',
    'age' => 25
];
$userId = insertData($db, 'users', $newUser);
echo "新用户ID: $userId\n";
// 查询数据
$user = querySingle($db, 'users', ['id' => 1]);
print_r($user);
// 更新数据
$updateResult = updateData($db, 'users', ['age' => 26], ['id' => 1]);
echo "更新了 $updateResult 条记录\n";
// 删除数据
$deleteResult = deleteData($db, 'users', ['id' => 2]);
echo "删除了 $deleteResult 条记录\n";
// 关闭连接
$db->close();

php sqlite3 增加删除 修改 写成函数

php sqlite3 增加删除修改写成函数