Bài giảng Phát triển ứng dụng nguồn mở: Bài 3.2 - Đoàn Thiện Ngân

Chia sẻ: 5A4F5AFSDG 5A4F5AFSDG | Ngày: | Loại File: PDF | Số trang:58

lượt xem

Bài giảng Phát triển ứng dụng nguồn mở: Bài 3.2 - Đoàn Thiện Ngân

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Bài 3.2 trình bày những kiến thức về PHP-PDO. Chương này gồm có những nội dung chính sau: Database Abstraction Layer, PDO (Connection, error handling, executing queries, prepare statement, transaction. Mời các bạn tham khảo.

Chủ đề:

Nội dung Text: Bài giảng Phát triển ứng dụng nguồn mở: Bài 3.2 - Đoàn Thiện Ngân

  1. Bài 3.2 PHP-PDO GV: ĐOÀN THIỆN NGÂN Đoàn Thiện Ngân Bài 3.2 – 1/58
  2. Contents • Database Abstraction Layer. • PDO – Connection – Error handling – Executing queries – Prepare statement – Transaction Đoàn Thiện Ngân Bài 3.2 – 2/58
  3. Database Abstraction Layer Decouple the application and data layers Đoàn Thiện Ngân Bài 3.2 – 3/58
  4. Widespread implementations • MDB2: written in PHP and available as a PEAR package. It presently supports FrontBase, InterBase, MySQL, Oracle, PostgreSQL, QuerySim, and SQLite. • JDBC: Java Database Connectivity standard allows Java programs to interact with any database for which a JDBC driver is available. Among others, this includes Microsoft SQL Server, MySQL, Oracle, and PostgreSQL. • ODBC: Open Database Connectivity interface is one of the most widespread abstraction implementations in use today, supported by a wide range of applications and languages, PHP included. ODBC drivers are offered by all mainstream databases, including those referenced in the above JDBC introduction. • Perl DBI: Perl Database Interface module is Perl’s standardized means for communicating with a database, and it was the inspiration behind PHP’s DB package. Đoàn Thiện Ngân Bài 3.2 – 4/58
  5. PHP Data Objects - PDO • PHP Data Objects (PDO) abstraction layer • Officially released with PHP 5.1 • PDO serves as an ideal replacement for the MDB2 PEAR package and similar solutions • PDO is actually much more than just a database abstraction layer: – Coding consistency – Flexibility – Object-oriented features – Performance Đoàn Thiện Ngân Bài 3.2 – 5/58
  6. Coding consistency • Because PHP’s various database extensions are written by a host of different contributors, the coding approaches are quite inconsistent despite the common set of features. • PDO removes this inconsistency by offering a single interface that is uniform no matter the database. Furthermore, the extension is broken into two distinct components: – PDO core contains most of the PHP-specific code, leaving the various drivers to focus solely on the data. – PDO developers took advantage of considerable knowledge and experience while previously building and maintaining the native database extensions, capitalizing upon what was successful and being careful to avoid what was not. Although a few inconsistencies remain, by and large the database features are nicely abstracted. Đoàn Thiện Ngân Bài 3.2 – 6/58
  7. Flexibility • Because PDO loads the desired database driver at run time, there’s no need to reconfigure and recompile PHP every time a different database is used. • For instance, if your database needs suddenly switch from Oracle to MySQL, just load the PDO_MYSQL driver Đoàn Thiện Ngân Bài 3.2 – 7/58
  8. Object-oriented features & Performance • Object-oriented features: PDO takes advantage of PHP 5’s object-oriented features, resulting in a more refined approach to database interaction than many preceding solutions. • Performance: PDO is written in C and compiled into PHP, which, all other things being equal, provides a considerable performance increase over solutions written in PHP. Đoàn Thiện Ngân Bài 3.2 – 8/58
  9. Installing PDO • PDO is enabled by default as of version PHP 5.1; however, the MySQL PDO driver is not. • Using PHP 5.1 or newer on the Windows platform, we need to add references to the PDO and driver extensions within the php.ini file extension=php_pdo.dll extension=php_pdo_mysql.dll Đoàn Thiện Ngân Bài 3.2 – 9/58
  10. PDO’s Database Options PDO supports quite a few databases • MySQL: Accessible via the PDO_MYSQL driver. • PostgreSQL: Accessible via the PDO_PGSQL driver. • Firebird/InterBase 6: PDO_FIREBIRD driver. • IBM DB2: PDO_IBM driver. • Informix: PDO_INFORMIX driver. • Microsoft SQL Server: PDO_DBLIB driver. • ODBC: PDO_ODBC driver. ODBC is not a database but it enables PDO to be used in conjunction with any ODBC-compatible database not found in this list. • Oracle: PDO_OCI driver (versions 8 through 11g). • 4D: Accessible via the PDO_4D driver. • SQLite 3.X: Accessible via the PDO_SQLITE driver. Đoàn Thiện Ngân Bài 3.2 – 10/58
  11. PDO Class • PDO::beginTransaction — Initiates a transaction • PDO::commit — Commits a transaction • PDO::__construct — Creates a PDO instance representing a connection to a database • PDO::errorCode — Fetch the SQLSTATE associated with the last operation on the database handle • PDO::errorInfo — Fetch extended error information associated with the last operation on the database handle • PDO::exec — Execute an SQL statement and return the number of affected rows • PDO::getAttribute — Retrieve a database connection attribute Đoàn Thiện Ngân Bài 3.2 – 11/58
  12. PDO Class • PDO::getAvailableDrivers — Return an array of available PDO drivers • PDO::inTransaction — Checks if inside a transaction • PDO::lastInsertId — Returns the ID of the last inserted row or sequence value • PDO::prepare — Prepares a statement for execution and returns a statement object • PDO::query — Executes an SQL statement, returning a result set as a PDOStatement object • PDO::quote — Quotes a string for use in a query. • PDO::rollBack — Rolls back a transaction • PDO::setAttribute — Set an attribute Đoàn Thiện Ngân Bài 3.2 – 12/58
  13. Connecting to a Database Server • Before interacting with a database using PDO, you’ll need to establish a server connection and select a database. • This is accomplished through PDO’s constructor. Its prototype follows: PDO PDO::__construct(string DSN [, string username [, string password [, array driver_opts]]]) • The DSN (Data Source Name) parameter consists of two items: the desired database driver name, and any necessary database connection variables such as the hostname, port, and database name. • The username and password parameters used to connect to the database • The driver_opts array specifies any additional options that might be required or desired for the connection. Đoàn Thiện Ngân Bài 3.2 – 13/58
  14. Parameters in the Constructor • The easiest way to connect to a database is by simply passing the connection parameters into the Constructor • For instance, the constructor can be invoked like this (MySQL-specific): $dbh = new PDO('mysql:host=localhost; dbname=testin', 'root', 'secret'); • DSN string in a separate file and reference it within the constructor: $dbh = new PDO('uri:file://usr/…/mysql.dsn'); • The file is owned by the same user responsible for executing the PHP script and possesses the necessary privileges. Đoàn Thiện Ngân Bài 3.2 – 14/58
  15. Referring to the php.ini File • It’s also possible to maintain the DSN information in the php.ini file by assigning it to a configuration parameter named pdo.dsn.aliasname, where aliasname is a chosen alias for the DSN that is subsequently supplied to the constructor. • For instance, it aliases the DSN to mysqlpdo: [PDO] pdo.dsn.mysqlpdo = 'mysql:dbname=db;host=myhost' • The alias can subsequently be called by the PDO constructor like so: $dbh = new PDO('mysqlpdo', 'root', 'secret'); • Unlike the previous method, this method doesn’t allow for the username and password to be included in the DSN. Đoàn Thiện Ngân Bài 3.2 – 15/58
  16. PDO’s Connection-Related Options • PDO::ATTR_AUTOCOMMIT: This option determines whether PDO will commit each query as it’s executed, or will wait for the commit() method to be executed before effecting the changes. • PDO::ATTR_CASE: You can force PDO to convert the retrieved column character casing to all uppercase, to convert it to all lowercase, or to use the columns exactly as they’re found in the database. Such control is accomplished by setting this option to one of three values: PDO::CASE_UPPER, PDO::CASE_LOWER, or PDO::CASE_NATURAL, respectively. • PDO::ATTR_EMULATE_PREPARES: Enabling this option makes it possible for prepared statements to take advantage of MySQL’s query cache. Đoàn Thiện Ngân Bài 3.2 – 16/58
  17. PDO’s Connection-Related Options • PDO::ATTR_ERRMODE: PDO supports three error-reporting modes, PDO::ERRMODE_EXCEPTION, PDO::ERRMODE_SILENT, and PDO::ERRMODE_WARNING. These modes determine what circumstances cause PDO to report an error. Set this option to one of these three values to change the default behavior, which is PDO::ERRMODE_EXCEPTION. • PDO::ATTR_ORACLE_NULLS: When set to TRUE, this attribute causes empty strings to be converted to NULL when retrieved. By default this is set to FALSE. Đoàn Thiện Ngân Bài 3.2 – 17/58
  18. PDO’s Connection-Related Options • PDO::ATTR_PERSISTENT: determines whether the connection is persistent. (By default) FALSE. • PDO::ATTR_PREFETCH: database feature that retrieves several rows even if the client is requesting one row at a time, the reasoning being that if the client requests one row, he’s likely going to want others. Doing so decreases the number of database requests and therefore increases efficiency. The prefetch size is set in kilobytes, for drivers that support this feature. • PDO::DEFAULT_FETCH_MODE: You can use this option to set the default fetching mode (associative arrays, indexed arrays, or objects), thereby saving some typing if you consistently prefer one particular method. Đoàn Thiện Ngân Bài 3.2 – 18/58
  19. PDO’s Connection-Related Options • PDO::ATTR_SERVER_INFO: server information. In MySQL, it retrieves data pertinent to server uptime, total queries, the average number of queries executed per second, and other important information. • PDO::ATTR_SERVER_VERSION: information pertinent to the database server’s version number. • PDO::ATTR_CLIENT_VERSION: information pertinent to the database client’s version number. • PDO::ATTR_CONNECTION_STATUS: information about the connection status. For instance, after a successful connection when using MySQL, the attribute contains “localhost via TCP/IP,” while on PostgreSQL it contains “Connection OK; waiting to send.” Đoàn Thiện Ngân Bài 3.2 – 19/58
  20. Handling Connection Errors • In the case of a connection error, the script immediately terminates unless the returned PDOException object is properly caught. Of course, you can easily do so using the exception-handling syntax. • Example (catch the exception in connection problem) Đoàn Thiện Ngân Bài 3.2 – 20/58



Đồng bộ tài khoản