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:

<code><span>extension=<span>php_pdo.dll</span></span><span>extension=<span>php_pdo_mysql.dll</span></span><span>extension=<span>php_pdo_pgsql.dll</span></span><span>extension=<span>php_pdo_sqlite.dll</span></span></code>

MySQL Database Connection

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

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

PostgreSQL Database Connection

<code><span>$dsn</span> = <span>'pgsql:dbname=yii2test host=localhost port=5432'</span>;
<span>$user</span> = <span>'sqluser'</span>;
<span>$password</span> = <span>'sqlpassword'</span>;

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

SQLite database connection

<code><span>$dsn</span> = <span>'sqlite:d:/sqlite/yii2test.db'</span>;
<span>$user</span> = <span>''</span>;
<span>$password</span> = <span>''</span>;

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

PDO operation MySQL database demonstration code:

<code><span><span><?php</span><span>$dsn</span> = <span>'mysql:dbname=yii2test;host=192.168.0.69;post=3306'</span>;
<span>$user</span> = <span>'shou'</span>;
<span>$password</span> = <span>'shouadmin'</span>;

<span>try</span>{
    <span>$db</span> = <span>new</span> PDO(<span>$dsn</span>, <span>$user</span>, <span>$password</span>);

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

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

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

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



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

<span>// 关闭数据库连接</span><span>$db</span> = <span>null</span>;
</span></span></code>

Copyright statement: This article is an original article by the blogger and may not be reproduced without the permission of the blogger.

The above introduces the PDO study notes, including aspects of the content. I hope it will be helpful to friends who are interested in PHP tutorials.