PHP database driver, different ways to connect to Data learning NOTE _ PHP Tutorial

PHP database drives and connects to data in different ways. Directory 1. PHP database driver Introduction 2. different ways for PHP to connect to the database 1. PHP database driver introduction a directory designed to interact with a specific type of database server

1. PHP database driver introduction

2. different ways for PHP to connect to the database

1. PHP database driver introduction

A driver is a piece of software code designed to interact with a specific type of database server. The driver may call some libraries. Similar to the concept of database driver in Java

1. JDBC-ODPC Bridge:

It maps JDBC APIs to ODPC APIs. Let the JDBC-ODPC call the local database driver code (that is, the database operating binary code library provided by the database vendor, such as oci. dll in Oracle)

2. local API driver

Directly map JDBC APIs to database-specific client APIs, that is, load the local code library (C/C ++) provided by the database vendor through the client)

3. network protocol-driven (mainstream)

This type of driver provides the client with a network API. The JDBC driver on the client uses Socket to call the Middleware program on the server, the latter is converting its request into a specific API call.

4. local protocol driver (mainstream)

This type of driver uses Socket to communicate directly between the client and the database. It is a type of JDBC that directly interacts with database instances.

This kind of driver is intelligent. it knows the underlying protocol used by the database and is also the most popular JDBC driver. the focus of this chapter is

For PHP, the network protocol driver and local protocol driver are also mainstream, that is, the MySQL client library and the MySQL Native driver library. These libraries implement the underlying protocols used for interaction with the MySQL database server.

The database driver is located at the bottom layer of the communication between PHP and the database. different database manufacturers will implement their own drivers based on a framework, provides basic functions and advanced functions for specific databases.

On top of the driver layer is a "connector" or an adapter abstraction layer for PHP code and Database connection. programmers can use PDO (PHP Database Object) or directly use the exposed APIs (mysql and mysqli) to communicate with the underlying database.

Underlying database driver provided by database vendors

Mysql: http://www.mysql.com/products/connector/

Oracle: http://www.oracle.com/technetwork/indexes/downloads/index.html#database

Sqlserver: http://msdn.microsoft.com/zh-cn/library/cc296170 (SQL .90). aspx

...

File database

A file type is a file-based database engine that uses file I/O (input/output) functions to store and read files from disks. It is generally much smaller than a relational database (such as Mysql) (such as a typical file-type database ).

SQLite command line version is smaller than kB). at the same time, the file-type database supports most of the SQL commands you are familiar with, and it is easy to carry.

Next, let's start with this big picture above and learn different ways of connecting PHP to databases one by one, as well as their advantages and disadvantages in different business scenarios.

2. different ways for PHP to connect to the database

0x1: use the extended API interface to communicate with the database

PHP code is a core and some optional extensions constitute the core functions. MySQL-related extensions of PHP, such as mysqli and mysql, are implemented based on the PHP extension framework.

A typical function of expansion is to expose an API to PHP programmers, allowing them to extend their functions to be used by programmers. Of course, some extensions developed based on the PHP Extension Framework do not expose API interfaces to PHP programmers. For example, the PDO MySQL driver extension does not expose API interfaces to PHP programmers, but provides an interface to the PDO layer at the upper layer.

For more information about PHP extensions, see another blog.

Http://www.cnblogs.com/LittleHann/p/3562259.html

In actual programming, the most frequently used APIs are used to connect to the database.

Extension = php_mysql.dll

This is an early extension of design and development that allows PHP applications to interact with MySQL databases. Mysql extension provides a process-oriented interface designed for mysql 4.1.3 or earlier versions. Therefore, although this extension can interact with MySQL4.1.3 or the updated database server, it does not support some features provided by the MySQL server later.

The source code for mysql extension is in the PHP extension directory ext/mysql.

// Connect and select a database

$ Link = mysql_connect ('MySQL _ host', 'MySQL _ user', 'MySQL _ password') or die ('could not connect: '. mysql_error ());

Echo 'ccted successfully ';

Mysql_select_db ('My _ database') or die ('could not select database ');

// Execute SQL query

$ Query = 'select * FROM my_table ';

$ Result = mysql_query ($ query) or die ('query failed: '. mysql_error ());

// Print the query result in HTML

Echo"

While ($ line = mysql_fetch_array ($ result, MYSQL_ASSOC ))

