Submit a Pull Request Report a Bug

PDO クラス

(PHP 5 >= 5.1.0, PHP 7, PHP 8, PECL pdo >= 0.1.0)

はじめに

PHP とデータベースサーバーの間の接続を表します。

クラス概要

class PDO {

/* 定数 */

public const int PARAM_NULL;

public const int PARAM_BOOL = 5;

public const int PARAM_INT = 1;

public const int PARAM_STR = 2;

public const int PARAM_LOB = 3;

public const int PARAM_STMT = 4;

public const int PARAM_INPUT_OUTPUT;

public const int PARAM_STR_NATL;

public const int PARAM_STR_CHAR;

public const int PARAM_EVT_ALLOC;

public const int PARAM_EVT_FREE;

public const int PARAM_EVT_EXEC_PRE;

public const int PARAM_EVT_EXEC_POST;

public const int PARAM_EVT_FETCH_PRE;

public const int PARAM_EVT_FETCH_POST;

public const int PARAM_EVT_NORMALIZE;

public const int FETCH_DEFAULT;

public const int FETCH_LAZY;

public const int FETCH_ASSOC;

public const int FETCH_NUM;

public const int FETCH_BOTH;

public const int FETCH_OBJ;

public const int FETCH_BOUND;

public const int FETCH_COLUMN;

public const int FETCH_CLASS;

public const int FETCH_INTO;

public const int FETCH_FUNC;

public const int FETCH_GROUP;

public const int FETCH_UNIQUE;

public const int FETCH_KEY_PAIR;

public const int FETCH_CLASSTYPE;

public const int FETCH_SERIALIZE;

public const int FETCH_PROPS_LATE;

public const int FETCH_NAMED;

public const int ATTR_AUTOCOMMIT;

public const int ATTR_PREFETCH;

public const int ATTR_TIMEOUT;

public const int ATTR_ERRMODE;

public const int ATTR_SERVER_VERSION;

public const int ATTR_CLIENT_VERSION;

public const int ATTR_SERVER_INFO;

public const int ATTR_CONNECTION_STATUS;

public const int ATTR_CASE;

public const int ATTR_CURSOR_NAME;

public const int ATTR_CURSOR;

public const int ATTR_ORACLE_NULLS;

public const int ATTR_PERSISTENT;

public const int ATTR_STATEMENT_CLASS;

public const int ATTR_FETCH_TABLE_NAMES;

public const int ATTR_FETCH_CATALOG_NAMES;

public const int ATTR_DRIVER_NAME;

public const int ATTR_STRINGIFY_FETCHES;

public const int ATTR_MAX_COLUMN_LEN;

public const int ATTR_EMULATE_PREPARES;

public const int ATTR_DEFAULT_FETCH_MODE;

public const int ATTR_DEFAULT_STR_PARAM;

public const int ERRMODE_SILENT;

public const int ERRMODE_WARNING;

public const int ERRMODE_EXCEPTION;

public const int CASE_NATURAL;

public const int CASE_LOWER;

public const int CASE_UPPER;

public const int NULL_NATURAL;

public const int NULL_EMPTY_STRING;

public const int NULL_TO_STRING;

public const string ERR_NONE;

public const int FETCH_ORI_NEXT;

public const int FETCH_ORI_PRIOR;

public const int FETCH_ORI_FIRST;

public const int FETCH_ORI_LAST;

public const int FETCH_ORI_ABS;

public const int FETCH_ORI_REL;

public const int CURSOR_FWDONLY;

public const int CURSOR_SCROLL;

/* メソッド */

public __construct(
    string $dsn,
    ?string $username = null,
    ?string $password = null,
    ?array $options = null
)

public beginTransaction(): bool

public commit(): bool

public errorCode(): ?string

public errorInfo(): array

public exec(string $statement): int|false

public getAttribute(int $attribute): mixed

public static getAvailableDrivers(): array

public inTransaction(): bool

public lastInsertId(?string $name = null): string|false

public prepare(string $query, array $options = []): PDOStatement|false

public query(string $query, ?int $fetchMode = null): PDOStatement|false

public query(string $query, ?int $fetchMode = PDO::FETCH_COLUMN, int $colno): PDOStatement|false

public query(
    string $query,
    ?int $fetchMode = PDO::FETCH_CLASS,
    string $classname,
    array $constructorArgs
): PDOStatement|false

public query(string $query, ?int $fetchMode = PDO::FETCH_INTO, object $object): PDOStatement|false

public quote(string $string, int $type = PDO::PARAM_STR): string|false

public rollBack(): bool

public setAttribute(int $attribute, mixed $value): bool

}

