Introduction to PDO

Source: http://www.phpchina.cn/bbs/viewthread.php? Tid = 1574 & fpage = 1 & Highlight = % 2 bmzt

 

 

PHP 5.1 comes with a new database connection layer, namely, PHP Data Objects (PDO ). Unlike the database abstraction layers such as ADODB and pear dB, it provides more efficient access to the database and query results. It can also prevent SQL injection through pre-processing statements.

Currently, the following databases are supported:

• Dblib: freetds/Microsoft SQL Server/SYBASE
• Firebird (Http://firebird.sourceforge.net /):Firebird/Interbase 6
• MySQL (Http://www.mysql.com /):MySQL 3.x/ 4.x
• OCI (Http://www.oracle.com ):Oracle call interface
• ODBC: ODBC V3 (IBM DB2 and unixodbc)
• Pgsql (Http://www.postgresql.org /):PostgreSQL
• SQLite (Http://www.postgresql.org /):SQLite 3 and SQLite 2

1. Connect to the database

Usually we use different connection functions (such as mysql_connect and pg_connect) to connect to the database. PDO provides a unified interface: pDO object.

<? PHP
$ Db = new PDO (
"Pgsql: dbname = PDO; host = localhost ",
"Ikes8 ",
"Ikes8"
);
Echo "successfully created a PDO object ";
?>

As you can see, PDO has three parameters,
• Connection string
Pgsql is a PDO driver used. It can be MySQL, MSSQL, Sybase, dblib, Firebird, OCI, ODBC, pgsql, SQLite, and sqlite2;
Dbname is the database name. Assume that there is a test database named PDO;
Host refers to the location to be connected. If it is local, It is localhost.
• User Name
• Password

Before proceeding to this step, make sure that the PDO module has been loaded. If we try to handle an invalid connection string:

<? PHP
$ Db = new PDO (
"This_is_not_a_pdo_module: dbname = PDO; host = localhost ",
"Foo ",
"Bar"
);
Echo "successfully created a PDO object ";
?>

PHP will return the following error:

Fatal error: uncaught exception 'pdoexception' with message 'could not find driver'

Therefore, we can handle the error in an elegant way, that is, throwing a PDO exception to handle the error (but not in all cases ).

<? PHP
Try
{
$ Db = new PDO (
"This_is_not_a_pdo_module: dbname = PDO; host = localhost ",
"Ikes8 ",
"Ikes8"
);
}
Catch (pdoexception $ E)
{
Die ($ e-> getmessage ());
}
Echo "successfully created a PDO object ";
?>

We will get

Cocould not find driver
Or
Sqlstate [hy000] [7] fatal: Database "pdo2" does not exist

If the database does not exist (different prompts are returned for different errors ).

2. Set attributes

1) There are three error handling methods for PDO:

• PDO: errmode_silent does not display error messages, but only sets error codes.
• PDO: errmode_warning: a warning error is displayed.
• PDO: errmode_exception throws an exception

You can use the following statement to set the error handling method to throw an exception.

$ Db-> setattribute (PDO: attr_errmode, PDO: errmode_exception );

When set to PDO: errmode_silent, you can call errorcode () or errorinfo () to obtain the error information. Of course, you can also obtain the error information in other cases.

2) because different databases process different names of returned fields in different cases, PDO provides PDO: attr_case settings (including PDO: case_lower, PDO: case_natural, PDO :: case_upper) to determine the case sensitivity of the returned field name.

3) specify the value of the null value returned by the database in PHP by setting the PDO: attr_oracle_nulls type (including PDO: null_natural, PDO: null_empty_string, PDO: null_to_string.

3. Query

For clarity, an RSS feeds table is defined here.

Id integer
Name character varying (100)
URL varharacter varying (255)
Feed character varying (255)

Do not use preprocessing statements:

<? PHP
Foreach ($ db-> query ("select * from feeds") as $ row)
{
Print_r ($ row );
}
?>

The following result is displayed:

Array
(
[ID] => 1
[0] => 1
[Name] => planet-PHP
[1] => planet-PHP
[Url] =>Http://www.planet-php.net
[2] =>Http://www.planet-php.net
[FEED] =>Http://www.planet-php.net/rdf/
[3] =>Http://www.planet-php.net/rdf/
)
(Only show that the behavior has saved space)

Preprocessing statement:

<? PHP
$ Stmt = $ db-> prepare ("select * from feeds ");
$ Stmt-> execute ();
Print_r ($ stmt-> fetch ());
?>

$ Stmt is a pdostatement object. This object is obtained after preprocessing and takes effect only after execute. The fetch function extracts only one row of data. to read all the data, replace it with the fetchall function.

Bind data

<? PHP
$ Stmt = $ db-> prepare ("select * from feeds where url =: URL ");
$ Url = "http://www.planet-php.net ";
$ Stmt-> bindparam (": URL", $ URL );
$ Stmt-> execute ();
While ($ ROW = $ stmt-> fetch ())
{
Print_r ($ row );
}
?>

Here, bindparam binds the $ URL variable to the URL field, which automatically loads the change volume during execution.

Insert data

<? PHP
$ Stmt = $ db-> prepare (
"Insert into feeds
(Name, URL, feed)
Values
(: Name,: URL,: feed )"
);

$ Stmt-> execute (
Array (
": Name" => "planeti Apache ",
": URL" => "http://www.planetapache.org ",
": Feed" => "http://www.planetapache.org/rss10.xml"
)
);
?>

Data insertion can also be used to quote data just like Binding data. Here, we provide a method to automatically quote data by specifying input parameters in execute.

Transaction Processing

$ DBH-> begintransaction ();
Try {
$ DBH-> query ("Update ...");
$ DBH-> query ("Update ...");
$ DBH-> commit ();
} Catch (exception $ e ){
$ DBH-> rollback ();
}

If the database supports transaction processing, when begintransaction is called, the database is set to non-automatic commit, and the status of automatic commit is returned for commit or rollback.

4. Stored Procedure

$ Stmt = $ DBH-> prepare ("Call sp_set_string (?) ");
$ Stmt-> bindparam (1, $ Str );
$ STR = 'foo ';
$ Stmt-> execute ();

Similar to the previous example, but "?" is used here. Data Binding method. sp_set_string is the name of the stored procedure.

Stored Procedure with output parameters

$ Stmt = $ DBH-> prepare ("Call sp_get_string (?) ");
$ Stmt-> bindparam (1, $ ret, PDO: param_str, 4000 );
If ($ stmt-> execute ()){
Echo "got $ RET/N ";
}

Bind Column output

$ Stmt = $ DBH-> prepare ("select extension, name from credits ");
If ($ stmt-> execute ()){
$ Stmt-> bindcolumn ('extension', $ extension );
$ Stmt-> bindcolumn ('name', $ name );
While ($ stmt-> fetch (PDO: fetch_bound )){
Echo "Extension: $ extension/N ";
Echo "Author: $ name/N ";
}
}