以下是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();