Database Abstraction Layer-PDO

Database Abstraction Layer:

Provides a solution for ease of operation when data migration and project operations are multi-database.

When migrating from one database system to another, there is almost no need to change too much program code, such as migrating MS SQL Server to MySQL.

Code planning must be prescriptive, using the same data as possible for object instantiation, and using the same database abstraction layer (such as PDO). This will only change the configuration file of the system to migrate the database.

At present, the use of PHP for different database system development, these systems are not the same, and many database abstraction layer in PHP different levels, but the use of the same method, its development will undoubtedly improve development efficiency.

A common database abstraction layer:

Pdo,adodb, Metabase,pear:db, mysqli (MySQL extension component)

PDO Benefits:

PDO Bottom is developed in C language, faster

PDO is easy to learn (30 + functions-20 or so)

PDO has been used as the default database link in the high version of PHP, and mysql_query is about to be eliminated.

Usage of PDO:

PDO class: Basic commands for manipulating databases $pdo _object

PDO Statement class: Class $PDO for operations on the resulting set of queries _statement

PDO Exception Class: PDO Exception class

PDO Configuration and initialization:

PHP.ini Open PDO: Re-Apache

Extension = Php_pdo.dll

Extension = Php_pdo_mysql.dll

$db = new PDO ("Library type: host=?; Dbname=? "," User name "," password ");

Link string (DSN)

"Pdo_connect.php"

<?PHP$dsn= "Mysql:host=localhost;dbname=thinkshop";$user= "Root";$pwd= "";Try{    $pdo=NewPDO ($dsn,$usr,$pwd);}Catch(pdoexception$e){    Echo $e-GetMessage ();}
if($pdoinstanceof PDO)Echo"PDO Connect OK";Else    Echo"PDO Connect Error";?>

Query operation:

$pdo _statement = $pdo _object, query ("SQL command"):

return value:

Query function: Have record return-return result set false

Insert/delete/modify: Returns TRUE or False

Get the data in the result set as an array:

$pdo _statement, Fetch (): Converts a record to a one-dimensional array

$pdo _statement-Fetchall () convert all records to a two-dimensional array

To set the array style:

$pdo _statement-Serfetchmode (mode)

Mode:

Pdo::fetch_num: Indexed Array

Pdo::fetch_both: Two types of arrays

PDO::FETCH_ASSOC: Associative array

Set link Properties (case):

$pdo _statement-SetAttribute (attribute, value);

Property:

Pdo::attr_case

Value:

Pdo::case_upper Caps

Pdo::case_lower lowercase

"Pdo_statement.php"

<?PHP$dsn= "Mysql:host=localhost;dbname=thinkshop";$user= "Root";$pwd= "";Try{    $pdo=NewPDO ($dsn,$user,$pwd);}Catch(pdoexception$e){    Echo $e-getMessage ();} $sql= "SELECT * FROM Students";$result=$pdo, Query ($sql);$data=$result-Fetchall ();Print_r($data);Echo";//data does not exist in result set object after extraction$pdoSetAttribute (pdo::attr_case,pdo::case_upper); //Key name uppercase $result=$pdo, Query ($sql);$result-Setfetchmode (PDO::FETCH_ASSOC);$data=$result-fetch ();Print_r($data);?>

SQL operations returned with no result set such as modify, delete, etc.

$pdo _object, EXEC (SQL statement);

Return: There is a record effect, the return is affected by the number of rows, no effect returns 0;

Pre-load Processing: Multiple executions at one time.

$pdo _statement = $pdo _object-Prepare (SQL statement): preloading SQL statement commands

$pdo _statement-> Execute (): Execute the preload command

$sql = "SELECT * from student where sid=:id& sname=:name";

?: a uniform symbol for memory variable labeling.

: characters, memory variables;

$sql = "SELECT * from student where sid=:id& sname=:name"

$pdo _statement->bindvalue (": id", value)

$sql = "SELECT * from student where sid=?& sname=?"

$pdo _statement->bindvalue (1, value)

$pdo _statement-Bindparam (": id| number", PHP variable);

Database Abstraction Layer-PDO