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:

• PDO will not throw exceptions by default, and you need to specify ATTR_ERRMODE when connecting to the database. An exception will be thrown only if it is ERRMODE_EXCEPTION.
• PDO's prepare method will automatically escape the parameters, so there is no need to manually escape them.
• PDO's fetchAll method returns an associative array by default. You can use PDO::FETCH_NUM to get a numeric index array, and PDO::FETCH_BOTH to get an array that contains both numeric indexes and associative indexes.
• For MySQL database, simulated prepared statements must be used when PDO executes SQL statements, which can prevent SQL injection attacks. When using simulated prepared statements, you need to specify ATTR_EMULATE_PREPARES as false when connecting to the database.

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!