PHP PDO Database Operation Preprocessing and Points for Attention

PDO (PHP Database Object) extends to define a lightweight, 1-dimensional interface for PHP to access databases. It provides a data access abstraction layer, so that no matter what database is used, queries and data can be obtained through 1-dimensional functions. Safer and more efficient in database operation!

PDO defines a lightweight 1-dimensional interface for PHP to access various databases. No matter what database, it can query and obtain data by 1-dimensional method without considering the differences between different databases, which greatly simplifies database operation. PDO can support mysql, postgresql, oracle, mssql and other databases.

What is pretreatment?

Mature databases support the concept of preprocessing statements (Prepared Statements).

What are they? You can think of them as a compiled SQL statement template for execution, which can be customized with different variable parameters.

Preprocessing statements have two main advantages:

1. The query only needs to be parsed (or prepared) once, but it can be executed multiple times with the same or different parameters. When the query is ready (Prepared), the database analyzes, compiles, and optimizes its plan to execute the query.

For complex queries, if you repeat queries with different parameters but with the same structure many times, this process will take up a lot of time and slow down your application.

By using one preprocessing statement, you can avoid repeated analysis, compilation and optimization. Simply put, preprocessing statements use fewer resources and execute faster.

2. The parameters passed to the preprocessing statement do not need quotation marks, and the underlying driver will handle this for you.

If your application uses preprocessing statements exclusively, you can be sure that no SQL injection will occur.

Code demo:

<?php
header('content-type:text/html; charset=utf-8');
// Instantiation pdo Object 
$pdo = new PDO('mysql:host=127.0.0.1;port=3306;dbname=test;', 'root', '888888');
// Pass query Function execution sql Command 
$pdo->query('set names utf8');
// Insert data 
$sql  = "insert into persons (name,age) values (?, ?);";
$preObj = $pdo->prepare($sql);
$res  = $preObj->execute(array(' Xiao Ming ', 22));
var_dump($res);
// Delete data 
$sql = "delete from persons where id = ?";
$preObj = $pdo->prepare($sql);
$res  = $preObj->execute(array(3));
var_dump($res);
// Modify data 
$sql = "update persons set name = ? where id = ?;";
$preObj = $pdo->prepare($sql);
$res  = $preObj->execute(array('lucy', 5));
var_dump($res);
// Query data 
$sql = "select * from persons where age > ? order by id desc;";
$preObj = $pdo->prepare($sql);
$preObj->execute(array(20));
$arr = $preObj->fetchAll(PDO::FETCH_ASSOC);
/*
 * FETCH_BOTH    Is the default, can be saved, and returns associations and indexes. 
 * FETCH_ASSOC    Parameter determines that only associative arrays are returned. 
 * PDO::FETCH_NUM  Returns an index array 
 * PDO::FETCH_OBJ  Object consisting of an object 2 Dimensional array 
 */
print_r($arr);

If you use pdo preprocessing to insert 1 piece of data, an error is reported

<?php
$dsn = 'mysql:dbname=test;host=127.0.0.1';
$user = 'root';
$password = '';
try {
  $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
  echo 'Connection failed: ' . $e->getMessage();
}
/* Execute a prepared statement by passing an array of values */
$sth = $dbh->prepare('INSERT INTO room (create_time,create_uid,exp_time,is_private) VALUES (?,?,?,?)');
$rs = $sth->execute(["2018-05-14 14:10:04",0,1526278504,1]);
var_dump($sth->errorInfo());exit;

Report an error

array (size=3)
0 = > string 'HY000' (length=5)
1 = > int 1364
2 = > string 'Field 'id' doesn't have a default value' (length=39)

This is because the primary key of the table you designed does not have auto_increment

Solution

You can add a primary key field to a self-growing or preprocessing statement

Summarize