Logo
  • PHP
    • HipHop / HHVM
    • Modern PHP
    • PHPStorm
    • LAMP
    • Laravel
    • Composer
    • PDO
  • JavaScript
    • node.js
    • AngularJS
  • CSS
    • SASS
    • “CSS4” (CSS level 4)
  • HTML
  • Git
  • LAMP
  • Vagrant
  • UI / UX
  • Architecture of …
  • Off-Topic
With ♥ from Berlin
July 8, 2014
Chris
Composer, Local Development, PDO, PHP, Refactoring
0

Debug PDO with this one-line function. Yeah!

PreviousNext
pdo-debug

Update: This article is a little bit older, the tool has changed gently. Everthing this article still works exactly like described. But, to get the latest version (different syntax) and up-to-date install instructions please see the GitHub page.

You know the problem: Debugging PDO is a pain in the ass, as it’s “not really possible”. With the classic mysql_* and mysqli_* functions / methods you can easily create your SQL query and output it directly in PHP with echo, var_dump or – the correct way – see the content directly in your IDE’s debugger. A little reminder here: Learn real debugging, it will save your life! However, when using PDO you’ll get the advantages of automatic SQL injection protection, beautiful syntax and performance increases, but have to deal with the fact you’ll never really see the full SQL statement. This is a common problem and has been asked over and over again on the web. I was also frustrated with that, and therefore grabbed the best PDO query emulator function I could find, put it into a Composer project and released it on GitHub / packagist: pdo-debug.

Why does this problem exist ?

PDO sends the raw query (without real values) and the parameters independently to the database server. That’s it. So PHP will never know the full statement, as the final query is constructed by MySQL, not by PHP.

In theory, the possibilities to debug PDO are

1. Let MySQL log the finally created values. This needs a lot of configuration and is definitly the best way to go, as this shows the real, final statements.

2. Take the raw SQL with the placeholders, take the parameter values and simply combine them in the way PDO would do. This is not perfect, as PDO does / allows more than just replacing the placeholders with real values (like changing types etc.). It’s just an emulation, and a very basic one.

Prepare a PDO block for debuggin with pdo-debug

Your PDO block probably looks like this:

$sql = "INSERT INTO test (col1, col2, col3) VALUES (:col1, :col2, :col3)";

and this, right?

$query->execute(array(':col1' => $param_1, ':col2' => $param_2, ':col3' => $param_3));

To use this PDO logger, you’ll have to rebuild the param array a little bit: Create an array that has the identifier as the key and the parameter as the value, like below:
WARNING: write this WITHOUT the colon! The keys need to be ‘xxx’, not ‘:xxx’!

$parameters = array(
    'param1' => 'hello',
    'param2' => 123,
    'param3' => null
);

Your full PDO block would then look like:

$sql = "INSERT INTO test (col1, col2, col3) VALUES (:param1, :param2, :param3)";
$query = $database_connection->prepare($sql);
$query->execute($parameters);

Perfect!

Load pdo-debug into your project (with Composer) and use it

As Composer is the de facto standard, we’ll load this debug tools with Composer (note the require-dev as you don’t need a PDO debugger in production, just in development)

"require-dev": {
    "panique/pdo-debug": "0.1"
}

and perform an

composer update

to load this into your project. You can now use pdo-debug everywhere by using the global function debugPDO(). A simple real world example:

echo debugPDO($sql, $parameters);

This would echo out the full SQL statement including the parameter values, like

INSERT INTO test (col1, col2, col3) VALUES ('hello', 123, NULL)

Voila!

Big thanks

A big thanks to bigwebguy and Mike on this StackOverflow thread: Getting raw SQL query string from PDO prepared statements. These guys have created and improved the basic function that is used in this free and open-source project. Send them a Thanks-notice if pdo-debug saves you a lot of time and nerves!

Warning: This is highly experimental!

This little tool is super-basic. The underlaying function has a lot of upvotes on StackOverflow and already helped lots of people. However, it’s just a basic emulation, not looking at possible glitches when doing advanced PDO stuff. If you are advanced or an expert with PDO, it would be nice if you could add your part to the repo right on GitHub. Maybe it’s possible to make this function better by respecting PDO’s parameter options etc. – anyway, if you want to contribute, please do!

This article was written quite a while ago (9 years), please keep this in mind when using the information written here. Links, code and commands might be outdated or broken.

Random articles

  • [Link] How to create, read, update and delete (CRUD) with PDO, MySQLi and MySQL the right way (prepared statements)[Link] How to create, read, update and delete (CRUD) with PDO, MySQLi and MySQL the right way (prepared statements)
  • How to install sqlite driver for PHP in Ubuntu & DebianHow to install sqlite driver for PHP in Ubuntu & Debian
  • PHPStorm: 42 Tips and Tricks (47min video talk by Mikhail Vink at Dutch PHP Conference 2015)PHPStorm: 42 Tips and Tricks (47min video talk by Mikhail Vink at Dutch PHP Conference 2015)
  • What’s new in PHPStorm 9What’s new in PHPStorm 9
  • Experimenting with HHVM at Etsy (Link)Experimenting with HHVM at Etsy (Link)
  • Dangerous Performance Myths in the Web (video talk by Thomas Lohner, PHPUG Latvia)Dangerous Performance Myths in the Web (video talk by Thomas Lohner, PHPUG Latvia)
  • Install MINI in 30 seconds inside Ubuntu 14.04 LTSInstall MINI in 30 seconds inside Ubuntu 14.04 LTS
  • MINI2, an extremely simple barebone PHP application on top of SlimMINI2, an extremely simple barebone PHP application on top of Slim
  • MINI, an extremely simple barebone PHP applicationMINI, an extremely simple barebone PHP application
