Could you point what's preventing a query that perfectly runs under MySQL IDE produces no result when running in PHP?

Eduardo Fuerte
Eduardo Fuerte used Ask the Experts™
on
Hi Experts


Could you point what's preventing a query that perfectly runs under MySQL IDE produces no result when running in PHP?

And what is needed to workaround it?


The query uses a special character in the where clause:

$analista = $where['like']['created_by'];
	
//     print_r($analista);
//            die;
	
	$query['query'] = "
			SELECT  
				sg.nome_seguradora as cliente_corporativo,
				DATE_FORMAT(data, '%d/%m/%y') as mes,
				it._name as analista,
				IF(interna_externa = 'I', 'Interna',IF(interna_externa ='E', 'Externa','-')) as interna_externa,
				enviado_por,
				nome_reclamante,
				IF(genero = 'M','Masculino',IF(genero = 'F','Feminio','-')) as genero,
				IF(fisica_juridica = 'F','Física',IF(genero = 'J','Jurídica','-')) as fisica_juridica,
				cpf_cnpj as cpf_cnpj,
				DATE_FORMAT(data_atendimento, '%d/%m/%Y') as data_atendimento,
				sise,
				cc.descricao as nome_produto,
				se.descricao as servico,
				su.descricao as sub_servico,
				cidade,
				uf,
				mo.motivo as motivo,
				de.descricao as origem_problema,
				bp.descricao as base_prestador,
				enviado_por as agente,
				DATE_FORMAT(enviado_dpto_em, '%d/%m/%Y %H:%i:%s') as enviado_dpto_em,
				DATE_FORMAT(resposta_dpto_em, '%d/%m/%Y %H:%i:%s') as resposta_dpto_em,
				responsavel,
				acoes_dpto,
				comentarios,
				parecer,
				DATE_FORMAT(recebido_em, '%d/%m/%Y %H:%i:%s') as recebido_em,
				DATE_FORMAT(respondido_em, '%d/%m/%Y %H:%i:%s') as respondido_em,
				tempo_resposta,
				IF(procedencia = 'A', 'Em Análise', IF(procedencia = 'P', 'Procedente', IF(procedencia = 'I', 'Improcedente', '-'))) as procedencia,
				IF((custo IS NULL) OR  (custo=0), 'Não' , 'Sim') as houve_custo,
				custo,
				IF((reembolso IS NULL) OR  (reembolso=0), 'Não' , 'Sim') as houve_reembolso,
				reembolso
		   FROM
					system_reclamacao sr
				LEFT OUTER JOIN system_cliente_corporativo cc
					ON sr.fk_nome_produto = cc.id_cliente_corporativo
				LEFT OUTER JOIN system_servico se
					ON sr.fk_servico = se.id_servico
				LEFT OUTER JOIN system_sub_servico su
					ON sr.fk_sub_servico = su.id_sub_servico
				LEFT OUTER JOIN system_motivo mo
					ON sr.fk_motivo_reclamacao = mo.id_motivo
				LEFT OUTER JOIN system_departamento de
					ON sr.fk_origem_problema = de.id_dpto
				LEFT OUTER JOIN system_base_prestador bp
					ON sr.fk_base_prestador = bp.id_base_prestador
				LEFT OUTER JOIN system_seguradora sg
					ON sr.fk_seguradora = sg.id_seguradora
				LEFT OUTER JOIN interface_users it
					ON sr.created_by = it.idinterface_users
				 WHERE  it._name LIKE '%" . $analista . "%' AND it._name IS NOT NULL";       

}



$get_reclamacao = $this->reclamacao_model->new_get($query);

print_r($get_reclamacao);
die;

Open in new window



Evidences:
MySQL IDE:
 img001

PHP (browser - print_r)
img002
and

img003

Thanks in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Year 2008
Top Expert 2008

Commented:
Try specifying the charset once you establish the connection to the db.

