117笔记问答以下是PHP的MySQLi函数的一些高级用法示例：
- 预处理语句和参数绑定
$mysqli = new mysqli("localhost", "user", "password", "database");
// 检查连接
if ($mysqli->connect_error) {
die("连接失败: " . $mysqli->connect_error);
}
// 预处理SQL语句
$stmt = $mysqli->prepare("INSERT INTO users (username, password) VALUES (?, ?)");
// 绑定参数
$stmt->bind_param("ss", $username, $password);
// 设置参数并执行
$username = "john_doe";
$password = "secret";
$stmt->execute();
echo "新记录插入成功";
// 关闭连接
$mysqli->close();
- 使用事务
$mysqli = new mysqli("localhost", "user", "password", "database");
// 检查连接
if ($mysqli->connect_error) {
die("连接失败: " . $mysqli->connect_error);
}
// 开始事务
$mysqli->begin_transaction();
try {
// 插入第一条记录
$stmt = $mysqli->prepare("INSERT INTO users (username, password) VALUES (?, ?)");
$stmt->bind_param("ss", $username1, $password1);
$username1 = "john_doe";
$password1 = "secret";
$stmt->execute();
// 插入第二条记录
$stmt = $mysqli->prepare("INSERT INTO orders (user_id, product) VALUES (?, ?)");
$stmt->bind_param("is", $userId, $product);
$userId = 1;
$product = "laptop";
$stmt->execute();
// 提交事务
$mysqli->commit();
} catch (Exception $e) {
// 发生错误，回滚事务
$mysqli->rollback();
echo "Error: " . $e->getMessage();
}
// 关闭连接
$mysqli->close();
- 使用存储过程和命名参数
首先，创建一个带有命名参数的存储过程：
DELIMITER //
CREATE PROCEDURE insert_user_with_name(IN p_username VARCHAR(255), IN p_password VARCHAR(255))
BEGIN
INSERT INTO users (username, password) VALUES (p_username, p_password);
END //
DELIMITER ;
然后，在PHP中调用该存储过程：
$mysqli = new mysqli("localhost", "user", "password", "database");
// 检查连接
if ($mysqli->connect_error) {
die("连接失败: " . $mysqli->connect_error);
}
// 调用存储过程
$username = "john_doe";
$password = "secret";
$stmt = $mysqli->prepare("CALL insert_user_with_name(?, ?)");
$stmt->bind_param("ss", $username, $password);
$stmt->execute();
echo "新用户插入成功";
// 关闭连接
$mysqli->close();