Mysqli prepared statments error Column cannot be null when value is not null

[djeyewater]

I have the following function:

public function addCatsForItem(array $aItem):void
    {
    	static $oStmt;
		$oDb = $this->_oDb;
		if (!$oStmt) {
			$sSQL = 'INSERT INTO item_terms (item_id, term_id) VALUES (?,?)';
	    	$oStmt = $oDb->prepare($sSQL);
			$oStmt->bind_param('ii', $iItemId, $iTermId);
	    }
	    if (empty($aItem['categories'])) {
	    	return;
	    }
	    foreach ($aItem['categories'] as $iTermId) {
	    	$iItemId = $aItem['id'];
	    	var_dump($iItemId, $iTermId);
	    	$oStmt->execute();
	    }
    }

From the above you can see I am dumping both of the values to be inserted to check them. When I run it, I get the following:

int(83)
int(7)
<br />
<b>Fatal error</b>:  Uncaught mysqli_sql_exception: Column 'item_id' cannot be null in /var/www/html/lib/Xoogu/Itemsdb/ImportFromCsv.php:410

So I don’t understand why it is saying the column cannot be null when the value being inserted is not null?

[mabismad]

What does var_dump($aItem) show, so that we would know what the input data is and how many categories there are? Are you calling this method more than once, and on which call to this method does the error occur, 1, 2, last? Are you calling anything else that uses a prepared query between the calls to this method?

The most likely cause is because $iItemId and $iTermId are not static, and the instances of them that were bound don’t exist on any call to this method after the first call. The $iItemId and $iTermId that are being created by the foreach() loop on any later calls to this method are not the same as those that were bound on the first call to this method.

In any case, if you switch to the much simpler and more modern PDO extension, you can simply supply an array of the input values to the ->execute([…]) call and avoid the explicit binding that the mysqli extension requires.

[spaceshiptrooper]

@djeyewater

So I don’t understand why it is saying the column cannot be null when the value being inserted is not null?

The reason why you’re getting this error is because you got everything backwards. You’re trying to reference a variable that doesn’t exist. I’m assuming you think it exists because you’ve created the variable at the bottom, but that is exactly the issue. You’re trying to create “the egg or the chicken” scenario.

The variable doesn’t exist at the start of the execution and therefore is indeed a “null” variable. Only up until you started going through the array at the bottom do you actually start to create that variable you’re trying to reference at the top. That’s not how programs run. Programs run top to bottom. Not bottom to top.

@djeyewater

$oStmt->execute();

Also, this will execute twice which will have side effects you don’t want. Only execute once.

@mabismad

n any case, if you switch to the much simpler and more modern PDO extension

To be clear, switching to a different database API will still pose the same issue. This isn’t about “mysqli vs PDO”, it’s about logical thinking.

[mabismad]

Unfortunately, no. If you have ever used the mysqli extension, the bind_param statement creates a reference to the variable. The variable doesn’t need to exist, which is why this code doesn’t produce an undefined variable error. At the ->execute() call, the reference is used to get the current value of the variable. The PDO bindParam operates the same.

And if you have ever used the PDO extension, and use implicit binding, by supplying an array of the values to the execute call, none of this occurs. The values in the supplied array are simply included as a list of values as part of the execute command that is sent to the database server.

[mabismad]

And I just discovered that as of php8.1, the mysqli extension has added implicit binding. You can now simply supply an array of values to the ->execute([…]) call and avoid all the bind_param() problems.

[kicken]

The problem is that you are declaring your statement as static so it only gets created the first time you call that function. Unlike your static $oStmt variable, your parameter reference variables do not persist across function calls. On subsequent calls then, those parameters are never assigned any value and thus are NULL.

[Thallius]

Having static in a function is by 99.9% an error

[djeyewater]

Thanks very much, that makes sense and does seem to be the issue. I just changed the two bound variables to be static as well and the code now runs without problem.

For those wondering about why the statement is static, with prepared statements you send the statement to the database server once then execute it mutliple times. Hence I only need the statement creating the first time this method is run, then on all subsequent calls it will just be executed with the different values that need to inserted.

[Thallius]

For me a typical example for POITROAE