MCVE or How to debug database interactions with PDO

1. MCVE example
2. Debugging with your own data
3. Comments (1)

MCVE is a Minimal, Complete, and Verifiable example you will be asked for, if happen to post a code related question on Stack Overflow.

It is very important to provide a complete verifiable example that anyone can run and confirm a problem (or not confirm, but either way you will get a certain answer).

Interestingly, that most likely you will get your answer even faster, during the creation process. Because the process of MCVE creation will put aside all unnecessary and false issues, and will prove your hypothesis wrong, making you to search for a cause elsewhere.

MCVE example

A verifiable example means you have to provide a complete code that can display your problem. Most people don't get the concept at first, though. You would probably say, "I already posted the most complete code I run!". But the problem is that to verify your issue someone must be able to run your code in their computer and confirm the results. It means that you have to provide the full temporary environment for your code to run, including the sample data, the problem input and the most simplified excerpt from your code that could demonstrate the problem.

However, the process is not as scary as you would think. To create such an environment basically you need to follow three simple steps, providing a code which

• creates a new table in the database
• fills it with the sample data
• runs a query that does not return you the desired result

For example, imagine you've got a problem with searching certain data using LIKE. To create an MCVE you will need a code like this (given $pdo variable contains a valid pdo instance):

// connection
$host = '127.0.0.1';
$db   = 'test';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdo = new PDO("mysql:host=$host;dbname=$db;charset=$charset", $user, $pass, $options);

// first let's create a table 
// note that table is temporary so it won't pollute your database
$pdo->query("CREATE temporary TABLE pdo_mcve (id int auto_increment primary key, name varchar(255))");

// then fill it with sample data
$pdo->query("INSERT INTO pdo_mcve (name) VALUES ('foo'),('foobar'),('baz')");

// now let's see if we have our data back all right
$data = $pdo->query("SELECT name FROM pdo_mcve")->fetchAll(PDO::FETCH_COLUMN);
var_export($data);

// now let's test with a hardcoded value
$data = $pdo->query("SELECT name FROM pdo_mcve WHERE name LIKE 'foo%'")->fetchAll(PDO::FETCH_COLUMN);
var_export($data);

// and finally, with a prepared statement
$stmt = $pdo->prepare("SELECT name FROM pdo_mcve WHERE name LIKE ?");
$stmt->execute(['foo%']);
$data = $stmt->fetchAll(PDO::FETCH_COLUMN);
var_export($data);

The indispensable value of the above code snippet is that everyone can copy/paste it in their editor and run for test, thus being able to confirm (or not confirm) the issue.

But, like it was mentioned above, most likely upon running this test code, you will find that the query itself is all right. Means there is nothing wrong with the code. Whereas if something is not found still, it is the real data in your table to blame. Which, being unknown to SO participants, makes your question essentially unanswerable. And therefore to get an answer you have to provide a similar example that operates your own data but still being verifiable by anyone else.

Debugging with your own data

To provide an example with your existing data you have to select the row that contains the value in question. You can use other WHERE condition or LIMIT or anything that will return the needed value. Then compare the values using PHP, as well as displaying values in question for the visual inspection.

For example, you are sure that a row that contains value "foo" has an id=1. So, select this value, fetch it and then compare with the value you have in PHP script. Of course, you have to display both values as well:

$data = $pdo->query("SELECT name FROM pdo_mcve WHERE id=1")->fetchColumn();
$value = 'foo';
var_dump($data, $value, $data == $value);

So now you can tell that either your data is invalid or you are doing some even more stupid mistake like connecting to a wrong database.

In case displayed values look equal but comparison returns false, then there is something wrong with data encoding. To test this, try to encode both values in hex to see actual codes comprising the value:

$data = $pdo->query("SELECT name FROM pdo_mcve WHERE id=1")->fetchColumn();
$value = 'foo ';
var_dump(bin2hex($data), bin2hex($value));

this example will reveal an extra space in your data which you'll be able to fix.