php mysqli函数的高级用法示例

以下是PHP的MySQLi函数的一些高级用法示例：

1. 预处理语句和参数绑定

$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();

2. 使用事务

$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();

3. 使用存储过程和命名参数

首先，创建一个带有命名参数的存储过程：

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();