{

Echo "\ t

Foreach ($ line as $ col_value)

{

Echo "\ t

}

Echo "\ t

}

Echo"

\ N "; \ N "; \ N "; \ N ";

$ Col_value

\ N ";

// Release the result set

Mysql_free_result ($ result );

// Close the connection

Mysql_close ($ link );

?>

Extension = php_mysqli.dll

Mysqli extension, which is sometimes called MySQL enhanced extension, can be used to use MySQL4.1.3 or to update new advanced features in the version. MySQL I extensions are included in PHP 5 and later versions.

The mysqli extension has a series of advantages, which are mainly improved compared with the mysql extension:

1. object-oriented interface

2. prepared statements are supported (that is, parameter compilation preprocessing can effectively prevent the occurrence of SQL injection)

3. support for multi-statement execution

4. transaction support

5. Enhanced debugging capability

6. embedded service support

7. provides an object-oriented interface and a process-oriented interface.

Mysqli extension is built using the PHP Extension Framework. its source code is in ext/mysqli under the PHP source code directory.

$ Con = new mysqli ("localhost", "root", "111", "php4fun _");

/* Check connection */

If (mysqli_connect_errno ())

{

Printf ("Connect failed: % s \ n", mysqli_connect_error ());

Exit ();

}

$ SQL = "select name from users where name =? And pass =? ";

$ Cmd = $ con-> prepare ($ SQL );

$ Name = $ _ GET ['name'];

$ Pass = $ _ GET ['pass'];

// Add parameters to SQL query

$ Cmd-> bind_param ("ss", $ name, $ pass );

$ Cmd-> execute ();

$ Cmd-> bind_result ($ result );

$ Cmd-> fetch ();

If ($ result)

{

Var_dump ($ result );

}

?>

In addition to parameter compilation preprocessing, mysqli can also use process-oriented encoding for database communication.

/* Connect to a MySQL server to Connect to the database server */

$ Link = mysqli_connect (

'Localhost',/* The host to connect to The MySQL address */

'Root',/* The user to connect as connection MySQL user name */

'123',/* The password to use to connect to The MySQL password */

'Company');/* The default database to query connection database name */

If (! $ Link)

{

Printf ("Can't connect to MySQL Server. Errorcode: % s", mysqli_connect_error ());

Exit;

}

/* Send a query to the server to Send a query request to the server */

If ($ result = mysqli_query ($ link, 'select * from p8_ad_user '))

{

Print ("Very large cities are :");

/* Fetch the results of the query returns the query result */

While ($ row = mysqli_fetch_assoc ($ result ))

{

Printf ("% s (% s)", $ row ['name'], $ row ['population']);

}

/* Destroy the result set and free the memory used for it end query to release memory */

Mysqli_free_result ($ result );

}

/* Close the connection to Close the connection */

Mysqli_close ($ link );

?>

PHP also supports many other database connection extensions in a similar way. you only need to follow the function call specifications. For more information, see

Http://www.php.net/manual/zh/refs.database.php

0x2: Use the PDO abstraction layer to communicate with databases

PDO (PHP Database Object) is a Database abstraction layer specification in PHP applications. PDO provides a unified API so that your PHP application does not care about the type of the database server to be connected. That is to say, if you use the pdo api, you can seamlessly switch the database server as needed, for example, from Firebird to MySQL, you only need to modify a few PHP code.

Examples of other database abstraction layers include JDBC in Java applications and DBI in Perl.

Note: using PDO extension itself cannot implement any database functions; you must use the PDO driver of a specific database to access the database service (it is only an Interface Specification)

On the other hand, the more compatible an interface is, the weaker its customization and specificity (which is easy to understand ), the main drawback of the pdo api is that you cannot use the MySQL Server to provide all advanced database features. For example, PDO does not allow MySQL-supported multi-statement execution.

In PHP5, PDO currently supports a large number of databases and uses it as the default database connection method in PHP6:

1. sqlite

2. mysql

3. pgsql

4. mssql

...

PDO is implemented based on the PHP Extension Framework. its source code is in ext/pdo of the PHP source code directory.

Again, PDO is only an interface specification and does not implement any database functions. programmers must use a specific database "PDO driver" to access a specific database.

Extension = php_pdo_mysql.dll

$ Dbhost = "localhost ";

$ Dbname = "company ";

$ Dbusr = "root ";

$ Dbpwd = "111 ";

$ Dbhdl = NULL;

$ Dbstm = NULL;

