I used to use the standard mysql_connect(), mysql_query(), etc statements for doing MySQL stuff from PHP. Lately I've been switching over to using the wonderful MDB2 class. Along with it, I'm using prepared statements, so I don't have to worry about escaping my input and SQL injection attacks.
However, there's one problem I'm running into. I have a table with a few VARCHAR columns, that are specified as not-null (that is, do not allow NULL values). Using the old MySQL PHP commands, I could do things like this without any problem:
INSERT INTO mytable SET somevarchar = '';
Now, however, if I have a query like:
INSERT INTO mytable SET somevarchar = ?;
And then in PHP I have:
$value = ""; $prepared = $db->prepare($query, array('text')); $result = $prepared->execute($value);
This will throw the error "
null value violates not-null constraint"
As a temporary workaround, I check if
$value is empty, and change it to
" " (a single space), but that's a horrible hack and might cause other issues.
How am I supposed to insert empty strings with prepared statements, without it trying to instead insert a NULL?
EDIT: It's too big of a project to go through my entire codebase, find everywhere that uses an empty string "" and change it to use NULL instead. What I need to know is why standard MySQL queries treat "" and NULL as two separate things (as I think is correct), but prepared statements converts "" into NULL.
Note that "" and NULL are not the same thing. For Example,
SELECT NULL = ""; returns
NULL instead of
1 as you'd expect.