How can PHP achieve database CRUD operations?

2 years ago
Sophia Anderson
2 minutes

In PHP, you can use either MySQLi or PDO to connect and operate with databases. Here are some basic examples:

  1. Connect to the database
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

// 使用MySQLi连接数据库
$conn = new mysqli($servername, $username, $password, $dbname);

// 使用PDO连接数据库
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    echo "连接数据库失败: " . $e->getMessage();
}
  1. Insert data.
$sql = "INSERT INTO your_table (column1, column2, column3) VALUES ('value1', 'value2', 'value3')";

// 使用MySQLi
if ($conn->query($sql) === TRUE) {
    echo "数据插入成功";
} else {
    echo "数据插入失败: " . $conn->error;
}

// 使用PDO
if ($conn->exec($sql)) {
    echo "数据插入成功";
} else {
    echo "数据插入失败";
}
  1. Data query:
$sql = "SELECT * FROM your_table";

// 使用MySQLi
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "列1: " . $row["column1"]. " 列2: " . $row["column2"]. " 列3: " . $row["column3"]. "<br>";
    }
} else {
    echo "没有数据";
}

// 使用PDO
$stmt = $conn->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (count($result) > 0) {
    foreach ($result as $row) {
        echo "列1: " . $row["column1"]. " 列2: " . $row["column2"]. " 列3: " . $row["column3"]. "<br>";
    }
} else {
    echo "没有数据";
}
  1. Update data.
$sql = "UPDATE your_table SET column1='new_value1', column2='new_value2' WHERE condition";

// 使用MySQLi
if ($conn->query($sql) === TRUE) {
    echo "数据更新成功";
} else {
    echo "数据更新失败: " . $conn->error;
}

// 使用PDO
if ($conn->exec($sql)) {
    echo "数据更新成功";
} else {
    echo "数据更新失败";
}
  1. Delete data.
$sql = "DELETE FROM your_table WHERE condition";

// 使用MySQLi
if ($conn->query($sql) === TRUE) {
    echo "数据删除成功";
} else {
    echo "数据删除失败: " . $conn->error;
}

// 使用PDO
if ($conn->exec($sql)) {
    echo "数据删除成功";
} else {
    echo "数据删除失败";
}

Please note that in the above example, “your_table” is the name of the database table you want to operate on, “column1”, “column2” are column names of the table, “value1”, “value2” are the values to be inserted or updated, and “condition” is the condition statement to select the data rows to update or delete. You need to replace these values with your own actual values.

bannerAds