$ Opt = array (PDO: MYSQL_ATTR_INIT_COMMAND => 'set NAMES utf8 ',);

$ Dsn = 'MySQL: host = '. $ dbhost.'; port = 3306; dbname = '. $ dbname;

Try

{

$ Dbhdl = new PDO ($ dsn, $ dbusr, $ dbpwd, $ opt );

// Display exception

$ Dbhdl-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION );

}

Catch (pdow.tsddttrtion $ e)

{

// Return PDOException

Print "Error! : ". $ E-> getMessage ()."
";

Die ();

}

$ Dbstm = $ dbhdl-> query ('select * from p8_ad_user LIMIT 0, 1 ');

$ Rows = $ dbstm-> fetchAll (PDO: FETCH_ASSOC); // $ rows = $ dbhdl-> Fetch ();

Print_r ($ rows );

?>

Extension = php_pdo_pgsql.dll

$ Host = "localhost ";

$ User = "root ";

$ Pass = "111 ";

$ Db = "company ";

$ Cursor = "cr_123456 ";

Try

{

$ Dbh = new PDO ("pgsql: host = $ host; port = 5432; dbname = $ db; user = $ user; password = $ pass ");

Echo "Connected

";

}

Catch (Exception $ e)

{

Echo "Unable to connect:". $ e-> getMessage ()."

";

}

$ Dbh-> beginTransaction ();

$ Query = "SELECT * from p8_ad_user LIMIT 0, 1 ";

$ Dbh-> query ($ query );

$ Query = "fetch all in \" $ cursor \"";

Echo "begin data

";

Foreach ($ dbh-> query ($ query) as $ row)

{

Echo "$ row [0] $ row [1] $ row [2]
";

}

Echo "end data ";

?>

Here, we only use Mysql and PostGreSQL as an example. In fact, the PDO abstraction layer can access most mainstream databases, and PDO will become the default database connection method of PHP6. For more details, see

Http://www.php.net/manual/zh/book.pdo.php

0x3: communicate with the database using the ODBC abstraction layer

ODBC is an Application Programming Interface (API) that enables us to connect to a data source (such as a MS Access database)

Try to standardize the connection methods through programming languages and database query access (SQL standardization), such as functions and configurations.

ODBC acts as an interface or connector and has a dual design goal:

1. for the ODBC system, it acts as a programming language system.

2. for the data storage system, it acts as an ODBC system.

ODBC requires a "programming language for ODBC" driver (such as a PHP-ODBC Library) and a "ODBC for the data storage system" driver (such as MySQL-ODBC Library ). In addition to the ODBC system itself, ODBC can also process data source configurations, allowing ambiguity between data sources and programming languages.

What is slightly different from the extended API interfaces and PDO that I learned earlier is that using ODBC to connect to a database is a little troublesome (at least I personally think so ), this "trouble" is reflected in the fact that we need to configure the operating system of the target database server, that is, to create an ODBC data source before connecting to ODBC.

Method to create an ODBC Connection to the MS Access data:

1. open the management tool in the control panel

2. double-click the data source (ODBC) icon.

3. select the system DSN tab

4. click "add" on the system DSN tab.

5. select Microsoft Access Driver. Click finish

6. click "select" on the next page to locate the database.

7. get a data source name (DSN) for this database)

8. click OK.

Code:

// Connect to an ODBC data source without an account or password

$ Conn = odbc_connect ('northwind ','','');

If (! $ Conn)

{

Exit ("Connection Failed:". $ conn );

}

$ SQL = "SELECT * FROM p8_ad_user ";

$ Rs = odbc_exec ($ conn, $ SQL );

If (! $ Rs)

{

Exit ("Error in SQL ");

}

Echo"

Echo"

Echo"

While (odbc_fetch_row ($ rs ))

{

$ Username = odbc_result ($ rs, "username ");

$ Password = odbc_result ($ rs, "password ");

Echo"

Echo"

}

Odbc_close ($ conn );

Echo"

"; "; "; "; ";

Companyname	Contactname
$ Username	$ Password

";

?>

0x4: Use DBX to communicate with the database

PHP has built-in DBX functions. the DBX module is a database abstraction layer ("X" in DBX indicates the X types of databases it supports ). The DBX function allows you to access all databases supported by DBX.

DBX supports the following databases:

1. Mysql

2. ODBC

3. PgSQL

4. Mssql (Microsoft SQL Server)

