What is the use of pdo in php?
Many PHP programmers learned how to access databases by using either the MySQL or MySQLi extensions. As of PHP 5.1, there's a better way. PHP Data Objects (PDO) provide methods for prepared statements and working with objects that will make you far more productive! Show
CRUD Generators and FrameworksDatabase code is repetitive, but very important to get right. That's where PHP CRUD generators and frameworks come in—they save you time by automatically generating all this repetitive code so you can focus on other parts of the app. On CodeCanyon, you will find CRUD generators and frameworks that will help you deliver outstanding quality products on time. (CRUD is an acronym for create, read, update, and delete—the basic manipulations for a database.) Introduction to PDOPDO—PHP Data Objects—are a database access layer providing a uniform method of access to multiple databases. It doesn't account for database-specific syntax, but can allow for the process of switching databases and platforms to be fairly painless, simply by switching the connection string in many instances. This tutorial isn't meant to be a complete how-to on SQL. It's written primarily for people currently using the When it comes to database operations in PHP, PDO provides a lot of advantages over the raw syntax. Let's quickly list a few:
Database SupportThe extension can support any database that a PDO driver has been written for. At the time of this writing, the following database drivers are available:
All of these drivers are not necessarily available on your system; here's a quick way to find out which drivers you have: print_r(PDO::getAvailableDrivers()); ConnectingDifferent databases may have slightly different connection methods. Below, you can see the method to connect to some of the most popular databases. You'll notice that the first three are identical, other than the database type—and then SQLite has its own syntax. try { # MS SQL Server and Sybase with PDO_DBLIB $DBH = new PDO("mssql:host=$host;dbname=$dbname", $user, $pass); $DBH = new PDO("sybase:host=$host;dbname=$dbname", $user, $pass); # MySQL with PDO_MYSQL $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass); # SQLite Database $DBH = new PDO("sqlite:my/database/path/database.db"); } catch(PDOException $e) { echo $e->getMessage(); } Please take note of the try/catch
block. You should always wrap your PDO operations in a try/catch and use the exception mechanism—more on this shortly. Typically, you're only going to make a single connection—there are several listed to show you the syntax. You can close any connection by setting the handle to null. # close the connection $DBH = null; You can get more information on database-specific options and/or connection strings for other databases from PHP.net. Exceptions and PDOPDO can use exceptions to handle errors, which means anything you do with PDO should be wrapped in a try/catch block. You can force PDO into one of three error modes by setting the error mode attribute on your newly created database handle. Here's the syntax: $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT ); $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING ); $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); No matter what error mode you set, an error connecting will always produce an exception, and creating a connection should always be contained in a try/catch block. PDO::ERRMODE_SILENTThis is the default error mode. If you leave it in this mode, you'll have to check for errors in the way you're probably used to if you've used the PDO::ERRMODE_WARNINGThis mode will issue a standard PHP warning and allow the program to continue execution. It's useful for debugging. PDO::ERRMODE_EXCEPTIONThis is the mode you want in most situations. It fires an exception, allowing you to handle errors gracefully and hide data that might help someone exploit your system. Here's an example of taking advantage of exceptions: # connect to the database try { $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass); $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); # UH-OH! Typed DELECT instead of SELECT! $DBH->prepare('DELECT name FROM people'); } catch(PDOException $e) { echo "I'm sorry, Dave. I'm afraid I can't do that."; file_put_contents('PDOErrors.txt', $e->getMessage(), FILE_APPEND); } There's an intentional error in the select statement; this will cause an exception. The exception sends the details of the error to a log file and displays a friendly (or not so friendly) message to the user. Insert and UpdateInserting new data (or updating existing data) is one of the more common database operations. Using PHP PDO, this is normally a two-step process. Everything covered in this section applies equally to both the Here's an example of the most basic type of insert: # STH means "Statement Handle" $STH = $DBH->prepare("INSERT INTO folks ( first_name ) values ( 'Cathy' )"); $STH->execute(); You could also accomplish the same operation by using the Prepared StatementsUsing prepared statements will help protect you from SQL injection. A prepared statement is a pre-compiled SQL statement that can be executed multiple times by sending just the data to the server. It has the added advantage of automatically making the data used in the placeholders safe from SQL injection attacks. You use a prepared statement by including placeholders in your SQL. Here are three examples: one without placeholders, one with unnamed placeholders, and one with named placeholders. # no placeholders - ripe for SQL Injection! $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values ($name, $addr, $city)"); # unnamed placeholders $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (?, ?, ?)"); # named placeholders $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (:name, :addr, :city)"); You want to avoid the first method; it's here for comparison. The choice of using named or unnamed placeholders will affect how you set data for those statements. Unnamed Placeholders# assign variables to each place holder, indexed 1-3 $STH->bindParam(1, $name); $STH->bindParam(2, $addr); $STH->bindParam(3, $city); # insert one row $name = "Daniel" $addr = "1 Wicked Way"; $city = "Arlington Heights"; $STH->execute(); # insert another row with different values $name = "Steve" $addr = "5 Circle Drive"; $city = "Schaumburg"; $STH->execute(); There are two steps here. First, we assign variables to the various placeholders (lines 2–4). Then, we assign values to those placeholders and execute the statement. To send another set of data, just change the values of those variables and execute the statement again. Does this seem a bit unwieldy for statements with a lot of parameters? It is. However, if your data is stored in an array, there's an easy shortcut: # the data we want to insert $data = array('Cathy', '9 Dark and Twisty Road', 'Cardiff'); $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (?, ?, ?)"); $STH->execute($data); That's easy! The data in the array applies to the placeholders in order. Named PlaceholdersYou could probably guess the syntax, but here's an example: # the first argument is the named placeholder name - notice named # placeholders always start with a colon. $STH->bindParam(':name', $name); You can use a shortcut here as well, but it works with associative arrays. Here's an example: # the data we want to insert $data = array( 'name' => 'Cathy', 'addr' => '9 Dark and Twisty', 'city' => 'Cardiff' ); # the shortcut! $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) value (:name, :addr, :city)"); $STH->execute($data); The keys of your array do not need to start with a colon, but otherwise need to match the named placeholders. If you have an array of arrays, you can iterate over them and simply call the Another nice feature of named placeholders is the ability to insert objects directly into your database, assuming the properties match the named fields. Here's an example object, and how you'd perform your insert: # a simple object class person { public $name; public $addr; public $city; function __construct($n,$a,$c) { $this->name = $n; $this->addr = $a; $this->city = $c; } # etc ... } $cathy = new person('Cathy','9 Dark and Twisty','Cardiff'); # here's the fun part: $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) value (:name, :addr, :city)"); $STH->execute((array)$cathy); Casting the object to an array in the Selecting DataData is obtained via the
In reality, there are three which will cover most
situations: $STH->setFetchMode(PDO::FETCH_ASSOC); You can also set the fetch type directly within the FETCH_ASSOCThis fetch type creates an associative array, indexed by column name. This should be quite familiar to anyone who has used the mysql/mysqli extensions. Here's an example of selecting data with this method: # using the shortcut ->query() method here since there are no variable # values in the select statement. $STH = $DBH->query('SELECT name, addr, city from folks'); # setting the fetch mode $STH->setFetchMode(PDO::FETCH_ASSOC); while($row = $STH->fetch()) { echo $row['name'] . "\n"; echo $row['addr'] . "\n"; echo $row['city'] . "\n"; } The while loop will continue to go through the result set one row at a time until complete.
|