It is widely known that PHP has problems. Some people even take it to the extreme (I really should write about this one day). But no matter how you hate PHP, you can’t deny the fact it runs half of the website on the internet and that if you know how to program you’re not going to find much friction between your code and PHP (except of course the ridiculously inconsistent function names and arguments ordering).

And not too long ago, at work, I’ve discovered yet another weird quirk, this time it’s something that can seriously bite you in the butt and seems to not be documented. It pertains to mysqli database connection library.

In short, if your statement has returned an error while executing, that error is available via $mysqli->error only as long as the statement object exists. If you unset it or leave the context in which it was created (which means it’ll be collected from the stack), mysqli will report no error happening:

    // Given a table 'test' with not-null column 'id'
    $mysqli = new mysqli();

    query($mysqli);
    echo mysqli_error($mysqli); // Echoes empty string

    function query($mysqli){
        $param = null;
        $statement = mysqli_prepare($mysqli, "INSERT INTO test(id) VALUES(?)");
        $statement->bind_param("s", $param);
        $statement->execute();
        echo mysqli_error($mysqli); // Echoes "Column 'id' cannot be null"
    }

Let’s check the docs:

• http://php.net/manual/en/mysqli.error.php – Nope, nothing about statements. It doesn’t even mention them, though it’s easy to guess it should return errors caused by executing statements because, under the hood, it’s still using the same mysqli instance.
• http://php.net/manual/en/mysqli.prepare.php – Predictably nothing interesting in prepare function, though there are some interesting comments in there.
• http://php.net/manual/en/mysqli-stmt.execute.php – Clearly the function that executes the statement will have something about this behavior, right? Nope.
• http://php.net/manual/en/mysqli-stmt.error.php – At this point, I no longer trust I’ll find anything, but, for good measure, I checked docs for statement’s error property, and there is nothing there. Drat.

It’s an annoying issue that can be a huuuuge pain if you’re working in a legacy system and the legacier it is the worse. But it might just be that you’ll never care about it a bit. I tested and reproduced this issue on PHP 7.1.9 and that’s the only one I checked because I don’t really care much about older versions of PHP. Below is a complete example if you’d like to check it yourself.

A complete example

<?php

/*
	Assuming table:

	CREATE TABLE `test` (
		`id` int(11) NOT NULL,
		PRIMARY KEY (`id`)
	);
 */

$mysqli = mysqli_connect("localhost", "root", "", "test");

echo "<pre>";
runQueryWithError();
l("out -> mysqli::query", mysqli_error($mysqli));
runQueryInStatementWithError();
l("out-> statement::query", mysqli_error($mysqli));

/*
Gives:
1. [f() -> mysqli::query] Column 'id' cannot be null
2. [out ->; mysqli::query] Column 'id' cannot be null
3. [f() -> statement::query] Column 'id' cannot be null
4. [f() -> statement::query -> after unset]
5. [out-> statement::query]
 */

function l($title, $message)
{
	static $counter = 0;

	$counter++;
	echo "{$counter}. [{$title}] {$message}<br>";
}

function runQueryWithError()
{
	global $mysqli;

	mysqli_query($mysqli, "INSERT INTO test(id) VALUES(NULL)");
	l("f() -> mysqli::query", mysqli_error($mysqli));
}

function runQueryInStatementWithError()
{
	global $mysqli;

	$param = null;
	$statement = mysqli_prepare($mysqli, "INSERT INTO test(id) VALUES(?)");
	$statement->bind_param("s", $param);
	$statement->execute();
	l("f() -> statement::query", mysqli_error($mysqli));
	unset($statement);
	l("f() -> statement::query -> after unset", mysqli_error($mysqli));
}

[mc4wp_form id="444"]