[Example sharing] PHP PDO operation database (add, delete, modify, check)

PHP PDO is an extension of PHP. It provides PHP developers with a standardized way to operate databases, allowing developers to seamlessly switch between different databases. This article will demonstrate how to use PHP PDO to connect to a MySQL database and write an example of adding, deleting, modifying, and checking code.

Install the PDO extension

Before you begin, make sure you have the PDO extension installed in your PHP environment. You can check whether PDO is already installed in your current environment by running the following command in the terminal.

php -m | grep pdo

If no error message is prompted, it means that PDO has been installed successfully. If the prompt does not find the PDO extension, please install the corresponding version of PDO according to your operating system and PHP version.

Connecting to MySQL database

Before the demonstration, we first need to create a MySQL database locally and create a new data table named test. At the same time, in order to connect to the database, we need to prepare the information necessary for the connection, such as database name, user name, password, etc. The connection code is as follows:

$dbname = 'test';
$username = 'root';
$password = '';
$dsn = "mysql:host=localhost;dbname=$dbname;charset=utf8";

try {
  $pdo = new PDO($dsn, $username, $password);
  echo "连接成功";
} catch(PDOException $e) {
  echo $e->getMessage();
}

Description:

• dbname is the database name, which can be modified according to the actual situation;
• username and password are the username and password for accessing the database, which should be modified according to the actual situation;
• dsn is the necessary parameter for PDO to connect to the MySQL database, where mysql:host=localhost is the host name of MySQL, dbname=$dbname is the database name, charset=utf8 is the character set to ensure that our data is transmitted during transmission There will be no garbled characters.

If the connection is successful, the page will output "Connection successful".

Get data

Next, we will demonstrate how to use PDO to get data from the database. The code is as follows:

$sql = "SELECT * FROM test";
$stmt = $pdo->query($sql);

while ($row = $stmt->fetch()) {
  echo $row['id'] . ' ' . $row['name'] . ' ' . $row['age'] . "<br>";
}

Description:

• SELECT * FROM test is a SQL statement, where test is the name of the data table we created;
• $stmt is the result set object returned after PDO executes the SQL statement;
• $stmt->fetch() is used to obtain a piece of data in the result set, After each execution of this method, $stmt will point to the next piece of data;
• while loop is used to traverse all qualified data records until the result set Until there is no data.

Add data

Next, we will demonstrate how to use PDO to add data to the database. The code is as follows:

$name = 'Tom';
$age = 28;
$sql = "INSERT INTO test(name,age) VALUES(:name,:age)";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':name', $name);
$stmt->bindValue(':age', $age);
$result = $stmt->execute();

if ($result) {
  echo "数据插入成功";
} else {
  echo "数据插入失败";
}

Description:

• INSERT INTO is a SQL statement used to insert new data into the data table;
• :name and :age are placeholders for PDO parameter binding and will be replaced with real values ​​in the following code;
• $ pdo->prepare($sql) is used to preprocess SQL statements, where $sql is the SQL statement to be executed;
• $stmt-&gt ;bindValue(':name', $name) Bind the placeholder :name to the specific value $name;
• $result = $stmt->execute() Execute the SQL statement and return the execution result.

Update data

Next, we will demonstrate how to use PDO to update data in the database. The code is as follows:

$id = 1;
$name = 'John';
$age = 30;
$sql = "UPDATE test SET name=:name,age=:age WHERE id=:id";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':name', $name);
$stmt->bindValue(':age', $age);
$stmt->bindValue(':id', $id);
$result = $stmt->execute();

if ($result) {
  echo "数据更新成功";
} else {
  echo "数据更新失败";
}

Description:

• UPDATE is a SQL statement used to update data in the data table;
• SET name=:name,age=:age indicates the field to be updated and the corresponding value;
• WHERE id=:id indicates the conditions of the data to be updated;
• $stmt->bindValue(':id', $id) Bind the placeholder :id to a specific value $id.

Deleting Data

Finally, we will demonstrate how to use PDO to delete data in the database. The code is as follows:

$id = 1;
$sql = "DELETE FROM test WHERE id=:id";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':id', $id);
$result = $stmt->execute();

if ($result) {
  echo "数据删除成功";
} else {
  echo "数据删除失败";
}

Description:

• DELETE FROM is a SQL statement used to delete data from the data table;
• WHERE id=:id Indicates the condition of the data to be deleted;
• $stmt->bindValue(':id', $id) Change the placeholder:id is bound to the specific value $id.

Summary

The above is an example of using PHP PDO to operate the MySQL database to add, delete, modify, and query. I believe that through these examples, everyone has mastered the method of using PDO to connect to the database and perform related operations on the data. In actual projects, you can also combine other PHP frameworks and components to quickly develop some complex business logic.

The above is the detailed content of [Example sharing] PHP PDO operation database (add, delete, modify, check). For more information, please follow other related articles on the PHP Chinese website!