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

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!
Eduardo FuerteDeveloper and AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hieloCommented:
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");
Ray PaseurCommented:
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 AnalystAuthor 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.
Get a highly available system for cyber protection

The Acronis SDI Appliance is a new plug-n-play solution with pre-configured Acronis Software-Defined Infrastructure software that gives service providers and enterprises ready access to a fault-tolerant system, which combines universal storage and high-performance virtualization.

gr8gonzoConsultantCommented:
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.
hieloCommented:
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.
Ray PaseurCommented:
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 AnalystAuthor 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
hieloCommented:
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?
gr8gonzoConsultantCommented:
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 AnalystAuthor 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 AnalystAuthor 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 AnalystAuthor Commented:
What I saw is:

img010
Eduardo FuerteDeveloper and AnalystAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eduardo FuerteDeveloper and AnalystAuthor Commented:
It's only a matter of apply the decode function, as pointed!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.