Sunday, September 27, 2009

PHP MySQLi bind_result Empty LONGTEXT

The problem is that when using the PHP MySQLi extension with prepared statements, I was getting an empty value in one of my bound variables after using $mysqli->execute(), $stmt->bind_param(), and $stmt->fetch(). If I executed the exact same query using $mysqli->query() and subsequently using $result->fetch_object() I would get the values I was looking for.

I went as far as copying and pasting the variable names associated with the column from every place they were used in the script and comparing them to make sure there were no typos.

Now, if I leave everything exactly how it is, I can toggle this behavior by switching the data type of the problematic column between TEXT and LONGTEXT. When the column is LONGTEXT then the prepared statement returns an empty string to the bound variable. If the column is TEXT then everything works as expected.

Strange behavior to say the least. I've caught a few unresolved PHP bug reports that appear to be related to this when looking for PHP MySQLi LONGTEXT, but nothing too detailed.

In any event, we'll be able to work with a TEXT column instead of using a LONGTEXT column in this application. You should probably research the impart if could have on you before switching though. Some of the older bug reports appear to mention Wordpress users struggling with this when reading from the Wordpress database for plugins.


Kenny Cason said...

Very interesting. A friend and I ran into this same bug earlier this week.

Ambrose Chua said...

Me too.

Ambrose Chua said...

Me too. But using PHP and MySQL. Help?

Anonymous said...

Thank you. Banging my head against the keyboard was starting to hurt.