5. Fbsql

Download:

Http://pecl.php.net/package/dbx

Http://rpmfind.net/linux/rpm2html/search.php? Query = php-dbx

Code:

A PHP-DBX URL Organizer

/*****

* Table definition for this example:

* Create table URLS (

* Url VARCHAR (128) not null,

* Description TEXT,

* Primary key (url ));

*****/

// Define $ MODULE as DBX_MYSQL, DBX_MSSQL, DBX_PGSQL, or your supported database

$ MODULE = DBX_PGSQL;

$ Server = "localhost ";

$ User = "root ";

$ Password = "111 ";

$ Database = "company ";

/* FUNCTIONS */

Function get_urls ($ dbconn, $ SQL)

{

$ Result = @ dbx_query ($ dbconn, $ SQL );

If ($ result = 0)

{

Echo dbx_error ($ dbconn );

}

Else

{

Return $ result;

}

}

Function url ($ action, $ dbconn, $ url, $ description)

{

If ($ action = "add ")

{

$ SQL = "insert into URLS values ('$ url',' $ description ')";

}

Elseif ($ action = "delete ")

{

$ Url = urldecode ($ url );

$ SQL = "delete from URLS where URL = '$ url '";

}

$ Result = @ dbx_query ($ dbconn, $ SQL );

If ($ result = 0)

{

Echo"

Error adding url: ". dbx_error ($ dbconn );

}

Else

{

Print ("

$ Action: $ url succeeded!

");

}

}

/***** MAIN ***/

$ Dbconn = dbx_connect ($ MODULE, $ server, $ database, $ user, $ password) or die ("cannot connect to database ");

?>

Php dbx url Organizer

If (isset ($ addurl ))

{

Url ("add", $ dbconn, $ url, $ description );

}

If (isset ($ delete ))

{

Url ("delete", $ dbconn, $ delete ,"");

}

$ SQL = "select * from URLS ";

$ Result = get_urls ($ dbconn, $ SQL );

If (sizeof ($ result-> data) = 0)

{

?>

Sorry, there are no URLs in the database. You shoshould add some.

}

Else

{

?>

{

?>

?>

For ($ I = 0; $ I <sizeof ($ result-> data); $ I ++)

}

URL	Description
Data [$ I] ['URL']?> Data [$ I] ['URL']?>	Data [$ I] ['description']?>	Data [$ I] ['URL'])?> Delete

}

?>

0x5: Use DBA (Database (dbm-style) communication action Layer) to communicate with the Database

The DBA abstraction layer of PHP is used to support file databases such as Berkeley DB.

These functions build the foundation for accessing Berkeley DB style databases.

In Berkeley's BSD operating systems, there is a simple database structure, which uses several files to form an ultra-small database system and is structured as a DBA database with an abstract action layer.

Currently, the DBA databases supported by PHP include

1. DBM: The earliest DBA database developed by Berkeley

Http://en.wikipedia.org/wiki/Dbm

2. NDBM: a newer and more flexible DBA

Http://en.wikipedia.org/wiki/NDBM

3. GDBM: The DBA developed by GNU

Ftp://ftp.gnu.org/pub/gnu/gdbm/

4. DB2: DB2 developed by Sleepycat (non-IBM DB2)

Http://www.openldap.org/lists/openldap-software/199905/msg00009.html

5. CDB: this is a quick and reliable DBA developed by the qmail author.

Http://pobox.com /~ Djb/cdb.html

After installing DBA, use the following code to connect

$ Id = dba_open ("/tmp/test. db", "n", "db2 ");

If (! $ Id)

{

Echo "dba_open failedn ";

Exit;

}

Dba_replace ("key", "This is an example! ", $ Id );

If (dba_exists ("key", $ id ))

{

Echo dba_fetch ("key", $ id );

Dba_delete ("key", $ id );

}

Dba_close ($ id );

?>

3. postscript

The above are different ways to connect PHP to the database. through the study in this article, we have learned a little

Currently, the mainstream database connection technology used in PHP development is

1. Mysql extension API

2. Mysqli extension API

3. PDO abstraction layer

What we hope to do next

1. study the underlying principle of protocol-driven interaction between PHP and mysql

2. try to program a simple communication protocol driver

Protocol 1. PHP database driver Introduction 2. different ways for PHP to connect to the database 1. PHP database driver introduction the driver is designed to interact with a specific type of database server...