Aug
10
PHP â PDO Extension ”; Previous Next PDO is an acronym for PHP Data Objects. PHP can interact with most of the relational as well as NOSQL databases. The default PHP installation comes with vendor-specific database extensions already installed and enabled. In addition to such database drivers specific to a certain type of database, such as the mysqli extension for MySQL, PHP also supports abstraction layers such as PDO and ODBC. The PDO extension defines a lightweight, consistent interface for accessing databases in PHP. The functionality of each vendor-specific extension varies from the other. As a result, if you intend to change the backend database of a certain PHP application, say from PostGreSql to MySQL, you need to make a lot of changes to the code. The PDO API on the other hand doesnât require any changes apart from specifying the URL and the credentials of the new database to be used. Your current PHP installation must have the corresponding PDO driver available to be able to work with. Currently the following databases are supported with the corresponding PDO interfaces − Driver Name Supported Databases PDO_CUBRID Cubrid PDO_DBLIB FreeTDS / Microsoft SQL Server / Sybase PDO_FIREBIRD Firebird PDO_IBM IBM DB2 PDO_INFORMIX IBM Informix Dynamic Server PDO_MYSQL MySQL 3.x/4.x/5.x/8.x PDO_OCI Oracle Call Interface PDO_ODBC ODBC v3 (IBM DB2, unixODBC and win32 ODBC) PDO_PGSQL PostgreSQL PDO_SQLITE SQLite 3 and SQLite 2 PDO_SQLSRV Microsoft SQL Server / SQL Azure By default, the PDO_SQLITE driver is enabled in the settings of php.ini, so if you wish to interact with a MySQL database with PDO, make sure that the following line is uncommented by removing the leading semicolon. extension=pdo_mysql You can obtain the list of currently available PDO drivers by calling PDO::getAvailableDrivers() static function in PDO class. PDO Connection An instance of PDO base class represents a database connection. The constructor accepts parameters for specifying the database source (known as the DSN) and optionally for the username and password (if any). The following snippet is a typical way of establishing connection with a MySQL database − <?php $dbh = new PDO(”mysql:host=localhost;dbname=test”, $user, $pass); ?> If there is any connection error, a PDOException object will be thrown. Example Take a look at the following example − <?php $dsn=”localhost”; $dbName=”myDB”; $username=”root”; $password=””; try{ $dbConn= new PDO(“mysql:host=$dsn;dbname=$dbName”,$username,$password); Echo “Successfully connected with $dbName database”; } catch(Exception $e){ echo “Connection failed” . $e->getMessage(); } ?> It will produce the following output − Successfully connected with myDB database In case of error − Connection failedSQLSTATE[HY000] [1049] Unknown database ”mydb” PDO Class Methods The PDO class defines the following static methods − PDO::beginTransaction After obtaining the connection object, you should call this method to that initiates a transaction. public PDO::beginTransaction(): bool This method turns off autocommit mode. Hence, you need to call commit() method to make persistent changes to the database Calling rollBack() will roll back all changes to the database and return the connection to autocommit mode.This method returns true on success or false on failure. PDO::commit The commit() method commits a transaction. public PDO::commit(): bool Since the BeginTransaction disables the autocommit mode, you should call this method after a transaction. It commits a transaction, returning the database connection to autocommit mode until the next call to PDO::beginTransaction() starts a new transaction. This method returns true on success or false on failure. PDO::exec The exec() method executes an SQL statement and return the number of affected rows public PDO::exec(string $statement): int|false The exec() method executes an SQL statement in a single function call, returning the number of rows affected by the statement. Note that it does not return results from a SELECT statement. If you have a SELECT statement that is to be executed only once during your program, consider issuing PDO::query(). On the other hand For a statement that you need to issue multiple times, prepare a PDOStatement object with PDO::prepare() and issue the statement with PDOStatement::execute(). The exec() method need a string parameter that represents a SQL statement to prepare and execute, and returns the number of rows that were modified or deleted by the SQL statement you issued. If no rows were affected, PDO::exec() returns 0. PDO::query The query() method prepares and executes an SQL statement without placeholders public PDO::query(string $query, ?int $fetchMode = null): PDOStatement|false This method prepares and executes an SQL statement in a single function call, returning the statement as a PDOStatement object. PDO::rollBack The rollback() method rolls back a transaction as initiated by PDO::beginTransaction(). public PDO::rollBack(): bool If the database was set to autocommit mode, this function will restore autocommit mode after it has rolled back the transaction. Note that some databases, including MySQL, automatically issue an implicit COMMIT when a DDL statement such as DROP TABLE or CREATE TABLE is issued within a transaction, and hence it will prevent you from rolling back any other changes within the transaction boundary. This method returns true on success or false on failure. Example The following code creates a student table in the myDB database on a MySQL server. <?php $dsn=”localhost”; $dbName=”myDB”; $username=”root”; $password=””; try{ $conn= new PDO(“mysql:host=$dsn;dbname=$dbName”,$username,$password); Echo “Successfully connected with $dbName database”; $qry = <<<STRING CREATE TABLE IF NOT EXISTS STUDENT ( student_id INT AUTO_INCREMENT, name VARCHAR(255) NOT NULL, marks