Learning notes for different methods of PHP database driver and Data Connection

Directory 1. PHP database driver Introduction 2. Different ways to connect PHP to the database 1. PHP database driver Introduction The 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 APIs to ODPC APIs. Let the JDBC-ODPC call the database local driver code (that is, the database operating binary code library provided by the database vendor, such as Oracle oci. dll) 2. the local API driver maps JDBC APIs directly to database-specific client APIs, that is, loading the local code library (C/C ++) provided by the database vendor through the client. the network protocol driver (mainstream) provides a network API for the client. 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 drivers (mainstream) Use sockets to communicate directly between the client and the database. It is a kind of smart JDBC driver that interacts directly with database instances. It knows the underlying protocol used by the database and is also the most popular JDBC driver currently, the focus of this chapter is to copy the Code. For PHP, the mainstream is the network protocol driver and local protocol driver, 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. Database manufacturers provide the underlying database driver mysql: http://www.mysql.com/products/connector/oracle: http://www.oracle.com/technetwork/indexes/downloads/index.html?databasesqlserver: http://msdn.microsoft.com/zh-cn/library/cc296170 (SQL .90 ). aspx... A file database is a file-based database engine that uses file I/O (input/output) functions to store and read files from disks. It is also much smaller than relational databases (such as Mysql) (for example, the typical file-type database SQLite command line version is smaller than KB). At the same time, file-based databases support most of the SQL commands you are familiar with and are easy to carry. Next, let's start with this big chart above, learn different ways to connect PHP to databases one by one, and their advantages and disadvantages in different business scenarios. different Ways for PHP to connect to the database 0x1: using 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 the compilation of PHP extensions, see another blog article titled export extension = php_mysql.dll, which 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, however, it does not support some features provided by the MySQL server later. The source code of mysql extension is copied under the PHP extension directory ext/mysql. <? Php // connect and select database $ link = mysql_connect ('mysql _ host', 'mysql _ user', 'mysql _ password') or die ('could not connect :'. mysql_error (); echo 'ted CTED 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 "<table> \ n"; while ($ line = m Ysql_fetch_array ($ result, MYSQL_ASSOC) {echo "\ t <tr> \ n"; foreach ($ line as $ col_value) {echo "\ t <td> $ col_value </td> \ n";} echo "\ t </tr> \ n ";} echo "</table> \ n"; // release the result set mysql_free_result ($ result); // close the connection mysql_close ($ link);?> Copy the code extension = php_mysqli.dll mysqli extension, which is sometimes called the MySQL enhancement extension and can be used to use the advanced features in MySQL4.1.3 or the latest version. MySQL I extensions are included in PHP 5 and later versions. Mysqli extension has a series of advantages, compared with mysql extension, mainly including: Copy Code 1. object-oriented interface 2. prepared statements are supported (that is, parameter compilation preprocessing can effectively prevent the occurrence of SQL injection. multi-statement execution supports 4. transaction support 5. enhanced debugging capability 6. embedded service support 7. it provides an object-oriented interface and a process-oriented interface. The replication code mysqli extension is built using the PHP extension framework. Its source code is copied to ext/mysqli in the PHP source code directory. <? Php $ 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) ;}?> Copying code mysqli can use parameter compilation and preprocessing for database communication, and also be compatible with copying code using process-oriented encoding. <? Php/* Connect to a MySQL server to connect to The database server */$ link = mysqli_connect ('localhost',/* The host to Connect to connect to The MySQL address */'root ', /* The user to connect as connection MySQL user name */'123',/* The password to use to connect to 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 */if ($ result = mysqli_query ($ link, 'select * from p8_ad_user ') {print ("Very large cities are :"); /* Fetch the results of the query results returned */while ($ row = mysqli_fetch_assoc ($ result) {printf ("% s (% s )", $ row ['name'], $ row ['population']);}/* Destroy Result set and free the memory used for it end query release memory */mysqli_free_result ($ result);}/* Close the connection Close connection */mysqli_close ($ link);?> Copy code PHP also supports many other database connection extensions, using the same method, as long as you follow the function call specifications, for more details see http://www.php.net/manual/zh/refs.database.php 0x2: using the PDO abstraction layer to communicate with the Database 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), but in other words, the more compatible an interface provides, 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. sqlite2. mysql3. pgsql4. mssql... PDO is implemented based on the PHP extension framework. Its source code is again emphasized in ext/pdo of the PHP source code directory. PDO is only an interface specification and does not implement any database functions, the programmer must use the "PDO driver" of a specific database to access the extension = php_pdo_mysql.dll of a specific database to copy the Code <? Php $ 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) {/r Eturn PDOException print "Error! :". $ E-> getMessage (). "<br>"; die () ;}$ dbstm = $ dbhdl-> query ('select * from p8_ad_user LIMIT 0, 1 '); $ rows = $ dbstm-> fetchAll (PDO: FETCH_ASSOC); // $ rows = $ dbhdl-> Fetch (); print_r ($ rows);?> Copy code extension = php_pdo_pgsql.dll copy Code <? Php $ 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 <p>";} catch (Exception $ e) {echo "Unable to connect :". $ e-> getMessage (). "<p>" ;}$ dbh-> beginTransaction (); $ query = "SELECT * from p8_ad_user LIMIT 0, 1"; $ dbh-> query ($ query ); $ query = "FETCH ALL IN \ "$ cursor \" "; echo" begin data <p> "; foreach ($ dbh-> query ($ query) as $ row) {echo "$ row [0] $ row [1] $ row [2] <br>";} echo "end data";?> Copy the code. 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: using ODBC abstraction layer to communicate with databases ODBC is an Application Programming Interface (API ), enables us to connect to a data source (such as a MS Access database) and try to standardize the connection methods, such as functions and configurations, through programming languages and database query Access (SQL Standardization. ODBC acts as an interface or connector. It has a dual design goal: 1. first, for the ODBC system, it acts as a programming language system 2. secondly, the data storage system 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 you can establish an ODBC connection to create an ODBC connection to the MS Access data method: Copy Code 1. open Administrative Tools in Control Panel 2. double-click the data source (ODBC) icon 3. select system DSN tab 4. click "add" on the system DSN tab. 5. select Microsoft Access Driver. Click Finish 6. on the next page, click "select" to locate the database. 7. obtain a data source name (DSN) for this database 8. click OK to copy the code: copy the code