CakePHP | How to handle special characters (like apostrophes) in queries

|
| By Webner

Let us take an example:

We have table employees with following fields:

id int primary key,

name varchar(50),

city varchar(50)

We have following records in the table:

(1, isha, amr);

(2, Hamlet’s, ghy);

(3, éhjj, amr);

2nd and 3rd records have special characters in their name field. Suppose we have this query to retrieve data from this table in CakePHP:

// find all non empty records
$result = $this->find('all', array
('conditions' => array('name !=' => ''),
'Fields' => array('id', 'name'))
);

print_r($result);  // this is print statement for formatted array in php
If we print the result, result will be displayed as follows:

Array
  (
   [0] => Array(
   [Employee] => Array(
   [id] => 1
   [name] => isha)
   )
   [1] => Array(
   [Employee] => Array(
   [id] => 2
   [name] => Hamlet�s)
   )
   [2] => Array(
   [Employee] => Array(
   [id] => 3
   [name] => �hjj)
   )
 )

We can see from above result that the special characters are not displayed properly. Special characters are displayed as a question mark in filled diamond shape. Similarly, if we want to find id and city of given name having the special character, we will again face a problem.

Suppose our query is:

 
$result = $this->find('all', array(
'fields' => array('id','city'),
'conditions' => array(
'name' => 'éhjj')
));

If we run above query, it will return the empty array.

To resolve both the issues like displaying special characters when we print array and not displaying empty array when we pass name in a query of find condition, CakePHP needs us to use correct character encoding.

We have to use option ‘encoding’ => ‘utf8’ in database config setting.

This option will be used in database.php file located in config folder as follows:

public $default = array(
 'datasource' => 'Database/Mysql',
 'persistent' => false,
 'host' => 'localhost',
 'login' => 'root',
 'password' => '123456',
 'database' => 'TestDatabase',
 'prefix' => '',
 'encoding' => 'utf8'
 );

After using this option, special characters will be displayed properly as follows:

Array
(
 [0] => Array(
 [Employee] => Array(
 [id] => 1
 [name] => isha         
   ))
  [1] => Array(
  [Employee] => Array(
  [id] => 2
  [name] =>     Hamlet’s)
   )
  [2] => Array(
  [Employee] => Array(
  [id] => 3
  [name] =>      éhjj))
)

Empty array will not be returned when we pass name with special character in find condition of query. It will return id and city of record for name passed in find condition as follows:

Array
(
  [0] => Array(
  [Employee] => Array(
  [id] => 3
  [name] => éhjj))
 )

Leave a Reply

Your email address will not be published. Required fields are marked *