PDO study notes

The PDO extension defines a lightweight, consistent interface for PHP to access the database. It provides a data access abstraction layer so that no matter what database is used, queries and data can be obtained through consistent functions.
PDO is released with PHP5.1 and can also be used in the PECL extension of PHP5.0, but cannot run on previous PHP versions.

Before the advent of PDO, the functions to connect to the database in PHP were different depending on the database.
For example, MySQL uses the mysql_connect function, and PostgreSQL database uses the pg_connect function.

With the code written through PDO, if the database changes in the future, you only need to modify the connection parameters of the database appropriately, and there is no need to modify the logic code.

PDO and major database drivers are released with PHP as extensions. To activate them simply edit the php.ini file:
extension=php_pdo.dll
Then, select the DLL files for the specific database to load at runtime using dl(), or enable them after the php_pdo.dll line in the php.ini file, such as:

extension=php_pdo.dllextension=php_pdo_mysql.dllextension=php_pdo_pgsql.dllextension=php_pdo_sqlite.dll

MySQL Database Connection

$dsn = 'mysql:dbname=yii2test;host=localhost';
$user = 'sqluser';
$password = 'sqlpassword';
$db = new PDO($dsn, $user, $password);

try{
    $dbh = new PDO($dsn, $user, $password);
}catch (PDOException $e){
    print('Error:'.$e->getMessage());
    die();
}

PostgreSQL Database Connection

$dsn = 'pgsql:dbname=yii2test host=localhost port=5432';
$user = 'sqluser';
$password = 'sqlpassword';

try{
    $dbh = new PDO($dsn, $user, $password);
}catch (PDOException $e){
    print('Error:'.$e->getMessage());
    die();
}

SQLite database connection

$dsn = 'sqlite:d:/sqlite/yii2test.db';
$user = '';
$password = '';

try{
    $dbh = new PDO($dsn, $user, $password);
}catch (PDOException $e){
    print('Error:'.$e->getMessage());
    die();
}

PDO operation MySQL database demonstration code:

$dsn = 'mysql:dbname=yii2test;host=192.168.0.69;post=3306';
$user = 'shou';
$password = 'shouadmin';

try{
    $db = new PDO($dsn, $user, $password);

    // $sql = 'insert into user(username, password,password_hash, status) value (?, ?, ?, ?)';
    $stmt = $db->prepare($sql);
    $param = [];
    $param[] = 'admin'. date('YmdHis');
    $param[] = time();
    $param[] =  md5(time());
    $param[] = 10;
    if($stmt->execute($param)) {
        echo"insert ok !" .PHP_EOL;
    } else {
        echo"insert ng !" .PHP_EOL;
    }

    // echo PHP_EOL. "==> query" . PHP_EOL;
    $sql = "select * from user";
    $data = $db->query($sql);
    foreach($dataas$row) {
      echo$row["username"] . "  " . $row["password"]. PHP_EOL;
    }

    // echo PHP_EOL. "==> prepare 1" . PHP_EOL;
    $sql = 'select * from user where username like ?';
    $stmt = $db->prepare($sql);
    $stmt->execute(['admin%']);
    while($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
        print_r($result);
    }

    // echo PHP_EOL. "==> prepare 2" . PHP_EOL;
    $sql = 'select * from user where username like :username';
    $stmt = $db->prepare($sql);
    $stmt->execute([':username' => 'admin%']);
    while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
        print_r($result);
    }



}catch (PDOException $e){
    print('Error:'.$e->getMessage());
    die();
}

// 关闭数据库连接$db = null;