#25845 [NEW]: Convert empty strings to NULL in DB::execute()

From: temporary1 at understroem dot dk Operating system: PHP version: 5.0.0b1 (beta1) PHP Bug Type: PEAR related Bug description: Convert empty strings to NULL in DB::execute() Description: ------------ It would be nice if empty strings were converted into the database NULL value when you use DB::execute(). Say you have a form in which the user writes a number which is later to be stored in a database column defined as SMALLINT. When the user pushes the submit button, the number will be accessible to the receiving script as, say, $_POST['age']. The script then tries to insert it into the database: $preparation = $db->prepare('INSERT INTO users ( age ) VALUES ( ? )'); $db->execute($preparation, array( $_POST['age'] )); This works fine if the user actually entered his/her age. But say the age form field is optional. Now, the value of $_POST['age'] is an empty string, and when you run the above code, the database will complain that you're trying to insert a string into a SMALLINT column (at least PostgreSQL will behave that way - I don't know about other databases). Most times when a programmer makes code that enters empty string into a database, he/she doesn't actually want the field to contain an empty string - he wants the field to be empty. Thus, it would be nice if PEAR DB converted empty strings into the database NULL value when you run execute(). The alternative is to run a lot of checks for each $_POST variable and then use '!' instead of '?' to insert: $_POST['age'] = (!empty($_POST['age'])) ? $db->quote($_POST['age']) : 'NULL'; $preparation = $db->prepare('INSERT INTO users ( age ) VALUES ( ! )'); $db->execute($preparation, array( $_POST['age'] )); ...but that's a lot of work when you're dealing with a lot of form fields. -- Edit bug report at http://bugs.php.net/?id=25845&edit=1 -- Try a CVS snapshot (php4): http://bugs.php.net/fix.php?id=25845&r=trysnapshot4 Try a CVS snapshot (php5): http://bugs.php.net/fix.php?id=25845&r=trysnapshot5 Fixed in CVS: http://bugs.php.net/fix.php?id=25845&r=fixedcvs Fixed in release: http://bugs.php.net/fix.php?id=25845&r=alreadyfixed Need backtrace: http://bugs.php.net/fix.php?id=25845&r=needtrace Try newer version: http://bugs.php.net/fix.php?id=25845&r=oldversion Not developer issue: http://bugs.php.net/fix.php?id=25845&r=support Expected behavior: http://bugs.php.net/fix.php?id=25845&r=notwrong Not enough info: http://bugs.php.net/fix.php?id=25845&r=notenoughinfo Submitted twice: http://bugs.php.net/fix.php?id=25845&r=submittedtwice register_globals: http://bugs.php.net/fix.php?id=25845&r=globals PHP 3 support discontinued: http://bugs.php.net/fix.php?id=25845&r=php3 Daylight Savings: http://bugs.php.net/fix.php?id=25845&r=dst IIS Stability: http://bugs.php.net/fix.php?id=25845&r=isapi Install GNU Sed: http://bugs.php.net/fix.php?id=25845&r=gnused Floating point limitations: http://bugs.php.net/fix.php?id=25845&r=float