mysqli预处理增删改查
时间: 2023-07-07 15:21:54 浏览: 47
当使用MySQLi进行增删改查时,建议使用预处理语句来保护应用程序免受SQL注入攻击。以下是使用MySQLi预处理语句的示例:
1. 连接数据库
```php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);
// 检测连接
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
```
2. 执行查询
```php
// 准备SQL语句和绑定参数
$stmt = $conn->prepare("SELECT * FROM myTable WHERE id=?");
$stmt->bind_param("i", $id);
// 设置参数并执行查询
$id = 1;
$stmt->execute();
// 获取结果集
$result = $stmt->get_result();
// 输出结果
while ($row = $result->fetch_assoc()) {
echo "id: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
}
```
3. 执行插入
```php
// 准备SQL语句和绑定参数
$stmt = $conn->prepare("INSERT INTO myTable (name, email, phone) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $name, $email, $phone);
// 设置参数并执行插入
$name = "John Doe";
$email = "john@example.com";
$phone = "555-555-5555";
$stmt->execute();
// 输出插入的行数
echo "New records created successfully. Rows inserted: " . mysqli_affected_rows($conn);
```
4. 执行更新
```php
// 准备SQL语句和绑定参数
$stmt = $conn->prepare("UPDATE myTable SET email=?, phone=? WHERE id=?");
$stmt->bind_param("ssi", $email, $phone, $id);
// 设置参数并执行更新
$id = 1;
$email = "john@example.com";
$phone = "555-555-5555";
$stmt->execute();
// 输出更新的行数
echo "Records updated successfully. Rows affected: " . mysqli_affected_rows($conn);
```
5. 执行删除
```php
// 准备SQL语句和绑定参数
$stmt = $conn->prepare("DELETE FROM myTable WHERE id=?");
$stmt->bind_param("i", $id);
// 设置参数并执行删除
$id = 1;
$stmt->execute();
// 输出删除的行数
echo "Records deleted successfully. Rows affected: " . mysqli_affected_rows($conn);
```
注意:在使用预处理语句时,要注意使用正确的参数类型。在上面的示例中,“s”表示字符串类型,“i”表示整型。您可以根据需要更改参数类型。