Home > Article > Backend Development > PDO Operation Guide in PHP
In PHP, using PDO to operate the database can improve the security and portability of the code. PDO is a PHP extension that provides a unified interface for database access and supports multiple database types. This article will introduce you to the basic usage and precautions of PDO.
1. Connect to the database
Using PDO to connect to the database requires the following parameters:
$dsn: database type: host=host name;dbname=database name
$username: user Name
$password: Password
$options: Optional parameters, used to specify some options for connecting to the database, such as character set, connection timeout, etc.
The following is an example of connecting to a MySQL database:
$dsn = 'mysql:host=localhost;dbname=test;charset=utf8';
$username = 'root';
$password = '123456';
$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
try {
$pdo = new PDO($dsn, $username, $password, $options);
} catch (PDOException $e) {
die($e->getMessage());
}
2. Execute SQL statements
Using PDO to execute SQL statements requires calling the prepare method of PDO to first prepare the SQL statement and bind the variables in the SQL statement. fixed on the parameters. Next, call the execute method to execute the SQL statement.
The following is an example of querying the database:
$sql = 'SELECT id, name, age FROM users WHERE age > ?';
$stmt = $pdo-> prepare($sql);
$stmt->bindParam(1, $age);
$age = 18;
$stmt->execute();
$rows = $stmt ->fetchAll(PDO::FETCH_ASSOC);
In this example, the variables in the SQL statement are replaced by question marks. When binding variables to parameters, you need to use the bindParam or bindValue method.
The bindParam method passes the variable to the parameter by reference, which means that when the value of the variable changes, the value of the parameter will also change.
The bindValue method copies the value of the variable to the parameter, which means that even if the value of the variable changes, the value of the parameter will not change.
3. Use transaction control
In database operations, a transaction refers to a group of related database operations. This group of operations as a whole either all execute successfully or all are rolled back. PDO's beginTransaction, commit and rollBack methods can help us achieve transaction control.
The following is an example of inserting data:
try {
$pdo->beginTransaction(); $stmt = $pdo->prepare('INSERT INTO users (name, age) VALUES (:name, :age)'); $stmt->bindParam(':name', $name); $stmt->bindParam(':age', $age); $name = '张三'; $age = 20; $stmt->execute(); $name = '李四'; $age = 22; $stmt->execute(); $pdo->commit();
} catch (PDOException $e) {
$pdo->rollBack(); die($e->getMessage());
}
In this example, the beginTransaction method means starting a transaction, the commit method means committing the transaction, and the rollBack method means rolling back the transaction.
4. Notes
When using PDO to operate the database, you also need to pay attention to the following matters:
To sum up, using PDO to operate the database can improve the security and portability of the code. In order to avoid security issues when operating the database, you can combine the above methods, think more and try more, and make better use of PDO.
The above is the detailed content of PDO Operation Guide in PHP. For more information, please follow other related articles on the PHP Chinese website!