debugpdoPHPsql
Share this

Leave A Comment Cancel reply

increase-your-pagespeed-score

Increase your PageSpeed score (10min video with Matt Gaunt)

Compact and clever information for dramatic speed increases. Definitly worth watching. This article was written quite a while ago (9

ubuntu-14-04-lts lamp

How to install/setup a basic LAMP stack (Linux, Apache, MySQL, PHP) on Ubuntu 14.04 LTS

This little tutorial shows how to setup Apache, MySQL and PHP on a Linux server, in this case Ubuntu 14.04

Useful basic linux stuff: Show kernel version, distribution name and distribution version on Ubuntu systems

Always nice to know: Show kernel version: uname -r Show kernel version and CPU: uname -a Show distribution version: lsb_release

Hacked french TV channel exposed passwords in TV interview (video, screenshots, links)

This week a major french TV networks was hacked (Article on CNN #1, #2), resulting in 11 channels being completely

GitHub introduces revert button / rollback for merged pull requests

Once again GitHub delivers an excellent feature: Clean one-click rollbacks / reverts / undos of pull requests, very very useful,

DigitalOcean coupon voucher 50

Only today: $50 coupon for DigitalOcean SSD VPS / hosting

Wow! The supernice SSD-cloud-server-provider DigitalOcean adds $50 (!) to new customers accounts ONLY TODAY (US-time-zones, so calculate if this is

phpstorm-8

A PHPStorm shortcuts cheat sheet (for Windows, Mac OS and Linux)

Shortcuts that will make you life so much easier! Please note: Frontend Devs might be irritated by the weird CTRL-Y-shortcut,

Disappointed by Watch Dogs’s graphics ? See how it looks with unlocked, hidden settings. Awesome!

When you are interested in 3D and game graphics in general, you probably stumbled upon these excellent and extremely good-looking

phpstorm 7.0 php

How to debug code on a remote server (or in vagrant box) with PHPStorm

Please also note: There are several methods to do remote debugging. This is the one that works without any browser

php uk conference

Slides & talks from PHP UK Conference 2014

The slides of most of PHP UK CONFERENCE 2014‘s talks are online, very interesting stuff, have a look. And a

1/4

Categories

Search

php uk conference
PHP Caching Best Practices by Eli White (video from PHP UK Conference 2014)
phpstorm-github-code-color-syntax-theme
Get Github’s syntax highlighting colors in PHPStorm
New project: Building a naked PHP skeleton / boilerplate application from scratch
Awesome next-gen PS4 graphics in “The Order: 1886”
This is an experimental advertisement
php
appserver.io – A New Way of Magento Enterprise Infrastructure (26min video talk)
Rare Steve Jobs AND Bill Gates video interview from 2007’s D5 conference (90min)
composer
How to install Composer on Windows 7 / 8 or Ubuntu
css3-chrome-font
[Link] Retinafy your Site / Device by Nijiko Yonskai
Meet the developers behind Ableton (14min video)
compress-png
Compress png, jpeg, gif and svg up to 90% with Compressor.io
organizing css
Jonathan Snook – CSS is a Mess – How to organize CSS in big projects (54min video talk)
phpstorm-8
When PHPStorm’s cache eats up your harddisk space
phpstorm 7.0 php
PHPStorm 7 has been released!
set up a local virtual machine for development with vagrant and puphpet / puppet (and ubuntu, linux, php, apache, mysql)
A super-simple pre-configured Vagrant box with HipHop, Hack and Hack code examples

Tags

apache bash centos composer conference coupon CSS debian fonts framework git GitHub hack HHVM HipHop HTML HTML5 IDE JavaScript JS LAMP laravel linux mod_rewrite MVC MySQL Nginx optimization PHP PHP 5.5 PHP 5.6 phpmyadmin PHPStorm security server SSD Ubuntu UI UX vagrant video virtual machine voucher VPS wordpress
Side-Project: Wordle-Solver:
www.wordle-helper.info

Pages

  • Privacy Policy
 
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept”, you consent to the use of ALL the cookies.
Do not sell my personal information.
Cookie SettingsAccept
Manage consent

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
CookieDurationDescription
cookielawinfo-checkbox-analytics11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional11 monthsThe cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy11 monthsThe cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytics
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Others
Other uncategorized cookies are those that are being analyzed and have not been classified into a category as yet.
SAVE & ACCEPT