Tip: It may seem obvious, but you need to trim your strings down to fit in the database field you are saving them to via a parametrized query or a stored procedure. (IE: only submit up to 20 characters to a VARCHAR(20) database field). If you send a larger string to the query then it can handle, you will get an error.
When cleaning up your strings, you will most likely find yourself using the php substr() function. This function will return, as documented, a boolean FALSE value when presented with an empty string. Not minding this boolean FALSE value will cause "0" to be saved in your database tables instead of an empty string.
Since trimming your input is also important, the simple and intuitive solution for this is to trim your substr() output, which will consistently supply and empty string, not boolean FALSE.
So this will always work:
<?php
$address_line_2 = trim(substr($_POST['addr2']),0,30));
echo gettype($address_line_2); $sql = "update tblAddressBook set name=(?), addr1=(?), addr2=(?),..."
$params = array($name, $address_line_1, $address_line_2, ...)
$sql_srv_query($db_conn, $sql, $params);
?>
This second way will give seemingly unexpected data in your database.
<?php
$address_line_2 = substr(trim($_POST['addr2'])),0,30);
echo gettype($address_line_2); $sql = "update tblAddressBook set name=(?), addr1=(?), addr2=(?),..."
$params = array($name, $address_line_1, $address_line_2, ...)
$sql_srv_query($db_conn, $sql, $params);
?>
You can also cast the type as a string using,
which will cast the boolean false back to the expected Empty String.
<?php
$address_line_2 = (string)substr(trim($_POST['addr2'])),0,30);
echo gettype($address_line_2); $sql = "update tblAddressBook set name=(?), addr1=(?), addr2=(?),..."
$params = array($name, $address_line_1, $address_line_2, ...)
$sql_srv_query($db_conn, $sql, $params);
?>
I didn't notice this behavior until switching to IIS7, PHP 5.3.8 and SQL Server 2008. But the behavior also is exhibited with IIS7, PHP 5.2 and SQL Server 2008.
