How to perform add, delete, modify and check operations in php+mysql-PHP Problem-php.cn

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

PHPz

Release： 2023-04-24 15:02:25

Original

1137 people have browsed it

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";

Copy after login

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

Copy after login

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

Copy after login

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;
}

Copy after login

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

Copy after login

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;
}

Copy after login

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

Copy after login

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;
}

Copy after login

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";
}

Copy after login

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";
}

Copy after login

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

Copy after login

Use PDO extension:

$conn = null;

Copy after login

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!