Home > Article > Backend Development > PHP database driver, different ways to connect data study notes_PHP tutorial
PHP database driver, different ways to connect data study notes_PHP tutorial
- WBOYOriginal
- 2016-07-13 10:31:331149browse
Table of contents
1. Introduction to PHP database driver
2. Different ways to connect to the database with PHP
1. Introduction to PHP database driver
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
Copy code
1. JDBC-ODPC Bridge:
It maps JDBC API to ODPC API. Then let JDBC-ODPC call the local driver code of the database (that is, the database operation binary code library provided by the database manufacturer, such as oci.dll in Oracle)
2. Local API driver
Directly map the JDBC API to a database-specific client API, that is, load the local code library (C/C++, etc.) provided by the database manufacturer through the client
3. Network protocol driver (mainstream)
This type of driver provides a network API to the client. The JDBC driver on the client uses a socket to call the middleware program on the server, which converts its request into the required specific API calls.
4. Local protocol driver (mainstream)
This type of driver uses Socket to communicate directly between the client and the database. It is a JDBC that interacts directly with the database instance
This driver is smart. It knows the underlying protocol used by the database. It is also the most mainstream JDBC driver currently used. The focus of this chapter is on it
Copy code
For PHP, network protocol drivers and local protocol drivers are also commonly used, namely the MySQL client library and the MySQL Native driver library. These libraries implement the low-level protocols for interacting with the MySQL database server.
The database driver is at the bottom layer of communication between PHP and the database. Different database vendors will implement their own drivers based on a certain framework to provide basic functions and advanced functions of specific databases.
On top of the driver layer is the "connector" or adapter abstraction layer, which is used to connect PHP code and database. Programmers can use PDO (PHP Database Object) or directly use extended interfaces (mysql, mysqli) These exposed APIs communicate with the underlying database.
The underlying database driver provided by the database manufacturer
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
File is a file-based database engine and uses file I/O (input/output) functions to store and read databases from files on disk. It is generally much smaller than relational databases (such as Mysql) (such as typical file databases
The size of the SQLite command line version is less than 200KB). At the same time, the file database supports most of the SQL commands you are familiar with and is easy to carry.
Next, let’s start with the big picture above and learn one by one the different ways PHP connects to the database and their advantages and disadvantages in different business scenarios
2. Different ways of connecting to database with PHP
0x1: Use extended API interface to communicate with database
PHP code is composed of a core and some optional extensions that make up the core functionality. PHP's MySQL-related extensions, such as mysqli and mysql, are all implemented based on the PHP extension framework.
A typical function of extensions is to expose an API to PHP programmers, allowing extended functions to be used by programmers. Of course, there are also some extensions developed based on the PHP extension framework that do not expose API interfaces to PHP programmers. For example, the PDO MySQL driver extension does not expose the API interface to PHP programmers, but provides an interface to the PDO layer above it.
Please refer to another blog post about writing PHP extensions
http://www.cnblogs.com/LittleHann/p/3562259.html
In actual programming, the most frequently used method is to connect to the database by extending the API
extension=php_mysql.dll
This is an early extension designed and developed to allow PHP applications to interact with MySQL databases. The mysql extension provides a procedure-oriented interface and is designed for MySQL 4.1.3 or earlier. Therefore, although this extension can interact with MySQL 4.1.3 or newer database servers, it does not support some features provided by later MySQL servers
The source code of mysql extension is in the PHP extension directory ext/mysql
Copy code
// Connect and select database
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password') or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db('my_database') or die('Could not select database');
// 执行 SQL 查询
$query = 'SELECT * FROM my_table';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
// 以 HTML 打印查询结果
echo "n";
n";
| $col_value | n";
// 释放结果集
mysql_free_result($result);
// 关闭连接
mysql_close($link);
?>
复制代码
extension=php_mysqli.dll
mysqli扩展，我们有时称之为MySQL增强扩展，可以用于使用MySQL4.1.3或更新版本中新的高级特性。mysqli扩展在PHP 5及以后版本中包含。
mysqli扩展有一系列的优势，相对于mysql扩展的提升主要有：
复制代码
1. 面向对象接口
2. prepared语句支持(即参数编译预处理，可以有效防御SQL注入的发生)
3. 多语句执行支持
4. 事务支持
5. 增强的调试能力
6. 嵌入式服务支持
7. 在提供了面向对象接口的同时也提供了一个面向过程的接口。
复制代码
mysqli扩展是使用PHP扩展框架构建的，它的源代码在PHP源码目录下的ext/mysqli中
复制代码
$con = new mysqli("localhost", "root", "111", "php4fun_");
/* check connection */
if (mysqli_connect_errno())
{
printf("Connect failed: %sn", 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);
}
?>
复制代码
mysqli除了可以使用参数编译预处理来进行数据库通信，同时也兼容使用面向过程的编码方式
复制代码
/* Connect to a MySQL server 连接数据库服务器 */
$link = mysqli_connect(
'localhost', /* The host to connect to 连接MySQL地址 */
'root', /* The user to connect as 连接MySQL用户名 */
'111', /* The password to use 连接MySQL密码 */
'company'); /* The default database to query 连接数据库名称*/
if (!$link)
{
printf("Can't connect to MySQL Server. Errorcode: %s ", mysqli_connect_error());
exit;
}
/* Send a query to the server 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 Return the results of the query */
while( $row = mysqli_fetch_assoc($result) )
} }
/* Destroy the result set and free the memory used for it End the query to release the memory */
mysqli_free_result($result);
}
/* Close the connection Close the connection*/
mysqli_close($link);
?>
Copy code
PHP also supports many other database connection extensions. The usage methods are similar, as long as you follow the function calling specifications. For more details, please refer to
http://www.php.net/manual/zh/refs.database.php
0x2: Use PDO abstraction layer to communicate with database
PDO (PHP Data Object PHP Database Object) is a database abstraction layer specification in PHP applications. PDO provides a unified API interface that allows your PHP application to not care about the specific type of database server system to be connected. In other words, if you use PDO's API, you can seamlessly switch database servers whenever needed, such as from Firebird to MySQL, with only a small amount of PHP code modifications.
Other examples of database abstraction layers include JDBC in Java applications and DBI in Perl.
Note: Using PDO extension itself cannot implement any database functions; a PDO driver of a specific database must be used to access database services (it is just an interface specification)
But on the other hand, the stronger the compatibility an interface provides, the weaker its customization and specificity will be (this is easy to understand). The main disadvantage of the PDO interface API is that it will restrict you from using the MySQL service The client provides all advanced database features. For example, PDO does not allow the use of multi-statement execution supported by MySQL.
In PHP5, PDO currently supports a large number of databases, and will be the default database connection method in PHP6:
1. sqlite
2. mysql
3. pgsql
4. mssql
...
PDO is implemented based on the PHP extension framework, and its source code is under ext/pdo in the PHP source code directory
I emphasize again that PDO is just an interface specification. It does not implement any database functions by itself. Programmers must use a "PDO driver" of a specific database to access a specific database
extension=php_pdo_mysql.dll
Copy code
$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 (PDOExceptsddttrtion $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";
?>
复制代码
这里只以Mysql、PostGreSQL为例，事实上，PDO这种抽象层方式可以访问目前主流的大多数的数据库，并且PDO将成为PHP6的默认数据库连接方式，更多详情请参阅
http://www.php.net/manual/zh/book.pdo.php
0x3: 使用ODBC抽象层与数据库通信
ODBC是一种应用程序编程接口(Application Programming Interface，API)，使我们有能力连接到某个数据源(比如一个MS Access 数据库)
试图通过编程语言和数据库查询访问(SQL标准化)来标准化连接方法，比如功能和配置。
ODBC的作用是充当接口或连接器，它具有双重设计目标：
1. 首先，对于ODBC 系统，它充当的是编程语言系统
2. 其次，对于数据存储系统，它充当的是 ODBC 系统。
所 以，ODBC 需要一个"对ODBC而言是编程语言"的驱动程序(例如PHP-ODBC库)和一个"对数据存储系统而言是ODBC"的驱动程序(比如 MySQL-ODBC库)。除了ODBC系统本身之外，ODBC还可以处理数据源的配置，允许数据源和编程语言之间存在模糊性。
和之前学习的扩展API接口、PDO略有不同的是，使用ODBC连接数据库要稍微麻烦一点(至少我个人这么觉得)，这个"麻烦"体现在我们需要对目标数据库服务器的操作系统进行一些配置，即创建ODBC数据源，然后才可以进行ODBC连接
创建到达 MS Access 数据的 ODBC 连接的方法：
复制代码
1. 在控制面板中打开管理工具
2. 双击其中的数据源 (ODBC)图标
3. 选择系统 DSN 选项卡
4. 点击系统 DSN 选项卡中的"添加"按钮
5. 选择 Microsoft Access Driver。点击完成
6. 在下一个界面，点击“选择”来定位数据库
7. 为这个数据库取一个数据源名 (DSN)
8. 点击确定
复制代码
code:
复制代码
//连接一个ODBC数据源，无帐号、密码
$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 "
";
| Companyname | ";Contactname |
|---|---|
| $username | ";$password |
?>
Copy code
0x4: Use DBX to communicate with the database
PHP itself has built-in DBX functions. The DBX module is a database abstraction layer (the "X" in DBX represents the X types of databases it can support). DBX functions allow you to access all DBX supported databases.
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:
Copy code
/*****
* 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
Add a URL:
URL: Description:
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 should add some.
}
else
{
?>
| URL |
Description | |
| data[$i]['url']?>>data[$i]['url']?> | data[$i]['description']?> |
}
?>
复制代码
0x5: 使用DBA(Database (dbm-style) Abstraction Layer)与数据库通信
PHP的DBA抽象层是用来支持Berkeley DB这种文件型数据库的。
These functions build the foundation for accessing Berkeley DB style databases.
在柏克莱的BSD系列操作系统中，有个简单的数据库结构，它以数个文件组成超小型的数据库系统，架构成抽象层(abstraction layer)的DBA数据库。
目前PHP支持的DBA数据库包括
复制代码
1. DBM: 柏克莱发展的最早期DBA数据库
http://en.wikipedia.org/wiki/Dbm
2. NDBM: 较新且较有弹性的DBA
http://en.wikipedia.org/wiki/NDBM
3. GDBM: GNU 发展的DBA
ftp://ftp.gnu.org/pub/gnu/gdbm/
4. DB2: 由Sleepycat软件开发的DB2(非IBM的DB2)
http://www.openldap.org/lists/openldap-software/199905/msg00009.html
5. CDB: 这是qmail作者开发快速可靠的DBA
http://pobox.com/~djb/cdb.html
复制代码
安装好DBA后，使用如下代码进行连接
复制代码
$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. 后记
以上就是PHP连接数据库的不同方式的学习，通过本文的学习，我们了解到一点
目前PHP开发中主流使用的连接数据库的技术是
1. Mysql扩展API
2. Mysqli扩展API
3. PDO抽象层
下一步希望做的事
1. 研究一下PHP和mysql进行交互的协议驱动的底层原理
2. 尝试编程简单的通信协议驱动
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：Delete html tags to get plain text and handle nested tags_PHP tutorialNext article：Delete html tags to get plain text and handle nested tags_PHP tutorial