Search code examples
phppostgresqlyiiwhitespacecriteria

Yii Php, PostgreSQL - programmatically handling whitespaces in database

i'm having a trouble with handling various, database values, special characters etc.

I'm using Yii php, specifically CDbCriteria when querying the postgres database, and i'm having problems with entries that have white spaces before and after the entry, to demonstrate:

I have:

  $criteria = new CDbCriteria;
  $criteria->compare('first_name',trim($name) , false, 'OR'); 
  $criteria->compare('surname', trim($name), false, 'OR');

  $person = Name::model()->find($criteria);

The code above is suppose to find in the database, a matched row, given the $name variable from an input form. This works perfectly if the first_name or surname in the database table has no white spaces.

for example:

$name = "Richard";

But in the database, the first_name value is:

"[white space]Richard[whitespace]"

Then the the $person will return null, because "Richard" cannot be found since the 'Richard' with white spaces is in the database. However the proper behavior is that it should match correctly for they are basically the same name ( i do know that having whitespaces in the database is not a good thing, for every entry should be trimmed, however the application should be flexible enough to handle this). How do i solve this problem? Thanks a lot!

Solution

  • You have two choices

    1) programatically remove white space when you edit a record (look at beforeSave())

    2) use TRIM in the criteria.

    $criteria = new CDbCriteria;
$criteria->condition = "TRIM(first_name) = '".trim($name)."' or TRIM(surname) = '".trim($name)."'";

    You may be able to do the above as $criteria->compare("TRIM(first_name)"...) - but I am not sure.