Looking for more information on how to do PHP the right way? Check out PHP: The Right Way

SitePoint PHP Blog:
Re-introducing PDO – the Right Way to Access Databases in PHP
Aug 25, 2015 @ 11:10:14

On the SitePoint PHP blog they have a post that "reintroduces PDO" or as they describe it, the "right way to access databases in PHP". The PDO functionality in PHP provides extra handling around database connections and queries as well as making it easier to connect to multiple types of databases with similar code.

PDO is the acronym of PHP Data Objects. As the name implies, this extension gives you the ability to interact with your database through objects. [...] PHP is rapidly growing, and it is moving toward becoming a better programming language. Usually, when this happens in a dynamic language, the language increases its strictness in order to allow programmers to write enterprise applications with peace of mind.

In case of PHP, better PHP means object-oriented PHP. This means the more you get to use objects, the better you can test your code, write reusable components, and, usually, increase your salary. Using PDO is the first step in making the database layer of your application object-oriented and reusable.

He starts by answering the question most ask about PDO versus mysql/mysqli by pointing out that PDO is more OOP friendly, it allows for parameter binding and the fact that the mysql extension is no longer supported. He shows how to check and ensure PDO is installed on your setup and, if not, how to add it in (for both linux and Windows systems). The tutorial then walks you through using PDO: making the connections to the server, running queries and returning the results. This includes a section on prepared statements and bound parameters and their benefits including SQL injection prevention.

tagged: pdo database access tutorial introduction prepared statements phpdataobjects

Link: http://www.sitepoint.com/re-introducing-pdo-the-right-way-to-access-databases-in-php/

Michelangelo van Dam:
Speeding up database calls with PDO and iterators
Jul 27, 2015 @ 08:45:28

In a post to his site Michelangelo van Dam shows you how to speed up database calls with PDO and iterators in a "no framework" environment.

When you review lots of code, you often wonder why things were written the way they were. Especially when making expensive calls to a database, I still see things that could and should be improved.

When working with a framework, mostly these database calls are optimized for the developer and abstract the complex logic to improve and optimize the retrieval and usage of data. But then developers need to build something without a framework and end up using the basics of PHP in a sub-optimal way.

He points out some of the common issues with a simple approach using just PDO and simple arrays including performance issues. Instead he recommends the use of iterators that wrap a PDO connection and allow for much simpler fetching and iteration of the found results. He includes code examples for a base iterator instance and a way to extend it to get the customized results. He also includes a few benchmarks showing the difference between a foreach loop and this iterator method.

tagged: database pdo iterator foreach benchmark compare

Link: http://www.dragonbe.com/2015/07/speeding-up-database-calls-with-pdo-and.html

Rob Allen:
Custom OAuth2 authentication in Apiiglity
Jul 21, 2015 @ 09:05:49

In an article posted to his site Rob Allen shows you how to hook in the OAuth2 authentication for an Apigility-based application with a pre-existing database table structure that may not match the defaults Apigility is looking for.

I have a client that's writing an Apigility API that needs to talk to a database that's already in place. This also includes the users table that is to be used with Apigility's OAuth2 authentication. Getting Apigility's OAuth2 integration to talk to a specific table name is quite easy. [...] However, if you want to use different column names, that's a bit trickier as they are hardcoded in the OAuth2StoragePdo class. To get Apigility's OAuth2 components to look at the correct columns, you create your own OAuth2 Adapter. I chose to extend ZFOAuth2AdapterPdoAdapter which extends OAuth2StoragePdo and go from there.

He includes the code for this extension of the PdoAdapter (a "OAuth2Adapter" class) in the post showing the definitions of the get user, set user and check password methods the OAuth2 flow needs to match users to OAuth sessions. He also includes the code for the "OAuth2AdapterFactory" class that's used to pull the custom PDO adapter class into Apigility and, along with some configuration changes, make it available for use. Then it's just a simple matter of changing the authentication type in the Apigility UI.