If you are using mysql, call $mysqli->set_charset("utf8") after you connect to the db (refer to http://php.net/manual/en/mysqli.set-charset.php).

If you are using PDO, specify the charset on the connection string  -- $link = new PDO("mysql:host=localhost;dbname=DB;charset=UTF8");
Most Valuable Expert 2011
Top Expert 2016

Commented:
You might want to bookmark this article.  See Character Sets in MySQL and Using UTF-8 with PDO
https://www.experts-exchange.com/articles/11880/Unicode-and-Character-Collisions.html
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Hi


The amazing thing is: since I'm using Codeigniter the configuration file database.php still consider UTF-8, acordingly to:

$db['default'] = array(
	'dsn'	=> '',
	'hostname' => 'localhost',
	'username' => 'root',
	'password' => 'root',
	'database' => 'teste',
	'dbdriver' => 'mysqli',
	'dbprefix' => '',
	'pconnect' => FALSE,
	'db_debug' => (ENVIRONMENT !== 'production'),
	'cache_on' => FALSE,
	'cachedir' => '',
        //----------------------------------
	'char_set' => 'utf8',
      //----------------------------------
	'dbcollat' => 'utf8_general_ci',
	'swap_pre' => '',
	'encrypt' => FALSE,
	'compress' => FALSE,
	'stricton' => FALSE,
	'failover' => array(),
	'save_queries' => TRUE
);

Open in new window


The code above run under this configuration.
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Commented:
Can you provide the code for reclamacao_model class and any class that it extends from?

My guess is that you'll need to check for the query error to find out what the problem is. Perhaps the query isn't being executed correctly, or is executing from the wrong database. Before your print_r() of your data, try doing:
echo $this->db->_error_message();

Open in new window


Also, when you're posting any code, like the configuration above, always mask or replace your real password.
Expert of the Year 2008
Top Expert 2008

Commented:
see if your default charset is utf-8 -- create file named info.php and type:
<?php
phpinfo();

Open in new window


Load it via the browser, and look for "default_charset".  If it is not utf-8, edit your php.ini file accordingly.

If it is already set to utf-8, then your php source code file may be using a different encoding. Open your php source code file and see if your editor has a setting to save the code in utf-8 encoding.  If it doesn't try installing Notepad++, then open your source code file in Notepad++ and click on "Encoding >Convert to UTF-8 without BOM", then save your file and re-upload it to your server.
Most Valuable Expert 2011
Top Expert 2016
Commented:
Character strings created in PHP are created in the character set that is in use at the time the string is created.  You might want to use some of the techniques in the article to inspect the variables and see if they are multi-byte.  I use these settings in my PHP scripts to ensure that PHP follows the rules for UTF-8 compliance.
mb_internal_encoding('UTF-8');
mb_regex_encoding('UTF-8');

Open in new window

And as if that's not enough, some of the behaviors are release-dependent.  Some time around PHP 5.4 they started to figure out the significance of UTF-8 and now it's standard multi-byte behavior for some functions.
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Hi

Thank you for the replies.

The query don't generates errors, just the result array is empty when using the special character "ã".

The UTF-8 is correctly configured is program file and php_info:

img001
img002

I used the configuration before running the query:
mb_internal_encoding('UTF-8');
mb_regex_encoding('UTF-8');
            

            $query['query'] = "
                    SELECT  .....
 

Open in new window


Unfortunatelly it's still producing an empty excell sheet.

img003
Expert of the Year 2008
Top Expert 2008
Commented:
On your original post, print the value of $query before you invoke your model:
echo '<pre>',print_r($query,true),'</pre>';
$get_reclamacao = $this->reclamacao_model->new_get($query);

Open in new window


Does the printed query contain what you expect for WHERE  it._name LIKE '%" . $analista . "%' AND it._name IS NOT NULL";?  In other words, do you see the expected value for $analista within the WHERE clause of your query?
Commented:
I wonder if we're looking at this in the wrong direction. The database itself has default collations/character sets. However, that doesn't prevent it from holding data that is encoded differently. If the data in the database was inserted as non-UTF-8-encoded data, and you're searching for the UTF-8 version, then you're not going to have any errors but you're not going to have any results either.

Let's try converting the query to looking for non-UTF-8 data. The code might thinking the DB is encoded as UTF-8 might there's always a chance that it's not, or that the data came in through a different encoding. Try this change:

BEFORE:
 $query['query'] = "
                    SELECT  .....";

Open in new window


AFTER:
 $query['query'] = utf8_decode("
                    SELECT  .....");

Open in new window


If you get data after that change, then it probably means you have non-UTF-8 data in your database.
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Really hard

After using utf8_decode the side effect is that special characters is considered FALSE

Using utf8_decode
img007
Without using
img008
Using print_r  it's very dificult to interpret:
img009
Even with Copy / Paste and supressing %20
http://localhost/_producao/ Cpre EArray(    [fields] = E Array        (            [0] = E sg.nome_seguradora as cliente_corporativo            [1] = E DATE_FORMAT(data, '%d/%m/%y') as mes            [2] = E it._name as analista            [3] = E IF(interna_externa = 'I', 'Interna',IF(interna_externa ='E', 'Externa','-')) as interna_externa            [4] = E enviado_por            [5] = E nome_reclamante            [6] = E IF(genero = 'M','Masculino',IF(genero = 'F','Feminio','-')) as genero            [7] = E IF(fisica_juridica = 'F','F%C3%ADsica',IF(genero = 'J','Jur%C3%ADdica','-')) as fisica_juridica            [8] = E cpf_cnpj as cpf_cnpj            [9] = E DATE_FORMAT(data_atendimento, '%d/%m/%Y') as data_atendimento            [10] = E sise            [11] = E cc.descricao as nome_produto            [12] = E se.descricao as servico            [13] = E su.descricao as sub_servico            [14] = E cidade            [15] = E uf            [16] = E mo.motivo as motivo            [17] = E de.descricao as origem_problema            [18] = E bp.descricao as base_prestador            [19] = E nome_agente as agente            [20] = E DATE_FORMAT(enviado_dpto_em, '%d/%m/%Y') as enviado_dpto_em            [21] = E DATE_FORMAT(resposta_dpto_em, '%d/%m/%Y') as resposta_dpto_em            [22] = E responsavel            [23] = E acoes_dpto            [24] = E comentarios            [25] = E parecer            [26] = E DATE_FORMAT(recebido_em, '%d/%m/%Y %h:%i:%s') as recebido_em            [27] = E DATE_FORMAT(respondido_em, '%d/%m/%Y %h:%i:%s') as respondido_em            [28] = E tempo_resposta            [29] = E IF(procedencia = 'A', 'Em An%C3%A1lise', IF(procedencia = 'P', 'Procedente', IF(procedencia = 'I', 'Improcedente', '-'))) as procedencia            [30] = E IF((custo IS NULL) OR  (custo=0), 'N%C3%A3o' , 'Sim') as houve_custo            [31] = E custo            [32] = E IF((reembolso IS NULL) OR  (reembolso=0), 'N%C3%A3o' , 'Sim') as houve_reembolso            [33] = E reembolso        )    [l] = E Array        (            [procedencia] = E             [sise] = E         )    [where] = E Array        (            [fk_seguradora] = E             [status] = E respondido_em IS NULL        )    [join] = E Array        (            [0] = E Array                (                    [table] = E system_cliente_corporativo cc                    [equal] = E fk_nome_produto = cc.id_cliente_corporativo                    [type] = E left                )            [1] = E Array                (                    [table] = E system_servico se                     [equal] = E fk_servico = se.id_servico                    [type] = E left                )            [2] = E Array                (                    [table] = E system_sub_servico su                     [equal] = E fk_sub_servico = su.id_sub_servico                    [type] = E left                )            [3] = E Array                (                    [table] = E system_motivo mo                     [equal] = E fk_motivo_reclamacao = mo.id_motivo                    [type] = E left                )            [4] = E Array                (                    [table] = E system_departamento de                     [equal] = E fk_origem_problema = de.id_dpto                    [type] = E left                )            [5] = E Array                (                    [table] = E system_base_prestador bp                     [equal] = E fk_base_prestador = bp.id_base_prestador                    [type] = E left                )            [6] = E Array                (                    [table] = E system_seguradora sg                     [equal] = E fk_seguradora = sg.id_seguradora                    [type] = E left                )            [7] = E Array                (                    [table] = E interface_users it                     [equal] = E created_by = it.idinterface_users                    [type] = E left                )        )) C/pre Ehttp://localhost/projeto__producao/public/excel/export.xlsx

Open in new window

Eduardo FuerteDeveloper and Analyst

Author

Commented:
My fault...

Obtaining the value from a string  containing the same content, not directly from the array

the content is:

WHERE  it._name LIKE '%Le%C3%A3o%' AND it._name IS NOT NULL

The query intercepts ã and put   %C3%A3 on its place...
Eduardo FuerteDeveloper and Analyst

Author

Commented:
What I saw is:

img010
Developer and Analyst
Commented:
Rewinding to the question origin:

 $analista = $where['like']['created_by'];

Open in new window


This solves the problem:

 $analista = urldecode($where['like']['created_by']);

Open in new window


Using your sugestions
Eduardo FuerteDeveloper and Analyst

Author

Commented:
It's only a matter of apply the decode function, as pointed!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial