How to perform add, delete, modify and check operations in php+mysql

The combination of PHP and MySQL is the foundation of modern web development. As a server-side programming language, PHP can process various data and logic, store and retrieve data in databases including MySQL. This article will introduce you how to use PHP and MySQL to perform add, modify, delete, and query operations.

1. Connect to the MySQL database

Before using MySQL, PHP needs to connect to the MySQL database. We can achieve connection in two ways: MySQLi extension and PDO extension.

Use MySQLi extension:

$servername = "localhost"; // 数据库主机名
$username = "username"; // 数据库用户名
$password = "password"; // 数据库密码
$dbname = "myDB"; // 数据库名

// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);

// 检查连接是否成功
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

echo "Connected successfully";

Use PDO extension:

$servername = "localhost"; // 数据库主机名
$username = "username"; // 数据库用户名
$password = "password"; // 数据库密码
$dbname = "myDB"; // 数据库名

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // 设置 PDO 错误模式为异常
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

2. Add (INSERT) data

To add new data to the MySQL database OK, we need to use the INSERT statement. In PHP, we can use MySQLi or PDO extensions to implement data insertion operations.

Use MySQLi extension:

// SQL 插入语句
$sql = "INSERT INTO myTable (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";

if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

Use PDO extension:

// SQL 插入语句
$sql = "INSERT INTO myTable (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";

// 使用 PDO::exec() 方法
if ($conn->exec($sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

3. Modify (UPDATE) data

To modify the data rows in the MySQL database, We need to use UPDATE statement. In PHP, we can use MySQLi or PDO extensions to implement data modification operations.

Use MySQLi extension:

// SQL 更新语句
$sql = "UPDATE myTable SET lastname='Doe2' WHERE id=1";

if (mysqli_query($conn, $sql)) {
    echo "Record updated successfully";
} else {
   echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

Use PDO extension:

// SQL 更新语句
$sql = "UPDATE myTable SET lastname='Doe2' WHERE id=1";

// 使用 PDO::exec() 方法
if ($conn->exec($sql)) {
    echo "Record updated successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

4. Delete (DELETE) data

To delete data rows from the MySQL database, We need to use DELETE statement. In PHP, we can perform data deletion operations using MySQLi or PDO extension.

Use MySQLi extension:

// SQL 删除语句
$sql = "DELETE FROM myTable WHERE id=1";

if (mysqli_query($conn, $sql)) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . mysqli_error($conn);
}

Use PDO extension:

// SQL 删除语句
$sql = "DELETE FROM myTable WHERE id=1";

// 使用 PDO::exec() 方法
if ($conn->exec($sql)) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . $conn->error;
}

5. Query (SELECT) data

The most commonly used query for data rows in the MySQL database The statement is a SELECT statement. In PHP, we can perform SELECT queries using MySQLi or PDO extensions.

Use MySQLi extension:

// SQL 查询语句
$sql = "SELECT id, firstname, lastname FROM myTable";

$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // 输出数据
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    }
} else {
    echo "0 results";
}

Use PDO extension:

// SQL 查询语句
$sql = "SELECT id, firstname, lastname FROM myTable";

$result = $conn->query($sql);

if ($result->rowCount() > 0) {
    // 输出数据
    while($row = $result->fetch(PDO::FETCH_ASSOC)) {
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    }
} else {
    echo "0 results";
}

6. End the connection

After processing the MySQL database, we need to close the connection with the database Connection.

Use MySQLi extension:

mysqli_close($conn);

Use PDO extension:

$conn = null;

In actual use, we can encapsulate the above operations into functions to make the code easier to manage and reuse . At the same time, data security is also very important. We should develop the habit of using prepared statements to avoid data leakage or loss due to SQL injection attacks.

The above is the detailed content of How to perform add, delete, modify and check operations in php+mysql. For more information, please follow other related articles on the PHP Chinese website!