PDO::beginTransaction — トランザクションを開始する
PDO::commit — トランザクションをコミットする
PDO::__construct — データベースへの接続を表す PDO インスタンスを生成する
PDO::errorCode — データベースハンドラにおける直近の操作に関連する SQLSTATE を取得する
PDO::errorInfo — データベースハンドラにおける直近の操作に関連する拡張エラー情報を取得する
PDO::exec — SQL ステートメントを実行し、作用した行数を返す
PDO::getAttribute — データベース接続の属性を取得する
PDO::getAvailableDrivers — 利用可能な PDO ドライバの配列を返す
PDO::inTransaction — トランザクション内かどうかを調べる
PDO::lastInsertId — 最後に挿入された行の ID あるいはシーケンスの値を返す
PDO::prepare — 文を実行する準備を行い、文オブジェクトを返す
PDO::query — プレースホルダを指定せずに、SQL ステートメントを準備して実行する
PDO::quote — クエリ用の文字列をクオートする
PDO::rollBack — トランザクションをロールバックする
PDO::setAttribute — 属性を設定する

＋add a note

User Contributed Notes 8 notes

down

Megaloman ¶

14 years ago

"And storing username/password inside class is not a very good idea for production code."

Good idea is to store database connection settings in *.ini files but you have to restrict access to them. For example this way:

my_setting.ini:
[database]
driver = mysql
host = localhost
;port = 3306
schema = db_schema
username = user
password = secret

Database connection:
<?php
class MyPDO extends PDO
{
    public function __construct($file = 'my_setting.ini')
    {
        if (!$settings = parse_ini_file($file, TRUE)) throw new exception('Unable to open ' . $file . '.');
        
        $dns = $settings['database']['driver'] .
        ':host=' . $settings['database']['host'] .
        ((!empty($settings['database']['port'])) ? (';port=' . $settings['database']['port']) : '') .
        ';dbname=' . $settings['database']['schema'];
        
        parent::__construct($dns, $settings['database']['username'], $settings['database']['password']);
    }
}
?>

Database connection parameters are accessible via human readable ini file for those who screams even if they see one PHP/HTML/any_other command.

down

williambarry007 at gmail dot com ¶

12 years ago

PDO and Dependency Injection

Dependency injection is good for testing.  But for anyone wanting various data mapper objects to have a database connection, dependency injection can make other model code very messy because database objects have to be instantiated all over the place and given to the data mapper objects.

The code below is a good way to maintain dependency injection while keeping clean and minimal model code.

<?php

class DataMapper
{
    public static $db;
    
    public static function init($db)
    {
        self::$db = $db;
    }
}

class VendorMapper extends DataMapper
{
    public static function add($vendor)
    {
        $st = self::$db->prepare(
            "insert into vendors set
            first_name = :first_name,
            last_name = :last_name"
        );
        $st->execute(array(
            ':first_name' => $vendor->first_name,
            ':last_name' => $vendor->last_name
        ));
    }
}

// In your bootstrap
$db = new PDO(...);
DataMapper::init($db);

// In your model logic
$vendor = new Vendor('John', 'Doe');
VendorMapper::add($vendor);

?>

down

