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

ID: 25845 User updated by: temporary1 at understroem dot dk Reported By: temporary1 at understroem dot dk Status: Open Bug Type: PEAR related PHP Version: 5.0.0b1 (beta1) New Comment: I've altered DB/common.php to make it insert the database NULL value when the data in the second argument to DB::execute() is an empty string. See http://understroem.dk/lab/common.php.diff Previous Comments: ------------------------------------------------------------------------ [2003-10-12 12:06:08] temporary1 at understroem dot dk 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 this bug report at http://bugs.php.net/?id=25845&edit=1