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

ID: 25845 Updated by: lsmith@php.net Reported By: temporary1 at understroem dot dk Status: Open Bug Type: PEAR related PHP Version: 5.0.0b1 (beta1) New Comment: Well NULL vs. empty strings is a mess. oracle stores all empty strings as NULL for example. However NULL's behave quite differently than empty strings due to the special character of NULL. Anyways there really is no point in adding this feature imho as its usefulness is quite specific. I recommend that you write a little wrapper function instead. Previous Comments: ------------------------------------------------------------------------ [2003-10-13 06:30:14] mansion@php.net Hi, I don't see the need for such a "feature". An empty string is not null and should not be treated as such. It's really up to you to filter your values before they get inserted. Your proposal will ad more problems than it will solve. Furthermore, it's not a good habit to have NULL columns in a database. If a column can be NULL, then most of the time it's useless. I suggest you have your column defaut to '' or 0 or whatever. Well, that's my opinion... ------------------------------------------------------------------------ [2003-10-13 05:26:39] temporary1 at understroem dot dk I should add that the version of common.php which I altered is 1.26. ------------------------------------------------------------------------ [2003-10-13 05:24:00] temporary1 at understroem dot dk 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 ------------------------------------------------------------------------ [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