PHP Best Practices Database

Posted by tsinka on Wed, 12 Jun 2019 22:40:39 +0200

PDO Extension

PHP natively provides PDO extensions, meaning PHP data objects.

Database Links and DSN

DSN refers to the name of the data source, providing detailed information about database links. Generally, it contains the following information:

Host name or IP address
Port number
Database name
character set

The DSN constructed by the above information can be used as the first parameter of the PDO class constructor, and the second and third parameters are the user name and password of the database, respectively. If the database needs authentication, these two parameters need to be provided.

<?php
try {
    $pdo = new PDO(
        'mysql:host = 127.0.0.1;dbname=books;port=3306;charset=utf8',
        'USERNAME',
        'PASSWORD'
    );
} catch(PDOException $e) {
    echo "Database connection failed";
    exit;
}

Ensuring the Security of Password Vouchers

Save the database credentials in a configuration file located outside the document root directory, and then import the file in the required file.

Preprocessing statement

When using user input in SQL, it must be filtered. So you need to use PDO extensions for preprocessing statements and parameter binding, which is very simple. Preprocessing statements are instances of PDO objects, but we seldom instantiate this class directly. Instead, we obtain objects of preprocessing statements through the prepare dness method of PDO instances. The first parameter of this method is an SQL statement string and the return value is a PDOStatement instance:

<?php
$sql = 'SELECT id FROM users WHERE email = :email';
$statement = $pdo->prepare($sql);

In this SQL statement, the value of email can be securely bound to any value.

<?php
$sql = 'SELECT id FROM users WHERE email = :email';
$statement = $pdo->prepare($sql);
$email =  filter_input(INPUT_GET, 'email');
$statement->bindValue(':email', $email);

Preprocessing statements automatically filter the value of $email. The third parameter of PDOStatement can specify the type of the binding value. If it is not filled in, the default is the string type. The optional constant types are as follows

PDO::PARAM_BOOL
PDO::PARAM_NULL
PDO::PARAM_INT
PDO:: PARAM_STR (default)

Query results

If the select method is executed, we need to use fetch(), fetchAll(), fetchColumn() and fetchObject() methods to get the query results.

//Processing the result of the preprocessing statement as an associative array
$sql = 'SELECT id, email FROM users WHERE email = :email';
$statement = $pdo->prepare($sql);
$email = filter_input(INPUT_GET, 'email');
$statement->bindValue(':email', $email);
$stament->execute();
//Iterative results
while(($result = $statement->fetch(PDO::FETCH_ASSOC)) !== false) {
    echo $result['email'];
}

Optional parameters of fetch and fetchAll methods

PDO::FETCH_ASSOC: Returns an associative array
PDO::FETCH_NUM: Returns an array of keys, whose keys are the index of the database column in the query results.
PDO::FETCH_BOTH: Returns an array with keys as column names and keys as numbers.
PDO::FETCH_OBJ: Let fetch() and fetchAll() return an object whose attribute is the column name of the database.
fetchAll() is not recommended unless you are quite sure that the available memory will hold the entire query result. If you only care about one column in the query result, you can use the fetchColumn() method, which acts like the fetch() method, returning a column in the next row of the query result, which has only one parameter to make the required index.

// Build and execute SQL queries
$sql = 'SELECT id, name FROM users WHERE email = :email';
$statement = $pdo->prepare($sql);
$email = filter_input(INPUT_GET, 'email');
$statement->bindValue(':email',  $email);
$statement->execute();

while(($emal = $statament->fetchCoulmn(1)) !== false) {
    echo $email;
}

Because email appears in the location of the second field in the SQL statement, index 1 is used here to extract it. We can also use fetchObj() method to get rows in the query results, which regards rows as objects, and the attributes of objects are columns in the query results.

// Build and execute SQL queries
$sql = 'SELECT id, name FROM users WHERE email = :email';
$statement = $pdo->prepare($sql);
$email = filter_input(INPUT_GET, 'email');
$statement->bindValue(':email',  $email);
$statement->execute();

while(($result = $statament->fetchObj()) !== false) {
    echo $result->email;
}

affair

Transaction means that a series of database statements are executed as a single logical execution unit, that is to say, a series of SQL queries in a transaction are either successful or not executed. The atomic performance of transactions ensures data consistency, security and persistence. A good side effect of transactions is performance improvement, because transactions queue multiple queries and execute them all at once.

Topics: PHP PDO SQL Database

Programmer Think