tagged: apigility oauth2 authentication custom factory pdo adapter oauth tutorial

Link: http://akrabat.com/custom-oauth2-authentication-in-apiiglity/

Evert Pot:
MySQL 5.6 BOOL behavior when using PDO and prepared statements
Dec 05, 2013 @ 10:37:42

Evert Pot was seeing some weird issues with his MySQL BOOL usage via PDO when he upgraded to one of the latest versions (5.6). Thankfully, he's shared his solution to the problem as well as the symptoms he was seeing when it was causing problems.

I recently updated my workstation to run MySQL 5.6.13. It didn't take very long for things to start breaking, and since I couldn't find any other information about this on the web, I figured this may be useful to someone else. The main error that started popping up was: "Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'my_bool' at row 1' in test.php" This exception happens under the condition that you use PDO, prepared statements and booleans.

He includes a small sample script to reproduce the issue and points out the issue - the default casting of prepared values to strings in prepared statements with PDO bound parameters. He shows two "relatively easy solutions" to the problem - either using integers instead of the true/false PHP boolean or specifying a type with the bindValue call.

tagged: mysql upgrade boolean field pdo prepared statement

Link: http://evertpot.com/mysql-bool-behavior-and-php/

How to Create an RSS Feed Using PHP and PDO
Dec 04, 2013 @ 11:52:53

On the tech.pro site there's a recent tutorial posted showing you a basic way to create an RSS feed using data coming from a database accessed via PDO.

Using an RSS feed on your website is a great way of letting your visitors, search engines or directories get a hand on your content. RSS feeds are common practice on most blog and CMS platforms including Wordpress, Joomla and evenly the newly released Ghost. If you're using a CMS or similar platform, the likelihood is that you don't need to implement an RSS feed yourself. [...] Below you've got the step-by-step process to create anything from the simple, standard-compliant RSS feed - up to the more advanced.

