Главная » Языки программирования

Php pdo class library

Содержание
  1. The PDO class
  2. Class synopsis
  3. Table of Contents
  4. User Contributed Notes 8 notes
  5. PHP Data Objects

The PDO class

Represents a connection between PHP and a database server.

Class synopsis

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

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

Table of Contents

  • PDO::beginTransaction — Initiates a transaction
  • PDO::commit — Commits a transaction
  • PDO::__construct — Creates a PDO instance representing a connection to a database
  • PDO::errorCode — Fetch the SQLSTATE associated with the last operation on the database handle
  • PDO::errorInfo — Fetch extended error information associated with the last operation on the database handle
  • PDO::exec — Execute an SQL statement and return the number of affected rows
  • PDO::getAttribute — Retrieve a database connection attribute
  • PDO::getAvailableDrivers — Return an array of available PDO drivers
  • PDO::inTransaction — Checks if inside a transaction
  • PDO::lastInsertId — Returns the ID of the last inserted row or sequence value
  • PDO::prepare — Prepares a statement for execution and returns a statement object
  • PDO::query — Prepares and executes an SQL statement without placeholders
  • PDO::quote — Quotes a string for use in a query
  • PDO::rollBack — Rolls back a transaction
  • PDO::setAttribute — Set an attribute

User Contributed Notes 8 notes

«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:

Читайте также:  Develop android app with kotlin

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

Database connection:
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.

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.

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 );

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.

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’ );

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

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.

Here is an singleton PDO example:

[db_options]PDO::MYSQL_ATTR_INIT_COMMAND=set names utf8

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» ] ;

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

foreach ( $attributes as $k => $v ) self :: $link -> setAttribute ( constant ( «PDO:: < $k >» )
, constant ( «PDO:: < $v >» ) ) ;
>

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

$stmt = Database :: prepare ( «SELECT ‘something’ ;» ) ;
$stmt -> execute ( ) ;
var_dump ( $stmt -> fetchAll ( ) ) ;
$stmt -> closeCursor ( ) ;
?>

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

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

  • PDO
    • Introduction
    • Installing/Configuring
    • Predefined Constants
    • Connections and Connection management
    • Transactions and auto-​commit
    • Prepared statements and stored procedures
    • Errors and error handling
    • Large Objects (LOBs)
    • PDO
    • PDOStatement
    • PDOException
    • PDO Drivers

    Источник

    PHP Data Objects

    This is a little late. but I’m old and slow.
    Regarding Extending PDOStatement and PDO I found that sending the PDOExtended class by reference helps:
    In the constructor after parent::__construct() :
    $this->setAttribute(\PDO::ATTR_STATEMENT_CLASS,array(‘PDOStatementExtended’, [&$this]));>

    And in
    class PDOStatementExtended extends \PDOStatement

    protected function __construct
    (
    \PDO &$PDO,
    )

    I wanted to extend PDO class to store statistics of DB usage, and I faced some problems. I wanted to count number of created statements and number of their executings. So PDOStatement should have link to PDO that created it and stores the statistical info. The problem was that I didn’t knew how PDO creates PDOStatement (constructor parameters and so on), so I have created these two classes:

    /**
    * PHP Document Object plus
    *
    * PHP Document Object plus is library with functionality of PDO, entirely written
    * in PHP, so that developer can easily extend it’s classes with specific functionality,
    * such as providing database usage statistics implemented in v1.0b
    *
    * @author Peter Pokojny
    * @license http://opensource.org/licenses/gpl-license.php GNU Public License
    */
    class PDOp protected $PDO ;
    public $numExecutes ;
    public $numStatements ;
    public function __construct ( $dsn , $user = NULL , $pass = NULL , $driver_options = NULL ) $this -> PDO = new PDO ( $dsn , $user , $pass , $driver_options );
    $this -> numExecutes = 0 ;
    $this -> numStatements = 0 ;
    >
    public function __call ( $func , $args ) return call_user_func_array (array(& $this -> PDO , $func ), $args );
    >
    public function prepare () $this -> numStatements ++;

    $args = func_get_args ();
    $PDOS = call_user_func_array (array(& $this -> PDO , ‘prepare’ ), $args );

    return new PDOpStatement ( $this , $PDOS );
    >
    public function query () $this -> numExecutes ++;
    $this -> numStatements ++;

    $args = func_get_args ();
    $PDOS = call_user_func_array (array(& $this -> PDO , ‘query’ ), $args );

    return new PDOpStatement ( $this , $PDOS );
    >
    public function exec () $this -> numExecutes ++;

    $args = func_get_args ();
    return call_user_func_array (array(& $this -> PDO , ‘exec’ ), $args );
    >
    >
    class PDOpStatement implements IteratorAggregate protected $PDOS ;
    protected $PDOp ;
    public function __construct ( $PDOp , $PDOS ) $this -> PDOp = $PDOp ;
    $this -> PDOS = $PDOS ;
    >
    public function __call ( $func , $args ) return call_user_func_array (array(& $this -> PDOS , $func ), $args );
    >
    public function bindColumn ( $column , & $param , $type = NULL ) if ( $type === NULL )
    $this -> PDOS -> bindColumn ( $column , $param );
    else
    $this -> PDOS -> bindColumn ( $column , $param , $type );
    >
    public function bindParam ( $column , & $param , $type = NULL ) if ( $type === NULL )
    $this -> PDOS -> bindParam ( $column , $param );
    else
    $this -> PDOS -> bindParam ( $column , $param , $type );
    >
    public function execute () $this -> PDOp -> numExecutes ++;
    $args = func_get_args ();
    return call_user_func_array (array(& $this -> PDOS , ‘execute’ ), $args );
    >
    public function __get ( $property ) return $this -> PDOS -> $property ;
    >
    public function getIterator () return $this -> PDOS ;
    >
    >
    ?>

    Classes have properties with original PDO and PDOStatement objects, which are providing the functionality to PDOp and PDOpStatement.
    From outside, PDOp and PDOpStatement look like PDO and PDOStatement, but also are providing wanted info.

    When using prepared statements there is no official PDO feature to show you the final query string that is submitted to a database complete with the parameters you passed.

    Use this simple function for debugging. The values you are passing may not be what you expect.

    //Sample query string
    $query = «UPDATE users SET name = :user_name WHERE > ;

    //Sample parameters
    $params = [ ‘:user_name’ => ‘foobear’ , ‘:user_id’ => 1001 ];

    function build_pdo_query ( $string , $array ) //Get the key lengths for each of the array elements.
    $keys = array_map ( ‘strlen’ , array_keys ( $array ));

    //Sort the array by string length so the longest strings are replaced first.
    array_multisort ( $keys , SORT_DESC , $array );

    foreach( $array as $k => $v ) //Quote non-numeric values.
    $replacement = is_numeric ( $v ) ? $v : «‘ < $v >‘» ;

    //Replace the needle.
    $string = str_replace ( $k , $replacement , $string );
    >

    echo build_pdo_query ( $query , $params ); //UPDATE users SET name = ‘foobear’ WHERE/> ?>

    Источник

Оцените статью
© 2024 Программирование