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

Johannes Schlüter:
Types in PHP and MySQL
Sep 05, 2016 @ 13:38:21

Johannes Schlüter has a post to his site detailing the handling of types in PHP and MySQL and how they might act differently than expected in some situations.

Since PHP 7.0 has been released there's more attention on scalar types. Keeping types for data from within your application is relatively simple. But when talking to external systems, like a database things aren't always as one eventually might initially expect.

He talks about MySQL types and how they relate to the "network protocol" being used, converting everything to strings. He includes a few examples of hinting on the results, one where an integer is expected/string provided and another where a string was type hinted but an integer was returned. He points out that sometimes this is a limitation of what PHP can handle, not always what MySQL returns. He also includes other examples of returning decimals - sometimes as a number value and others as a string.

This leaves the question whether you should disable the emulation in order to get the correct types. Doing this has some impact on performance characteristics: With native prepared statements there will be a client-server round-trip during the prepare and another round-trip for the execute.
tagged: types typehinting mysql database string integer decimal preparedstatement pdo

Link: http://schlueters.de/blog/archives/182-Types-in-PHP-and-MySQL.html

Usability problems of mysqli compared to PDO
Jun 27, 2016 @ 09:49:44

On the PHPDelusions.com site there's a post that compares the functionality of mysqli to PDO and looks at the differences in their overall usability.

By no means I am going to say that mysqli is worse than PDO. Mysqli is an excellent extension, with many specific features. But it's just not intended to be used directly. To make it usable, one have to always wrap it into a helper library, to reduce the enormous amount of code that otherwise have to be written by hand.

[...] But for the average PHP/MySQL user, standard APIs are the only known methods for database interaction. Thus they tend to use both extensions right in the application code, without any intermediate wrapper around. For such a use PDO is an indisputable winner, and I'll show you why.

The post then breaks it down into sections comparing the functionality between the two database access methods:

  • Named placeholders
  • General inconvenience in binding
  • Getting single column value
  • Getting multiple rows
  • Binding unknown number of parameters
  • Compatibility
Of course, all the inconveniences above could be overcame by a good wrapper. This is why if you choose mysqli, you definitely have to use one.
tagged: pdo mysqli comparison usability database access categories

Link: https://phpdelusions.net/pdo/mysqli_comparison

Matthew Turland:
PHPUnit + XHProf = BOOM!
Oct 14, 2015 @ 09:38:22

Matthew Turland has a post to his site sharing his experience with the PHPUnit and XHProf combination...and the unfortunate result that made every test fail.

I ran into an issue recently while trying to run PHPUnit tests in an environment using XHProf. Google didn’t prove to be much help, so I thought I’d document the problem and solution here for posterity.

When I ran my tests, each failed with the same cryptic error and no backtrace: "Attempted to serialize unserializable builtin class PDO" The cause was the culmination of two rather unfortunate circumstances.

He shares the two problems that causes this issue - one being XHProf's use of globals (where its PDO connection is stored) and the other is the @backupGlobals setting in PHPUnit that's enabled by default. This makes PHPUnit to try to backup that PDO connection by serializing it but can't, hence the failure. He points out a pull request that aims to fix the issue but recommends disabling the globals backup for the time being if you don't have a need for it.

tagged: phpunit xhprof combine globals pdo error backup

Link: http://matthewturland.com/2015/10/13/phpunit-xhprof-boom/

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