The tutorial shows you how to pull the data from a simple database table (SQL not provided, but pretty easy to figure out(, including example PDO connections for several database types. This data is then manually appended into an XML string to build out the RSS feed correctly. They also talk about implementing the Dublin Core metadata as a way for providing more information about the feed and its contents (including an image and category details).

tagged: rss feed introduction tutorial pdo xml dublincore

Link: http://tech.pro/tutorial/1722/how-to-create-an-rss-feed-using-php-and-pdo

Aura Blog:
A Peek At Aura v2 -- Aura.Sql and ExtendedPdo
Oct 22, 2013 @ 10:04:51

On the Aura blog Paul Jones has posted a look ahead for the framework, looking specifically at what's coming in version 2 for the Aura.Sql and ExtendedPdo functionality.

In the lessons learned post, I talked about how Aura was born of the idea that we could extract independent decoupled packages from Solar, and how in doing so, we discovered that some of those extracted packages themsleves could be further split into independent pieces.

He gives the example of Aura.Sql compared to the Solar_Sql (from the Solar framework) and how certain things that they thought needed to be coupled actually didn't. In version 2 of the Aura.Sql component, they're taking this same approach and abstracting out things that don't actually need to be in the base class. This breaks it up into three packages - Aura.Sql-v2, Aura.Sql_Query and Aura.Sql_Schema. He gets into more detail in the rest of the post as to what the new Aura.Sql (v2) will still handle.

tagged: aura framework aurasql extendedpdo pdo database version2

Link: http://auraphp.com/blog/2013/10/21/aura-sql-v2-extended-pdo/

Simon Champion:
PHP Upgrade Broke My Data Importer
Jun 27, 2013 @ 12:13:45

In his latest post Simon Champion recounts some of the issues he had when upgrading to PHP 5.4, what's usually a smooth transition from PHP 5.3. His specific problem came in a difference between the previous mysql_query call and the more-correct PDO usage.

Our office is in the thoes of a large-scale upgrade of the servers in our data center. The new version of Debian (version 7, or "Wheezy") has been officially released, having been in beta for the last few millenia, and our Ops team are slowly installing it across all our servers. This is great news, as it means we get to upgrade to PHP 5.4. Woohoo! New shininess. [...] We were ready. The upgrade should have been a breeze. But it wasn't.

He talks about his process of digging through the code trying to figure out why a call to import a CSV file into MySQL was failing. Their Data Importer component started failing with an error from MySQL about the "LOAD INFILE" not being allowed for use. He shares a "work around" that's not ideal (using exec) that manually imports the file into the database. He does point out that it could be something Debian-specific as they don't upgrade the version, just apply security patches retroactively.

We're making an effort to stick to modern PHP coding standards, so we're using PDO throughout, which makes is all the more galling. [...] Given that we do have a work-around now and everything is back up and running, I'm going to have to let this one drop; I don't have the time to try chasing it any more. But I hope this blog post will prove useful to anyone else having the same issue.
tagged: upgrade data importer mysql infile load pdo mysqlquery

Link: http://spudley.com/blog/php-upgrade-broke-my-data-importer

Duckout Blog:
Do Funny Stuff with the Google Charts API and PHP
Mar 26, 2013 @ 11:40:44

In this recent post to the Duckout blog, they show you how to hook your PHP-based (and database driven) application into the Google Charting API for chart/graph generation.

I think, whenever we see a chart in a magazine, in books or applications our brain say’s to us Yeepie!!! Don’t read these stupid texts or tables! Just look at the green or yellow line and hope that they are above the other lines or hope that your opinion is the biggest part of the pie. This saves us a lot of work and in my opinion we should concentrate on drawing beautiful colorized pie charts, instead of writing long boring articles. But the question is: How do I draw these beautiful colorized pie charts? The simple answer is: you don’t have to, because google will draw them for you and you just have to tell them what to draw via the google charts api.

The sample application is a "breakfast rating" tool that logs the results to a MySQL database via PDO calls. The results are then extracted and formated as JSON to be compatible with the Google Charts API data handling. Some sample Javascript is included showing how to call the Charts API with your data and get back a simple line graphing of the data from the database. You can see the application in action here for reference.

tagged: google charts api json tutorial rating database pdo


Michelangelo van Dam:
Look mama, no databases
Mar 18, 2013 @ 10:11:46

In his most recent post Michelangelo van Dam talks about unit testing and databases and how, to effectively test what should be tested (the code, not "the ability to fetch data") you need to correctly mock your database objects.

When I state "as is", I truly mean the way it's being used in production. So the database call collects real data on which business logic is applied. You can see this is not a healthy situation, especially when you also have services that apply business logic on data and store it back into the database. In "Chris Hartjes wrote this one sentence that says it all: "Unit test suites are meant to be testing code, not the ability of a database server to return results". And he's right, you shouldn't use database connections when your testing business rules and functional logic.

He goes on to show a few code examples that show a pre-mocked state of testing where the Product information is pulled directly from a PDO connection. The more correct version mocks out this object, though, and overrides the "execute" and "fetchAll" methods to return mocked results.

tagged: phpunit unittest database mock object pdo database


Avoid the Original MySQL Extension, Part 2
Feb 25, 2013 @ 13:40:09

PHPMaster.com has posted the second part of their "avoid the original MySQL extension" tutorial series (part one is here). In this new part, they share more about another alternative - the PDO extension.

The PDO extension supports twelve drivers, enabling it to connect to a variety of relational databases without the developer having to learn a number of different APIs. It does this by abstracting the database interaction calls behind a common interface, enabling the developer to utilize a consistent interface for different databases. This of course gives it a major advantage over both the MySQL and MySQLi extensions who are limited to only one database.

Included in the post are examples of some of the basics - connecting, executing queries, getting the results - as well as more compelx things like binding parameters and transaction support. There's also a bit about changing the default behavior of the PDO object through config options. He finishes off the article talking some about some of the issues that could come from using an abstraction layer like this and the need to still write good, compatible SQL depending on the database.

tagged: mysql extension avoid pdo tutorial abstraction