anrdaemon at freemail dot ru ¶

15 years ago

Keep in mind, you MUST NOT use 'root' user in your applications, unless your application designed to do a database maintenance.

And storing username/password inside class is not a very good idea for production code. You would need to edit the actual working code to change settings, which is bad.

down

Anonymous ¶

6 years ago

I personnaly create a new instance of PDO like this : 

$dbDatas = parse_ini_file( DB_FILE );
 $dbOptions = [
 \PDO::ATTR_DEFAULT_FECTH_MODE => \PDO::FETCH_OBJ,
 \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION
 ];

 $dsn = sprintf( 'mysql:dbname=%s;host=%s', $dbDatas['dbname'],
$dbDatas['host'] );
 
 $this->cn = new \PDO( $dsn, $dbDatas['user'], $dbDatas['password'],
$dbOptions );
 $this->cn->exec( 'SET CHARACTER SET UTF8' );

down

thz at plista dot com ¶

10 years ago

Starting with PHP 5.4 you are unable to use persistent connections when you have your own database class derived from the native PDO class. If your code uses this combination, you will encounter segmentation faults during the cleanup of the PHP process.
You can still use _either_ a derived PDO class _or_ persistent connections.

For more information, please see this bug report: https://bugs.php.net/bug.php?id=63176

down

sinri at everstray dot com ¶

5 years ago

For some Database Environment, such as Aliyun DRDS (Distributed Relational Database Service), cannot process preparing for SQL. 
For such cases, the option `\PDO::ATTR_EMULATE_PREPARES` should be set to true. If you always got reports about "Failed to prepare SQL" while this option were set to false, you might try to turn on this option to emulate prepares for SQL.

down

-12

kcleung at kcleung dot no-ip dot org ¶

13 years ago

Here is an singleton PDO example:

###### config.ini ######
db_driver=mysql
db_user=root
db_password=924892xp

[dsn]
host=localhost
port=3306
dbname=localhost

[db_options]
PDO::MYSQL_ATTR_INIT_COMMAND=set names utf8

[db_attributes]
ATTR_ERRMODE=ERRMODE_EXCEPTION
############

<?php class Database {
    private static $link = null ;

    private static function getLink ( ) {
        if ( self :: $link ) {
            return self :: $link ;
        }

        $ini = _BASE_DIR . "config.ini" ;
        $parse = parse_ini_file ( $ini , true ) ;

        $driver = $parse [ "db_driver" ] ;
        $dsn = "${driver}:" ;
        $user = $parse [ "db_user" ] ;
        $password = $parse [ "db_password" ] ;
        $options = $parse [ "db_options" ] ;
        $attributes = $parse [ "db_attributes" ] ;

        foreach ( $parse [ "dsn" ] as $k => $v ) {
            $dsn .= "${k}=${v};" ;
        }

        self :: $link = new PDO ( $dsn, $user, $password, $options ) ;

        foreach ( $attributes as $k => $v ) {
            self :: $link -> setAttribute ( constant ( "PDO::{$k}" )
                , constant ( "PDO::{$v}" ) ) ;
        }

        return self :: $link ;
    }

    public static function __callStatic ( $name, $args ) {
        $callback = array ( self :: getLink ( ), $name ) ;
        return call_user_func_array ( $callback , $args ) ;
    }
} ?>

<?php // examples
$stmt = Database :: prepare ( "SELECT 'something' ;" ) ;
$stmt -> execute ( ) ;
var_dump ( $stmt -> fetchAll ( ) ) ;
$stmt -> closeCursor ( ) ;
?>

down

-31

Ale L ¶

5 years ago

1) Do not use your ddbb info in the same file

AND 

2) DO NOT NEVER USE "All privileges user" for everything, always create an alternative user with restricted permissions (basic: SELECT, INSERT and so on)

＋add a note

PDO クラス

はじめに

クラス概要

目次

User Contributed Notes 8 notes