Posted on September 29th, 2010
2549 views An Introduction to PDO
PDO: A Better Way to Work With DatabasesPDO (PHP Data Objects) is a powerful, yet lightweight, abstraction layer within PHP that allows you to work with a database without caring what system it actually happens to be. In short, through PDO you can write database-driven code that is entirely independent of the underlying server specifics. Through PDO you can work with MySQL, SQLite, PostgreSQL, MS SQL, Oracle, and a few others without having to write separate code for each software. In fact, the only line of software-specific code is the initial initialization of the PDO object. Sounds pretty damn useful already doesn't it? The obvious advantage behind this is portability. Write once, use anywhere- a very useful concept, don't you agree? This is achieved through one very powerful keyword in programming: abstraction. That is: the act of writing code at the most appropriate level possible. Think of programming layers as a pyramid. The further towards the base of the pyramid you go the more hard-written and specific the code is. Each layer above it gets a little more generic and forgiving. As such it gets more portable and maintainable. I cannot stress to programmers enough just how vital abstracting your code is. Anyways, enough on the abstraction lecture. How about we take a look at some PDO!
A Simple ExampleLets just look at how we'd go about connecting to a MySQL database, and query all of the results from a made up table. <?php try { $db = new PDO ("mysql: host=localhost; dbname=DATABASE_NAME", "USERNAME", "PASSWORD"); $sql = "SELECT * FROM my_table ORDER BY some_col DESC"; foreach ($db->query ($sql) as $row) { echo "<p>Current row: {$row['id']}</p>"; } } catch (PDOException $err) { die ($err); } ?> Lets first look at the fifth line "$db = new PDO". This line here holds the only thing specific to MySQL- the connection string, otherwise known as the Data Source Name (DSN). The PDO constructor takes three parameters, which we gave in the above example. They are: __construct (DSN, [USERNAME, PASSWORD]) The username and password parameters are optional for PDO, but more likely than not your database software will require them. What actually needs describing here is the DSN. A DSN, aka Data Source Name, is a database-specific connection string. It'll be different for all different database systems, but he format of a MySQL DSN is: mysql: host=HOST; dbname=DATABASE; port=PORT The port parameter is optional. You're probably familiar with what thisline does just by looking at it. The DSN specifics we're usingMySQL, andthen goes on to initiate the database location (commonly localhost) andthe name of the database we're to work with. I've encased this line inside a Try-Catch structure. This is particularly helpful with PDO because anytime there's an error within PDO it'll shoot back PDOException::getCode (), and the Try-Catch structure makes catching that very easy. The details behind the PDOException class aren't very important for this tutorial. Just know that if you typecast a variable with the PDOException class, like we did above in the catch () statement, you'll set the variable to the contents of the error. I strongly recommend looking further into the Try-Catch block. So once we've initiated the PDO object (unless we killed the process by erring out, of course) we go on to write a simple SQL statement. We'll ignore that, as it's nothing interesting. Instead lets take apart the foreach () statement. foreach ($db->query ($sql) as $row) { echo " The $db variable, naturally, contains our PDO object. And here we're calling its query () method, passing through the SQL statement we defined just above. The query () returns an associative array of our results. Hence why we're using a foreach () statement on it.
More PDOThe above is fine and dandy, but there are some down sides to the query () method. First off it doesn't escape statements, thus it's not fit to be used any time there is outside influence on the SQL. Also it's very inefficient for any SQL that you might have to call multiple times. We can kill two birds with one stone by using three new methods: prepare (), execute () and fetchAll (). $sql = $db->prepare ("SELECT * FROM my_table ORDER BY some_col DESC"); $sql->execute (); foreach ($sql->fetchAll () as $row) { echo " It's a little more complex, but I assure you the above is much more efficient and it's safe. The prepare () statement is a little deceiving. At first you may think that it's job is to escape the statement, thus preparing it for querying. This however isn't the case. It will protect your statements, yes. But what also happens, and more importantly, is it returns a PDOStatement object. The PDOStatement class, a sub-class of PDO, allows you to perform a whole slew of actions against the SQL passed through the prepare () statement. Notice how on the following lines we're no longer making method calls on our $db variable (the PDO object)? The execute () and fetchAll () methods belong to PDOStatement. You can think of PDOStatement as a SQL statement on steroids, in a suit of armor. Instead of having to interact with the king (PDO), you can interact with the foot soldier that's doing the real work (PDOStatement). Lame analogy, but work with me here. It should become clear now how this route makes executing the same SQL statement multiple times much more efficient than how we were doing it before. We called the prepare () statement once, and now we can execute that specific PDOStatement object over and over again as we please, without the needed resources for multiple query () calls on the same bit of SQL. With that said, the execute () statement does just like sounds. It executes (queries) the SQL code that was passed to the PDOStatement object during the prepare () call. In order to access the results of the query we use the fetchAll () method. foreach ($sql->fetchAll () as $row) { echo " This foreach () should look familiar to you- it's almost the exact same as the first example, with the query () method. Instead, because we're working through PDOStatement, we use the fetchAll () statement. When we called execute () it stored the results within the PDOStatement class itself. To be specific, fetchAll () returns an array containing both numeric and associative indices. In our example we're choosing to just use the associative index.
The Real Power Behind PDOStatementI'm giving this its own section because I like it just that much. Reading from a database is fine and dandy, but inserting is necessary at some point. And we all know how messy INSERT statements canget. You either have a load of string concatenations, use the sprintf () function, or ignore all security measures and into form-fed variables directly. Either way, it gets messy. PDOStatement to the rescue! $sql = $db->prepare ("INSERT INTO my_table (col_one, col_two) VALUES (:something, :another_thing)"); $sql->bindValue (":something", "nifty"); $sql->bindValue (":another_thing", "nice and clean!"); $sql->execute (); Take a look at the INSERT statement. Specifically the VALUES definition. Those two labels, started with the colon (:something and :another_thing) are called place holders. And they do just that- they represent data that's going to be replaced during the execute () statement. The following two lines show how you replace a place holder with actual data. In a real application this is where you'd feed in form-generated data. Another nice feature of the bindValue () method is the third, but optional, parameter which allows you to designate a specific data type for that field. If you're as much of a stickler about constraints within your code as I am, then your bindValue () statements will always look like this: $sql->bindValue (":username", $username, PDO_PARAM_STR); $sql->bindValue (":email_opt_in", $emailOpt, PDO_PARAM_BOOL); $sql->bindValue (":birthday_month", $birthMonth, PDO_PARAM_INT); Of course as I mentioned above, that third parameter is entirely optional. But I highly recommend setting it, as it'll prevent any unwanted data types creeping into your columns. There are other ways to work with place holders, But I'll leave that to you to get into, as the above method is generally efficient enough for any given situation.
Wrapping UpI'm going to wrap up this introduction up with some miscellaneous methods that I think you may find interesting. Remember that anything marked as PDO is accessed via the variable you assign to the PDO object, and anything marked PDOStatementis accessed via the variable you assigned to the PDOStatement object (through the prepare () statement, for example). PDO::quote (Escapes special characters within, and quotes the passed string- useful when using the PDO::Query () method) $sql = $db->quote ("SELECT * FROM my_table ORDER BY some_col DESC"); foreach ($db->query ($sql) as $row) PDOStatement->rowCount (Returns the number of rows affected by last statement- can be used to see if an INSERT was successful or how many rows were affected by a DELETE call, for example) $sql = $db->prepare ("INSERT INTO login (username, password) VALUES (:username, :password)"); $sql->bindValue (":username", "hooah"); $sql->bindValue (":password", "oscarmike"); $sql->execute (); echo " PDOStatement->setFetchMode (Allows you to set the mode for fetch, fetchAll, fetchObject- example: strictly associative or strictly numerical) $sql->setFetchMode (PDO::FETCH_NUM); foreach ($sql->fetchAll () as $row) { echo " Well that about covers all of the basics behind PDO. Be sure to speak up and point outwhere I wasn't descriptive enough! And as I promised, some useful links for you to look at. |